How to transpose the values in the cells that belong to the same value in the other column

[sktneer]

Hi,
I have a query but I don't know that how to exactly explain it so writing it with the example....
Suppose I have values in the worksheet as below...
A B
1 John
5 John
3 John
7 John
2 Mike
3 Mike
9 Mike
and so on....There are thousands of records. The values in column A belong to a value in column B and I want them in the following way...

A B C D E F G H
John 1 5 3 7
Mike 2 3 9

I can do it manually by copying the values in the column A and transpose them next to the john and Mike but what if the data is too large ?
Is there any easy way to handle this task?
I have attached a file also.

Thanks in advance.

sktneer

[XOR LX]

Hi,

In your sheet in cell C23, enter this array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER) and copy down as required (i.e. for Mike and Clark):

=IFERROR(INDEX($A$3:$A$21,SMALL(IF($B$3:$B$21=$B23,ROW($B$3:$B$21)),COLUMNS($C$1:C$1))-2,1),"")

You will need to copy this to the right for at least as many cells as equals the number of entries in your table for each person's name.

Regards