Reformat excel data

[Brianfree]

Hi, does anyone know a way to convert the following rows/columns....

00044	BA538IR
00044	BA608IR
00044	BA630IR
00046	BA362IR
00046	BA503IR
00047	BA362IR
00047	BA503IR
00048	BA196IR
00048	BA609IR
00049	BA1041IR
00049	BA1042IR

into this...

00044	BA538IR, BA608IR, CA630IR
00046	BA362IR, BA503IR
00047	BA362IR, BA503IR
00048	BA196IR, BA609IR
00049	BA1041IR, BA1042IR

Many thanks,

Brian

 

[GlennUK]

Before going any further, can I ask why you want to do this? When data is in a list it is much more useable for Excel analysis, filtering and functions.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[lionelhill]

It's easy to do, but GlennUK is right, don't do it unless you are really really sure it's what you want. It's usually the wrong thing to do.

To do it:
Assuming your data are in columns A and B
(1) sort A and B by A, to make sure all like entries are grouped together.
(2) put in cell C1 the formula =B1
(3) put in cell C2 the formula =IF(A2=A1, C1&", "&B2, B2)
(4) copy this into all cells down column C to the bottom of the data
(5) put in cell D1 the formula =IF(A1<>A2, C1, "")
This will give only the rows that contain a finished piece of text the way you want. Intermediate incomplete rows will be blank.
(6) copy the four columns by value (copy, paste-special) to somewhere spare, and sort by the last column, so that you get rid of all the blank lines. Select the part without blank entries, and sort back by order of code if desired. Delete unwanted lines.