microsoft excel - How to convert column data into rows, grouped by identifier

I have data as shown below in two columns and I need to arrange them horizontally in a row for each individual.

Baker   17                              
Baker   37                              
Baker   28                              
Baker   49                              
Baker   27                              
Baker   44                              
Baker   47                              
Baker   32                              
Baker   29                              
Boley   13                              
Boley   46                              
Boley   10                              
Boley   35                              
Boley   32                              
Boley   49                              
Boley   18                              
Boley   47                              
Boley   22                              

Baker   17  37  28  49  27  44  47  32  29
Boley   13  46  10  35  32  49  18  47  22

How can I do that?

Answer

1. 
   

   Make list of all unique entries from Column A. In this sample, it would be a matter of typing two names. With a larger data set you could copy that column to your target location and just use the Remove Duplicates tool to pare down the list.

   
   


2. 
   

   To the right of the first name in your new list (Mine starts at F1, so I have the following in G1), enter the following formula as an array formula by pressing Ctrl+Shift+Enter:

   
   

   =IFERROR(INDEX($B$1:$B$18,SMALL(IF($A$1:$A$18=$F1,ROW($B$1:$B$18),""),COLUMN()-COLUMN($F1))),"")
   

For reference, A1:A18 is your original list of Names, B1:B18 is their corresponding numbers, and F1 is the name "Baker" in your new table. Fill this over to the right as far as you like. Then, fill down to complete your table.

NOTE: If your original data does NOT start on Row 1, then use the following formula instead, where A1 is the top-left cell of your original data:

=IFERROR(INDEX($B$1:$B$18,SMALL(IF($A$1:$A$18=$F1,ROW($B$1:$B$18)-ROW($A$1)+1,""),COLUMN()-COLUMN($F1))),"")