A complex Join of tables

[ViridianAnalytics]

Hi everybody,
This is my first thread, so be gentlemen
1. I have two tables that I would like to join.
2. As an initial step, I would like to reorganize one of the tables, and I don't know what to do:

REG_KEY REGISTRATION_LISTING_ID ESTABLISHMENT_TYPE_ID
5 10856 5
5 10856 11
5 571486 5
5 571486 11
5 576175 5
5 576175 11
5 646085 11
6 10860 5
6 10860 11
6 672233 7
6 672233 9
6 672233 11

As can be seen above, column A link values in column B, a portion of which are essentially unique values, that in turn link to specific values in column C.
I essentially want to merge the unique values in columns A and B - the 5 and 10856 for instance - while the related values in column C - 5 and 11 - will be merged into one cell, and will be separated by a comma:


5 | 10856 | 5, 11

5 | 571486 | 5, 11
....
6 | 10860 | 5, 11

Thank you very much!

[Glenn Kennedy]

Next time can we have an Excel file? As you can see, the forum mucked up your formatting, big time.

If your O365 is up to date, you can use:

=LET(a,A2:A13,b,B2:B13,c,C2:C13,L,UNIQUE(b),K,XLOOKUP(L,b,a),T,DROP(REDUCE("",L,LAMBDA(x,y,VSTACK(x,TEXTJOIN(", ",,FILTER(c,b=y))))),1),HSTACK(K,L,T))

[ViridianAnalytics]

Hi Glenn,
WOW, Thank you so much!
I tried to upload an illustration, but unfortunately, it didn't succeed. Next time I will upload the xls sheet itself - it is a great idea

BR

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

[Glenn Kennedy]

Oh, and a picture of a sheet is worse still. We can copy stuff (however badly the forum formats it) from a thread, but can do NOTHING with a non-editable picture.

[ViridianAnalytics]

OK, got it, Glenn.

I do the best

[ViridianAnalytics]

By the way,
The original table is much bigger (containing approximately 400-K rows!).
I tried to expand the function of the original range, but it does not work. Does the function have any limitations?

Thank you!

[Glenn Kennedy]

TEXTJOIN will fail at 32,767 characters.

Option 1. VBA (someone, not me) will be able to do it.

Option 2. Spill the results into separate cells in the same row. To follow.

Option 3 Spilt the function into 2, which might work. Formula in yellow cells needs copied down.

[Glenn Kennedy]

Option 2....

[ViridianAnalytics]

Thank you so much, Glenn! It all very-very useful for me!

I want to add a smiley over any of your replies instead of adding more and more threads.

[Glenn Kennedy]

No problem! Any time...

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.