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
New Member
September 26, 2023
Offline
Thanks for the useful answer
New Member
September 27, 2022
Offline
Sorry for necroing a thread, but I had to log in just to reply to this. That formula is absolutely unreal, I've never seen anything like it. @Janset Beyaz, I can't even begin to imagine how you managed to come up with that, but it was exactly what I was looking for! I modified it slightly so that I could count the number of characters in a table, grouped by the Transaction Number.
=LET(
CharacterCount, HSTACK(tblJE[Transaction '#],BYROW(LEN(tblJE[[Transaction '#]:[Remarks]]), LAMBDA(row,SUM(row)))),
REDUCE({"Transaction #","Character Count"}, UNIQUE(TAKE(CharacterCount,,1)), LAMBDA(x,y, VSTACK(x, LET(f,FILTER(CharacterCount, TAKE(CharacterCount,,1)=y), HSTACK(y,SUM(INDEX(f,,2))))))))
There is no way I could have done that without this post. Thank you so much!
1 Guest(s)
