How To Concatenate multiple non blank cells contents into adjacent column skipping intermediary blank cells in a GoogleSheets Formula?
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 | |
| 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.
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))))
