1

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:

CONCATENATE VERTICAL NON BLANK CELLS GROUPS

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.

Lod
  • 657
  • 1
  • 9
  • 30
  • 1
    Make sure to add input and expected output as **text table** (NOT as IMAGE/LINK) to the question. [Click here](https://webapps.stackexchange.com/a/161855/) to create a table easily. Adding such tables makes **it is easier to copy/paste**. If you share spreadsheets/images as the only source of data, your question may be closed, as questions here must be [self contained](https://meta.stackoverflow.com/a/260455). Your table should be a [mre].[Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), if you share Google files. – TheMaster Oct 28 '22 at 15:01
  • 1
    Thank @TheMaster for letting me know. I originally tried the table feature but wasn't sure if it would be useful but now I know it's better solution. Good to know about the email issue. I think I know now where the spams are coming from! Be well! – Lod Oct 28 '22 at 16:51
  • 1
    Glad to help. Do you still need a solution? – TheMaster Oct 28 '22 at 16:52
  • Thanks for offering further help I appreciate it a lot! Yes, I can't figure out how to keep the single vertical cells groups with my data. I tried to explain I can't see what's controlling the ommission of it in my 2nd comment in otherwise very helpful below answer by @player0. Any hint what would be the function to look at for the solution? Thanks again. – Lod Oct 28 '22 at 16:56
  • 1
    that would be this part: `IF(INDEX(SPLIT(x, " "),,2)<>"", x, )` – player0 Oct 28 '22 at 18:45
  • 1
    Try `=ARRAYFORMULA(REDUCE("Results",IF((A1:A24="")*(A2:A25<>"")*(A3:A26<>"")+(ROW(A2:A25)=2),ROW(A2:A25),"+"),LAMBDA(a,c,IF(c<>"+",{a;join(,offset(index(A1:A25, c),0,0,XMATCH(TRUE,OFFSET(index(A1:A25, c),0,0,15)="",2)))},{a;""}))))` – TheMaster Oct 28 '22 at 23:34
  • @TheMaster I updated the question after testing your suggestion. Thanks for your next ideas! – Lod Oct 29 '22 at 17:31
  • 1
    I thought E20 shouldn't display anything. Isn't that what your first table says. Column C has nothing. – TheMaster Oct 29 '22 at 17:31
  • Yes correct, I thought first it would make sense to leave out the single cell groups for a separate step because obviously they have nothing to concatenate with, but then I figured out it was more trouble to retrieve as they still would require manual one-by-one copy paste. So in short after all I would need any size of cells groups with no blanks (single ones included) separated by any size of blank cells. My dataset should have up to 13 (1 to 13) cells-lines groups. About Column C, it's the output column. It's only for the concatenated output of your formula, so no input in it, right. – Lod Oct 29 '22 at 17:53
  • 1
    Kindly revert the edits and ask a new question. Your question isn't consistent. Don't change goal posts. It's one thing if you just clarified the original question, but another if you change it into something else. – TheMaster Oct 29 '22 at 17:58
  • Yes, makes sense I thought a new question would be appropriate but wondered if it was right to split it. Next times I'll ask 1st. I'll submit the new question now. Sorry for the mix up. Thanks again. – Lod Oct 29 '22 at 18:01

1 Answers1

2

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))))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    Many Thanks player0! I'll test it asap on large dataset and be back asap to checkmark. – Lod Oct 28 '22 at 12:41
  • 1
    Working perfectly! Thanks again. I'll study the new function and try to figure out how it works asa! Be well! – Lod Oct 28 '22 at 12:57
  • I got final result working in column A here: (same sheet as before, as @TheMaster suggested I'll remove the link but let me know if you need it I'll repost for a short period). Basically it was for putting into one cell a "title cell" content and its subordinates related cells contents separated by blank cells. I spread Column B into Columns C and D using regex, then used your formula in Column E to concatenate the subordinates in Column D, and finally combined Columns C and E into Column A. Curious to know if you could think of a direct formula if possible. What do you think? Thanks again! – Lod Oct 28 '22 at 17:04
  • Sorry for coming back, I'll ask a new question if you prefer, but I found omitting instances of "mono-cells" leaves out some data content. I've tried some tweaking on your formula but can't make it keep the "mono-cells" groups. Would you please point out what's specific to leaving out the single cells groups in your formula? Thanks again – Lod Oct 28 '22 at 17:04
  • @Lod that would be this part: `IF(INDEX(SPLIT(x, " "),,2)<>"", x, )` but it highly depends on your dataset... I tried to throw in a lot of "safety stuff" to counter various scenarios otherwise without that the final formula would be maybe shorter by 1/3 – player0 Oct 28 '22 at 18:49
  • @Lod maybe try: https://i.stack.imgur.com/8wyz1.png – player0 Oct 28 '22 at 18:56
  • I updated the question after testing your formula on new simplified dataset. Thanks for your next input! – Lod Oct 29 '22 at 17:31
  • 1
    @Lod let me just confirm something... you actually want values for mono groups or not? coz as I understood it "skip "mono-cell" instances" you want empty cell in C20 for NN in A20 is that correct? – player0 Oct 29 '22 at 18:11
  • Hi and thanks. Correct, sorry again for the mix up, I should have foreseen the issue but it only occurred to me afterwards... You understood perfectly the skip mono-cell and it was exactly as I needed it. But once I checked further in the dataset I noticed the single cells groups would be too laborious to retrieve to concatenate finally into a single line with previous contents in Columns D and C (in simplified Dataset from https://i.stack.imgur.com/99gWZ.png). Again sorry for the lack of foresight. – Lod Oct 29 '22 at 18:29