Moving multiple rows into one row

[steveo411]

Hello,

I need to combine multiple rows based on and id number in column 1 into a single row without losing any of the data. I have attached a sample file. It is ok to have multiple instances of information, as it can be parsed out in further steps.

I expect there might be more information needed, but I am unsure what until asked.

Thanks

[alansidman]

Show us a mocked up solution of what you want your data to look like. We'd only be guessing at this point.

[steveo411]

Hello again..... good point!

I have attached a mockup of what I would need.

Thanks

[dosydos]

My example will only work if you have Excel 365
If you do not then please ignore this response.

See attached workbook.
It uses 2 formulas. The first is a UNIQUE() formula on the ID numbers.
The second formula is a combination of TEXTSPLIT() TEXTJOIN() and FILTER() based on the ID# formula.

[steveo411]

I had to do a little modification on the formulas to make it deal with the actual volume of information, but it all looks great!

Thank you

[dosydos]

Glad it worked and thanks for the rep

PS: you should update your MS Office version from 2016 to 365 in your profile.

[HansDouwe]

Another way: The whole table in one go:

=LET(t,SteveStudentRoutingReport202307!A2:I999,n,TAKE(t,,1),u,UNIQUE(n),v,FILTER(u,u),r,HSTACK(v,DROP(IFNA(REDUCE("",v,LAMBDA(i,x,VSTACK(i,TOROW(FILTER(DROP(t,,1),n=x))))),""),1)),r)

In this formula it is not necessary to adjust the references when there are more or less students and or data.