Search code examples
arraysgoogle-sheetsconcatenationgoogle-sheets-formulaformula

How to concatenate two dimensional ranges, while ignoring blanks from both ranges?

I have this google sheets file with input tabels: Input Range 01 and Input Range 02
How to get the output table using the simplest formula and the fewest range references possible? and if lambda and lhf can be avoided is better. see this question

Input Range 01

E F G
Set 1 Set 2 Set 3
Set 1 Set 2 Set 3
Set 1 Set 2 Set 3
Set 1 Set 2
Set 1 Set 2 Set 3
Set 2 Set 3

Input Range 02

A B C
Category 1 Category 1 Category 1
Category 2 Category 2
Category 3 Category 3
Category 4 Category 4 Category 4
Category 6 Category 6 Category 6

Output

I J K
Set 1 Category 1 Set 2 Category 1 Set 3 Category 1
Set 2 Category 2 Set 3 Category 2
Set 1 Category 3 Set 2 Category 3
Set 1 Category 4 Set 2 Category 4
Set 2 Category 6 Set 3 Category 6

enter image description here

Here what i tryed

=ARRAYFORMULA(A2:C7&" "&E2:G7)

and tried to clean it with merging true and false ranges for empty cells in both ranges. With the previous formula

=ARRAYFORMULA(LAMBDA(f, FILTER(f,f<>"" )(IF((A2:C>1*(A2:C<>""))*(E2:G>1*(E2:G<>""))=1,A2:C&" "&E2:G,""))))

At that point i was circling in loops to get it to work and...this error. If possible, avoid using Lambda due to its limitations. I know i created alot of references. I was planning to replace them with Lambda names, but it is out of consideration only if bypassed limitations with ignoring blanks IF(Range="",,[The rest of the formula]).

enter image description here

Solution 
  • =ARRAYFORMULA(LAMBDA(rg,QUERY(IF((rg<>"")*(OFFSET(rg,0,4)<>""),OFFSET(rg,0,4)&" "&rg,),"where "&JOIN(" or ","Col"&SEQUENCE(COLUMNS(rg))&" is not null "),0))(A2:C7))

    has the fewest range references using LAMBDA. & is used for dynamic concatenation. Uses OFFSET to reference the other range and QUERY's where Col is not null to check if category is empty.

    MAP is another alternative.MAP provides one value from each of the provided ranges:

    =ARRAYFORMULA(LAMBDA(rg,QUERY(MAP(rg,OFFSET(rg,0,4),LAMBDA(cat,set,IF(cat="",,IF(set="",,set&" "&cat)))),"where "&JOIN(" or ","Col"&SEQUENCE(COLUMNS(rg))&" is not null"),0))(A2:C7))