arraysexcelexcel-formula

Excel Function: Using a specific criteria to form an array to corresponding values


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)))) enter image description here