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.
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))))
- Why does array_udiff() return incorrect results when the callback returns a boolean value?
- Get values that occur in each row of a 2d array
- Compare two 2d arrays and find all intersections in a specific column
- Get the associative differences between two associative arrays
- Print the unique column values from two 2d arrays as <option> tags and declare the "selected" attribute on intersecting values
- Determine if a multidimensional array contains a specific column
- Determine if a specified value exists in a specified column of a 2d array
- Using a for loop to Print and Two-Dimensional Array in Java
- Weird array length behaviour in C (memory corruption?)
- Determine if a column name exists in a 2d array
- Determine if a 2d array containing a single row contains a specified column value
- Get the first level key of the row with a qualifying column value in a 2d array
- Determine if any value from a flat array exists in another flat array
- Power Automate keeps adding an "Apply to Each" and has nested my "Send an Email" action so far down that I'm only getting errors
- Multiple Image upload on array - Javascript/Jquery
- im trying to make an app where it will generate a random video game and show the games cover when its generated
- Determine if a string value exists in a whitelist array
- Presto: Aggregate the arrays for all columns
- How can I prevent extra array elements being created in Javascript?
- To check whether an Associative Array has got a particular value
- Swift JSONDecode decoding arrays fails if single element decoding fails
- How to check if an associative array contains a value and only that value?
- Find values in a flat array not found in another flat array
- How to determine if an array contains anything but a specific value?
- Get all first level keys of rows in a 2d array where a column value is partially matched by a search term
- Determine if a value exists in a 2d array
- Count the number of objects in an array of object with a specified property value
- Sum the correlated values of two array columns
- Count rows of a 2d array which contain a qualifying column value
- Splitting a Map instance into two separate arrays