Join data as a spilled array based on criteria

Hi All,

I have data in B5:AB10000 and want to join/concat them in a single column as an array based on the criteria like joined columns in AF5:AF100 and last row numbers in AG5:AG100.

For example, AF6=g,i and AG6=4 so the unique outcome will be
g,i0,1
g,i0,2

I have attached the expected result and thanks in advance for the support.

??? Bewildering. Please explain how you get teh results from the two shaded rows... highlight the cells making up the result, and explain WHY the solution in the second case also contains d and 222.

Hi Glenn,
Am using the below formula to get the expected result and is working perfectly with small data.
But when combining it with other formulas (as mentioned in Data Sheet AC4) then it takes extremely long time to calculate.
So I was looking for an alternative shorter and simpler formula to increase the speed of calculation please.
Assuming OFFSET(x,,1) could be one of the reasons for slowing the calculation.
Thanks in advance

Please Login or Register  to view this content.