How to concatenate ranges using formulas, even when there are blanks to be considered on Google Sheets?

Question

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: https://docs.google.com/spreadsheets/d/1poZW2JAEu419BnOzXVe7-Sq777moXvH_o16NKZtkjAE/edit?usp=sharing

I have tried: =transpose(sheet1A2:C),transpose(sheet1D:F) {sheet1A2:C,sheet1D:F} I have also tried filtering it, but none has worked so far.

Any help will be appreciated.

Cheers, Antonio

Answer 1

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.

Answer 2

use:

=ARRAYFORMULA(QUERY(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.*"))); "×");
 "where Col2 is not null"))

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.*"))); "×"))

Answer 3

Alternative Solution:

You can also try this bound script below and add it to your sheet for a simpler function use:

function transposeBy3(data) {
  var oneData = []
  var final = [];
  var start = 0;
  var end = 3;
  data.forEach(raw => {
    raw.forEach(value => {
      oneData.push(value);
    })
  })
  if(oneData.length%3 == 0){
    for(y=0; y<oneData.length; y++){
      final.push(oneData.slice(start, end));
      start = start + 3;
      end = end + 3;
    }
  }
  return final;
}

Sample

After saving the bound script on your spreadsheet file, on cell B11, you can put this custom function =transposeBy3(B3:J4) as seen below