Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

How To Concatenate 1 to 13/any non blank cells groups into adjacent column skipping intermediary blank cells in a GoogleSheets Formula?

My new problem is identical to my previous question asked and kindly answered by @player0 and @TheMaster here:

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

It is identical in all but the size/length of the non blank cells groups to output.
Previously I asked to omit single cells groups as they wouldn’t have any subordinate cells content to concatenate with. But that was a mistake as they’d still require laborious manual extraction one-by-one if omitted.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

So the new problem is as follow:

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,
  • Any or from 1 to 13 at least groups sizes (that’s the only difference needed).

Single Cell Groups remaining issue:

I’ve looked at new Functions REDUCE and LAMBDA but still need to work on grasping them.
@TheMaster thanks for your suggestion, I’ve tested it but it’s not returning any value for the single cell groups (please see the screenshot row 20, E20 doesn’t return A20 value.
What would be the fix you had in mind (I’m not sure what’s the controlling element for the number of row/cells to modify) Thanks again!

Your Formula Tested:

Your Formula Tested

@player0, thanks too for the reply and narrowing down of the formula, though the 2nd shared screenshot appears to be same as 1st one, can’t see the change).
I reproduced a simplified version of my dataset with the issue in next screenshots and below Text Table:

Your Formula In New Test:

Your Formula In New Test

My Regex Formula (To Output Only the Cells With 1s Word followed by 2 whitespaces):

=Arrayformula(if(regexmatch(A1:A,"^(\w+)(\s\s)"),A1:A,""))

My Regex Formula

My Regex Formula Reversed (To Output the other Cells):

=Arrayformula(if(regexmatch(A1:A,"^(\w+)(\s\s)")=FALSE,A1:A,""))

My Regex Formula Reversed

Text Table Simplified Dataset (Ctrl+K issue):

Text Table simplified Dataset pase code

>Solution :

try:

=INDEX(LAMBDA(z, IFNA(VLOOKUP(z, LAMBDA(x, {INDEX(SPLIT(x, " "),,1), 
 SUBSTITUTE(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

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading