Formula to join values, separate them by commas, multiple rows, columns and multiple conditions

Question

I have two columns. Column A has multiple group types (i.e.: Group1, Group2, Group3) and Column B is a list of domains (i.e.: a.com, b.com, c.com, etc.).

The result I need:

• To join, in a single cell, Domains from the same Group, separated by commas.
• Regardless of the number of Domains from a single Group, this string of comma-separated values must not surpass a certain limit number of Domains.
• The remaining Domains from the Group would appear in the following row, separated by commas (as many rows as necessary, never surpassing the maximum number of domains).
• The adjacent column, to the left, would indicate the Group to which each string of Domains belongs.

In this example, there are three Groups and 26 Domains. The limit number of Domains per string is five: https://docs.google.com/spreadsheets/d/1q1LkUyOkdiNvHjw78S4EXx1xw2n1ggeYRbDUhOwAgcc/edit#gid=0 ​

I don't know if this is too complex... hopefully, someone can share some insight. Thanks in advance.

Answer 1

You asked this question on the Google Forums as well. I posted my solution there since that's where I usually volunteer my efforts, but Player0 suggested I post here as well so here I am.

On your sample sheet on the tab called MK.Solution, you'll find this formula. I think it should get what you're after and work on larger datasets. I'm new to S.E. so I'm not entirely sure the protocol for including sheets formulae, so I'll just paste it for now and hopefully figure that out soon.

=ARRAYFORMULA(QUERY({SPLIT(UNIQUE(B2:B&"-"&INT((COUNTIFS(B2:B,B2:B,ROW(B2:B),"<="&ROW(B2:B))-1)/5)),"-"),TRANSPOSE(SUBSTITUTE(TRIM(QUERY(IF(B2:B&"-"&INT((COUNTIFS(B2:B,B2:B,ROW(B2:B),"<="&ROW(B2:B))-1)/5)=TRANSPOSE(UNIQUE(B2:B&"-"&INT((COUNTIFS(B2:B,B2:B,ROW(B2:B),"<="&ROW(B2:B))-1)/5))),C2:C&",",),,9^99)&"|"),", |",""))},"select Col1,Col3 where Col1<>'1' order by Col1"))

Cheers, Matt