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

I have this google sheets

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

CodePudding user response：

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