Converting rows to columns through a unique ID.

[James Curtin]

Hi there, I have a large set of data with unique ID's in column A and multiple names in column B. any idea how I can convert the data to multiple columns rather than rows ?

example
A B
1560 John Smith
1560 Dave Jones
1560 Tom Edwards
123 Jack Thompson
123 Jeremy Evans


How I want the data is.
A B C D
1560 John Smith Dave Jones Tom Edwards
123 Jack Thompson Jeremy Evans

Any ideas ?

[ganeshkumar]

copy the B cell datas and past special in C cell click transpose in window box then ok...

do this u can transpose the cell to cell..

[ganeshkumar]

copy the B cell datas and past special in C cell click transpose in window box then ok...

do this u can transpose the cell to cell..

[Pete_UK]

Use column C as a helper column with a formula like this:

=A1&"_"&COUNTIF(A$1:A1,A1)

then copy this down to the bottom of your data.

Then list your unique IDs, say in column E, so you can have this formula in F1:

=IFERROR(INDEX($B:$B,MATCH($E1&"_"$COLUMNS($F:F),$C:$C,0)),"")

and you can copy this across and down as required.

Hope this helps.

Pete

[James Curtin]

Hi Pete,

Thanks for the response. The first formula worked and created a copy of the ID's. However the second formula brought up an error. And wont allow me to drag the formula through the spreadsheet. I will keep looking for the suitable formula.

Cheers

James

[Pete_UK]

Or, you could just attach your workbook for faster problem-solving...

Pete

[James Curtin]

That would be the easy solution, However working for an education/school company I cannot post data due to data protection.

James

[Pete_UK]

Okay, well just check that you have typed the second formula correctly - you shouldn't get any error reported because of the IFERROR function, so if you do then it must be that the formula has not been formed correctly.

Hope this helps.

Pete