Unique tranposing: Data with one unique column and all other columns needed

[RichColeman]

Hi All, I hope you can help.

My spreadsheet looks like this:

Name	Grade
Abby	7
Abby	8
Abby	8
Abby	6
Bob	3
Frank	5
Frank	5

I need to be changed so I have unique names and then there 'grades' transposed in columns not rows. Like this:

NAME
Abby	7	8	8
Bob	3
Frank	5	5

Currently I get the unique names with:
=INDEX($A$2:$A$112, MATCH(0, COUNTIF($D$1:$D1, $A$2:$A$112), 0))
Which works.

Where I'm getting stuck is with the grades, I am using this:
=IFERROR(INDEX($B$2:$B$116, MATCH(0, COUNTIF($D3:D3,$B$2:$B$116)+IF($A$2:$A$116<>$D3, 1, 0), 0)), 0)

but the problem I am having is it's only bringing back unique grades and I need all to be displayed in columns

Does anyone have any ideas? I'd be really grateful!

Thanks
Rich

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this.

21 08 12.xlsm
	A	B	C	D	E	F	G	H
1	Name	Grade
2	Abby	7		Abby	7	8	8	6
3	Abby	8		Bob	3
4	Abby	8		Frank	5	5
5	Abby	6
6	Bob	3
7	Frank	5
8	Frank	5
Grades

Cell Formulas
Range	Formula
D2:D4	D2	=UNIQUE(A2:A8)
E2:H2,E4:F4,E3	E2	=TRANSPOSE(FILTER(B2:B8,A2:A8=D2))
Dynamic array formulas.

 

[RichColeman]

Welcome to the MrExcel board!

Try this.

21 08 12.xlsm
	A	B	C	D	E	F	G	H
1	Name	Grade
2	Abby	7		Abby	7	8	8	6
3	Abby	8		Bob	3
4	Abby	8		Frank	5	5
5	Abby	6
6	Bob	3
7	Frank	5
8	Frank	5
Grades

Cell Formulas
Range	Formula
D2:D4	D2	=UNIQUE(A2:A8)
E2:H2,E4:F4,E3	E2	=TRANSPOSE(FILTER(B2:B8,A2:A8=D2))
Dynamic array formulas.

That's great Peter, that's worked a trick! Looks like I was making it to complicated for myself. Thanks for your help!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.
Looks like you were not aware of some of the new functions available in Microsoft 365.

If the data might change, meaning the names list in column D might grow or shrink, the column E formula might be better as shown below. You can copy this further down column E and then if new names are added in column A, those names and their grades will automatically appear in columns D, E, F, ...

=IF(D2="","",TRANSPOSE(FILTER(B2:B8,A2:A8=D2)))

 

[RichColeman]

Yes the data will definitely grow, so I'm sure this formula will be of great use. Many thanks again Peter!