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))))
- Does JavaScript have a method like "range()" to generate a range within the supplied bounds?
- Local array variables in Bash 3.0
- Check if object with property = value exists in array of objects
- Size of array in C++
- Determine if all the values in a PHP array are null
- How to pass array with multiple objects to Angular FormControl?
- How to Iterate through a grouped object in JavaScript
- Check if a flat array is comprised wholly of true or wholly of false values
- Replace placeholder elements in a flat array with neighboring non-default values
- Javascript - Elements added into an array not in correct place
- Remove empty elements from an array in Javascript
- Why do array element values not change?
- Declaring an optional array argument
- Literal Syntax For byte[] arrays using Hex notation..?
- How to sort an array of objects from least to greatest, based on the value of a specified attribute
- Elegant way to find contiguous subarray within an array in JavaScript?
- Filter an array if a value is encountered 3 or more times
- array_unique for objects?
- merging numpy arrays converts int to decimal
- mysql select query within a serialized array
- Best way of transforming values in array of hashes (performance)
- Convert rows of a 2d array into multi-level groups
- How to get WooCommerce product variation attribute values
- Split String in Swift by their capital letters
- Can you assign an element's class list to a variable by clicking on the element?
- Remove duplicated values from an array
- Filter a multidimensional array (containing elements of irregular depth) using a whitelist of string values
- Group rows of a 2d arrays by a column value and push whole rows into subarrays
- If (Array.Length == 0)
- How to get an array size at compile time?