UKC

I have a spreadsheet full of OCR data which is a mess, I have descriptions relating to a series of unique id numbers but the descriptions have been split across multiple rows. Can i easily use a lookup to find which cells have the same id and then Concatenate the descriptions?

Ie.

001, Start of a really
001, long sentence spread over
001, multiple rows.
002, Different long sentence
002, similarly broken

Turned into...

001, Start of a long sentence spread over multiple rows
002, Different long sentence similarly broken

In reply to Reach>Talent: this looks like one of the problems I've had in the past, when the most convenient way was to save the file as a .csv then open in Word and make use of search and replace to deal with carriage returns and put all the text back together. Save the file as a .txt file and then reopen in Excel.

In reply to Denzil:

Alternatively, you can do a complicated IF(A1 = A2, Concatentate (B1, B2)) type affair.

But the find and replace would probably be easier

In reply to Reach>Talent:

You can use SMALL in an array to pull all the relevant columns and INDEX to then pull the corresponding sentence fragments.

I frequently have to generate data of this type: after a lot of messing about I now just sort the data and use a two stage version of Durbs' solution. First use a 'If(A2=A3,B2,"")' type formula so that each row corresponding to 001 has its sentence fragment duplicated in a new column on the first relevant row. Then I would use CONC to look back across each row. Obviously you also end up with lots of shorter/partial rows, but you can kill those with a de-dupe.

Find and replace sounds good if the format of the data allows?

In reply to Reach>Talent:

Is the text in the cells always the same number of characters? If so, you could use LEFT or RIGHT functions to extract each bit of text, then concatenate it together?

In reply to Reach>Talent:

Quick and dirty solution...

Add a column to check whether the number has changed i.e. =A1=A2

Add another column which cumulatively concatenates the text, starting again when the check column above is TRUE.

Use a filter to select only the rows in which the check columns is true (actually, it would be the row before, but that's easily solved with yet another column!)

Select the number and the cumulative text field columns from the filtered data set, copy and paste to a new sheet

Done

In reply to Oujmik:

That is quick, dirty and pretty much what I have done, ideally I would like a tidier way of doing it as I suspect that this will be a regular occurrence.

In reply to Reach>Talent:

I used to write my school reports using the mother of all 'if...then concatenate' systems. I dumped data from my (very carefully formatted) marksheets, and it used their averages of each type of task (practical, written, calculation, etc) combined with number-coded cells I'd filled in to indicate how friendly, enthusiastic, well behaved, etc each pupil was, and it built a report for each pupil based on pre-written statements. It took forever to put together but over the course of my career it saved me an immense amount of time. You had to look out for siblings getting near identical reports, of course...

So yes, you can get a concatenate system to do almost anything. There is a limit to how many if...then loops you can nest (or at least there was in Excel 2003) - I can't quite remember how many it was. Something like 12 or 15?

In reply to Jamie Wakeham:


I believe the limit was 7 in Excel 2003, since removed and I'm not sure there really is a practical limit any more - certainly more than I've ever wanted and I remember going to great lengths to get around the 7 nested ifs limit.

In reply to john arran:

You could very well be right. I've just had a quick look back (haven't used this spreadsheet for several years now) and I can't see any cell with more than 7 nested loops in it.

It was getting all the instances of he/she his/her right that drove me mad. That and all the spaces...

In reply to john arran:

Once you get towards that level of nested complexity it becomes extremely difficult to figure out, later on, what a formula is doing. Best to write a function in VBA.

In reply to Oujmik:


This. Only using AWK...

Or Python if you're feeling modern...