How to duplicate rows based on columns? Google sheets (only formulas please)

user_16634030 0
Viewed 219

I have something like this:

Item Color Id
TEST1 WHITE ID001 ST01 ST02 ST03
TEST2 RED ID002 ST02 ST03

And i want to have something like this:

Item Color Id
TEST1 WHITE ID001 ST01
TEST1 WHITE ID001 ST02
TEST1 WHITE ID001 ST03
TEST2 RED ID002 ST02
TEST2 RED ID002 ST03

So basically based on the last 3 columns values, i want to duplicate rows and assign a value from those columns to each row created.

I was trying to use transpose and split formulas, and some other things, but i couldn't get to the result that i want..if is not possible please let me know also.

google-query-language spreadsheet flatten google-sheets split
2 Answers
function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');
  let vs = sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
  sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).clearContent();
  let vA = [];
  vs.forEach((r, i) => {
    if(r.length > 4) {
      let t = r.slice(0,4);
      let u = r.slice(3);
      u.forEach((v,j) => {
        let x = r.slice(0,4);
        x[3] = v;
        vA.push(x);
      });
    } else {
      vA.push(r.slice());
    }
  });
  sh.getRange(2, 1, vA.length, vA[0].length).setValues(vA);
}

Sheet 0 before:

COL1 COL2 COL3 COL4 COL5 COL6
6 6 14 a b c
19 16 16 a b c
2 16 1 a b c

Sheet 0 after:

COL1 COL2 COL3 COL4 COL5 COL6
6 6 14 a
6 6 14 b
6 6 14 c
19 16 16 a
19 16 16 b
19 16 16 c
2 16 1 a
2 16 1 b
2 16 1 c
user_72150910

use:

=INDEX(SPLIT(QUERY(FLATTEN(IF(D1:F="",,A1:A&"×"&B1:B&"×"&C1:C&"×"&D1:F)), 
 "where Col1 is not null"), "×"))

enter image description here

user_56326290
Related