Log In
Register
Home
July 13, 2021
Offline
Hi all again,
Last week I received a brilliant help here and hence I'm reaching out once again.
Still to do with dynamic/spill array formulas.
The scenario - there are data in variable number of rows and some rows have 'duplicate' ids in them and I need to construct a dynamic array formula that for each of the unique ID would return their corresponding multiple DISTINCT values concatenated to one value per row/column.
For instance - source:
| ID | Name | City |
| 1 | John | Toronto |
| 2 | Adam | Paris |
| 1 | John | London |
| 3 | Ana | Madrid |
| 1 | James | Toronto |
| 3 | Ana | Rome |
and the result then would be - because ID=1 has 2 distinct names, 2 distinct cities, ID=2 has only one unique name & city, and ID=3 has 1 distinct name and 2 distinct cities. later on I'll need to make the columns also dynamic, i.e., sometimes I'd need only ID & Name, sometimes just ID & City, but that's the next level - for now I just need to get the 'grouping' to work. My problem is that whenever I tried to chuck 'filter' into =byrow, it fails...
| ID | Name | City |
| 1 | John;James | Toronto;London |
| 2 | Adam | Paris |
| 3 | Ana | Madrid;Rome |
It would be fantastic if again you could help me out here!
See Answer
Trusted Members
Moderators
November 1, 2018
Offline
What is the source data - a spill range (or several), regular range, or table?
Active Member
February 25, 2023
Offline
Hi,
Assuming your data is in the range A1:C7
Name and City / Formula - E2
=REDUCE(A1:C1,UNIQUE(A2:A7),LAMBDA(x,y,VSTACK(x,LET(f,FILTER(A2:C7,A2:A7=y),HSTACK(y,TEXTJOIN(", ",,UNIQUE(INDEX(f,,2))),TEXTJOIN(", ",,UNIQUE(INDEX(f,,3))))))))
| ID | Name | City |
| 1 | John, James | Toronto, London |
| 2 | Adam | Paris |
| 3 | Ana | Madrid, Rome |
Only City / Formula - E8
=REDUCE({"ID","City"},UNIQUE(A2:A7),LAMBDA(x,y,VSTACK(x,LET(f,FILTER(A2:C7,A2:A7=y),HSTACK(y,TEXTJOIN(", ",,UNIQUE(INDEX(f,,3))))))))
| ID | City |
| 1 | Toronto, London |
| 2 | Paris |
| 3 | Madrid, Rome |
Answers Post
The following users say thank you to Janset Beyaz for this useful post:
VelouriaJuly 13, 2021
Offline
Guys - you blow my mind yet again!!
Brilliant - thanks!
Sorry Velouria - I should have specified that - for now it's a table but perhaps it might change to become a spill range but as it's stands it's a table.
Active Member
February 25, 2023
Offline
@Velouria, many thanks...
@austris bahanovskis, glad to have helped and thanks for your feedback
Trusted Members
October 18, 2018
Offline
I would use a different approach. With Power Pivot, build a measure to concatenate the values.
ie. =CONCATENATEX(Range,Range[Name],", ")
You can see the results in the Pivot Table in the file attached
1 Guest(s)
