How to concatenate ranges using formulas, even when there are blacks to be considered on Google Shee-CodePudding

The problem consists of concatenating multiple horizontal ranges containing product1, brand1, price1, product2, brand2, price2 and so on, even when the cells are blank.

Here's a link to a working example:

update:

=ARRAYFORMULA(SPLIT(FLATTEN(A3:A4&"×"&
 FILTER(A3:4; REGEXMATCH(A2:2; "Product.*"))&"×"&
 FILTER(A3:4; REGEXMATCH(A2:2; "Brand.*"))&"×"&
 FILTER(A3:4; REGEXMATCH(A2:2; "Price.*"))); "×"))

CodePudding user response：

Alternative Solution:

You can also try this

CodePudding user response：

I will answer according to what you have shared, with the assumption that your headers will not be "Product1" or "Brand1" but rather actual names of products and brands which are not similar to one another.

First, never put dissimilar charts or results below a working database that will grow over time. For this reason, I have added two new sheets to your spreadsheet. The first is a duplicate of your first sheet ("Página1 - Erik") which only has your database headers and data. The results formula is then in another sheet ("Erik Help"), in cell A2. This formula refers to the cleaned sheet "Página1 - Erik":

=ArrayFormula(SPLIT(FLATTEN(FILTER('Página1 - Erik'!A3:A&"|"&FILTER('Página1 - Erik'!B3:J;MOD(COLUMN('Página1 - Erik'!B3:J3)-2;3)=0)&"|"&FILTER('Página1 - Erik'!B3:J;MOD(COLUMN('Página1'!B3:J3)-3;3)=0)&"|"&FILTER('Página1 - Erik'!B3:J;MOD(COLUMN('Página1 - Erik'!B3:J3)-4;3)=0);'Página1 - Erik'!A3:A<>""));"|";1;0))

It is a similar approach to what player0 offered, but instead of relying on similarity of header text, it relies on column patterns.

I will leave it to you to modify the formula as necessary to apply to your actual data set when the time comes.