Turning a column with multiple entries for the same value into array

[Thirith]

I suspect that this will already have been answered elsewhere, but as I'm finding it difficult to put into words what I need to do, I'm finding it equally difficult to come up with good search terms.

Basically I've got this kind of situation: I have a spreadsheet with a list of scores given to items, but the number of scores for an item can differ, so the list might look something like this:

Item A	4
Item A	7
Item B	8
Item B	7
Item B	9
Item C	4
Item C	2
Item C	5
Item D	6

What would be the best/easiest way to turn this into the following?

Item A	4	7
Item B	8	7	9
Item C	4	2	5
Item D	6

 

[denzo36]

assuming you have excel dynamics arrays this will do it

Formula in D2 =UNIQUE(A1:A9)
Formula in E2 =TRANSPOSE(FILTER(B1:B9,A1:A9=D1))

Short of that you would need Power Query

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[JEC]

=LET(t,A1:A9,u,UNIQUE(t),IFERROR(HSTACK(u,DROP(REDUCE("",u,LAMBDA(a,b,VSTACK(a,TOROW(FILTER(B1:B9,t=b))))),1)),""))

 

[JEC]

Here a shorter version

=LET(t,A1:A9,DROP(PIVOTBY(t,MAP(t,LAMBDA(x,SUM(--(A1:x=x)))),B1:B9,MAX,,0,,0),1))

 

[Thirith]

Thanks, this gives me a couple of options to work with.