[FIXED] How To Concatenate multiple non blank cells contents into adjacent column skipping intermediary blank cells in a GoogleSheets Formula?

Issue

I'm facing this problem:

In Column C I need to:

• concatenate each vertical non-blank cells groups from Column A (ignoring the blank cells groups in between) AND,
• only concatenate them once (no duplicate smaller groups in-between) AND,
• skip "mono-cell" instances.

Problem Illustration:

Text Table for easy copying:

Column A	Column B	Column C
AA	1	AABBCC
BB	1
CC	1
	0
	0
DD	1	DDEEFF
EE	1
FF	1
	0
GG	1	GGHH
HH	1
	0
II	1	IIJJKKLLMM
JJ	1
KK	1
LL	1
MM	1
	0
NN	1
	0
	0
OO	1	OOPPQQ
PP	1
QQ	1

So far I found this convoluted solution:

In Column A I have "vertical groups" of cells with content separated by vertical intermediary blank cells.

In Column B I have 0s for corresponding Column A blank cells and 1s for corresponding Column A non blank cells using this pull-down formula:

=if(A2<>"",1,0)

In Column C I have the following "2 Steps" 2nd pull-down Formula:

=IFERROR(IFS(AND(B1<>1,product(B2:B14)=1),concatenate(A2:A14),AND(B1<>1,product(B2:B13)=1),concatenate(A2:A13),AND(B1<>1,product(B2:B12)=1),concatenate(A2:A12),AND(B1<>1,product(B2:B11)=1),concatenate(A2:A11),AND(B1<>1,product(B2:B10)=1),concatenate(A2:A10),AND(B1<>1,product(B2:B9)=1),concatenate(A2:A9),AND(B1<>1,product(B2:B8)=1),concatenate(A2:A8),AND(B1<>1,product(B2:B7)=1),concatenate(A2:A7),AND(B1<>1,product(B2:B6)=1),concatenate(A2:A6),AND(B1<>1,product(B2:B5)=1),concatenate(A2:A5),AND(B1<>1,product(B2:B4)=1),concatenate(A2:A4),AND(B1<>1,product(B2:B3)=1),concatenate(A2:A3),AND(B1<>1,product(C2)=1),""),"")

It works but I'm forced to skip a row to first input the cells content starting in cells A2/B2, and it uses 2 steps as 2nd drawback in Column C.

Would anyone offer a simpler and direct solution? Your help is much appreciated.

Solution

try:

=INDEX(LAMBDA(z, IFNA(VLOOKUP(z, LAMBDA(x, {INDEX(SPLIT(x, " "),,1), 
 SUBSTITUTE(IF(INDEX(SPLIT(x, " "),,2)<>"", x, ), " ", )})
 (FLATTEN(SPLIT(QUERY(IF(z="", "​", z),,9^9), "​"))), 2, )))
 (SUBSTITUTE(A2:INDEX(A:A, MAX((A:A<>"")*ROW(A:A))), " ", CHAR(9))))

Answered By - player0

Answer Checked By - Timothy Miller (PHPFixing Admin)