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.
>Solution :
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.
=TRANSPOSE(UNIQUE(FILTER(B2:B10,(A2:A10=D2))))
