MS Excel > concat vertical cells within irregular range-CodePudding

I am not sure I can ask about just excel problems on stackoverflow.com. Just let me know gently If I am making mistakes.

I need to concat vertical cells in col3.
Their ranges depend on column 'num' which has numbers like 1(A2), 2(A7), 3(A9).
So the result would be like 'aaa,bbb,ddd,eee', then 'ff', 'gg,abc'.
I tried to use CONCATENATE or TEXTJOIN but I realized it wasn't that simple to do it.

Is there any way to do it? Or do I need to learn VBA? Hope someone knows can guide to do it...

CodePudding user response：

The values in the num column don't appear to affect the sorting.
The TEXTJOIN should work in this instance.
Setting the ignore_empty to TRUE will skip the empty cells.

Formula:

=TEXTJOIN(",",TRUE,D2:D11)

Results:

aaa,bbb,ddd,eee,ff,gg,abc

If you need a vertical list then use the FILTER function.

Formula:

=FILTER(D2:D11,D2:D11<>"")

Result:

aaa
bbb
ddd
eee
ff
gg
abc

CodePudding user response：

Column E is a helper column, you can then add an extra column (F) to get only required results.

Formulas updated to add the commas.

Given the information supplied, you can do this with formula:

Helper Column

Cell E1 - leave blank

Cell E2 =IF(A2="",D1&CHAR(44)&D2,D2)

Cell E3 =IF(A3="",IF(D3="",E2,IF(AND(A2<>"",D2=""),D3,E2&CHAR(44)&D3)),IF(D3="","",D3))

Results Column

Cell F1 =IF(A2<>"",D1,"")

Cell F2 =IF(A3="",IF(E3="",E2,""),E2)

Autofill E3 and F2 to the end of the data range by clicking on the small square on bottom right of the cells and dragging down.

It does not matter what is in Column A, but where there is data in a cell then it will start a new text join chain.

CodePudding user response：

Try this formula - sorry no explanation until later when I will tidy it up:

=LET(freq,DROP(FREQUENCY(IF(A2:A15="",ROW(A2:A15)),IF(A2:A15<>"",ROW(A2:A15))),1) 1,cols,MAX(freq),rows,ROWS(freq),
array,SEQUENCE(rows,cols,0),start,XMATCH(QUOTIENT(array,cols) 1,A2:A15),strings,IF(MOD(array,cols) 1>freq,"",IF(INDEX(D2:D15,start MOD(array,cols))="","",INDEX(D2:D15,start MOD(array,cols)))),
BYROW(strings,LAMBDA(s,TEXTJOIN(",",TRUE,s))))

CodePudding user response：

You can also declare new column in E with following formula (starting from E2:

=IF(ISBLANK(A2),E1,A2)

And from there you'd have to add column F with following formula (starting from F2:

=IF(E2<>E1,TEXTJOIN(",",TRUE,D2),TEXTJOIN(",",TRUE,D2,F1))

Which would result in this: