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))))
- How can i take an array, divide it by two and create two lists?
- Most elegant way to convert string array into a dictionary of strings
- In a java enhanced for loop, is it safe to assume the expression to be looped over will be evaluated only once?
- Can't exploit properly an array with twig
- how to use document.getElementById to display the textfield from table using combobox select
- Weird compiler optimization/behavior regarding a "for" loop without a body in Objective-C
- How can I split a string into segments of n characters?
- How to convert int[] to byte[]
- Group rows of a 2d array by a column and create subarrays
- Declare individual variables based on the key names in a 2d array
- Speed up numpy looking for best indices
- Does JavaScript's Array.prototype.fill() always create a packed array?
- Print array without brackets and commas
- Two way binding an array of strings in ng-repeat
- how to pass JSON data though a foreach loop in php
- Pass array value in query post in WordPress
- LINQ - How to correctly group a list of users by their nested list of values
- How can I remove a specific item from an array in JavaScript?
- Remove empty strings from array while keeping record Without Loop?
- What should be the type expression of a 2D array?
- How do I check if an array includes a value in JavaScript?
- Split a 2d array into two 2d grouping arrays by column value
- Jmeter JSON Extractor for Value based off another Value in Array
- Append or prepend to array without VBA/UDF
- JSON schema to enforce array contents
- Javascript: How to implement iterator over multidimensional array?
- Geometric series: calculate quotient and number of elements from sum and first & last element
- How to filter an array to get specific columns in two different objects?
- How to overwrite an array?
- what is array decay in c and when it happen?