I have a problem whereby I am unable to form an array based on a certain criteria and have been racking my brains for hours & would appreciate any assistance on this.
I have a massive data dump containing of an ID tag (non-unique) to the client's name in which if they are from the same family they will be tagged to the same ID. I am trying to create an array as shown in Sheet 2 based on the criteria in Column A.
Sheet1: Containing the Raw Data file that I have.
ID | Names |
---|---|
1 | John |
2 | Alan |
3 | Ray |
2 | David |
2 | Sean |
2 | Darren |
1 | Jerry |
1 | Charles |
3 | Kelvin |
Sheet2: How I want the data to populate based on criteria in column.
ID | Name1 | Name2 | Name3 | Name4 |
---|---|---|---|---|
1 | John | Jerry | Charles | |
2 | Alan | David | Sean | Darren |
3 | Ray | Kelvin |
Sheet1: Containing the Raw Data file that I have
Sheet2: How I want the data to populate based on criteria in column A
Appreciate any inputs on this please!
I've tried to use a number of formulas consisting of SMALL/ROW and using Ctrl+Shift+Enter but it always turns out a blank or error.
Assuming you have Excel 2021 or 365, you can FILTER
the range by a criteria, in this case, the family ID, and then take a UNIQUE
of the resulting names. Finally, use the `TRANSPOSE function to make the vertical list into a horizontal one.