Page 1 of 1
combine STR
Posted: Wed May 19, 2021 2:10 pm
by sna
Hi Dear,
I need a formula to combine text of cells in column B till next non-blank cell in column A
I attached a template with the required result
Best Wishes,
Re: combine STR
Posted: Wed May 19, 2021 2:39 pm
by norkaz
..
If can use CONCAT function.
C2
=MID(CONCAT(IF(A2=LOOKUP(ROW($A$2:$A$8),ROW($A$2:$A$8)/($A$2:$A$8<>""),$A$2:$A$8),","&$B$2:$B$8,"")),2,99)
Ctrl + Shift + Enter
Copy down.
========
If cannot use CONCAT function.
E2 (helper)
=D2&","&INDEX($B$1:$B$8,AGGREGATE(15,6,ROW($2:$8)/($A2=LOOKUP(ROW($2:$8),ROW($2:$8)/($A$2:$A$8<>""),$A$2:$A$8)),COLUMN(A:A)))
Copy to right-hand side and down
C2
=IFERROR(MID(LOOKUP(CHAR(255),E2:Z2),2,99),"")
Copy down.
Norkaz
Re: combine STR
Posted: Thu May 20, 2021 10:26 am
by sna
Thanks it worked