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!
What is the source data - a spill range (or several), regular range, or table?
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 |
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.
@Velouria, many thanks...
@austris bahanovskis, glad to have helped and thanks for your feedback
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
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!
