

July 13, 2021

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!


Trusted Members
Moderators

November 1, 2018


Active Member

February 25, 2023

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:
Velouria

Trusted Members

October 18, 2018

1 Guest(s)
