Google Spreadsheet - M2M Concatenator

Question

I have an issue with Google Spreadsheet. From the example table above, I want to generate exactly like column C. I understand that this cannot be achieved using regular formulas alone, but requires a script. Is there anyone who can help me?

Answer 1

Give a try to the following formula-

=LET(
lr,COUNTA(B1:B),
y,A1:INDEX(A1:A,lr),
z,B1:INDEX(B1:B,lr),
MAP(y,LAMBDA(t,IF(t="","",TEXTJOIN(",",1,FILTER(z,SCAN(,y,LAMBDA(p,q,IF(q="",p,q)))=t)))))
)

Answer 2

In the case of Google Apps Script, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet and save the script. When you use this script, please put a custom function of =SAMPLE(A2:B) into a cell "C2". By this, the result values are returned.

function SAMPLE(values) {
  const len = values.length;
  const { rows } = values.slice().reverse().reduce((o, [a], i) => {
    o.c++;
    if (a !== "" || i == len - 1) {
      o.rows.push(o.c);
      o.c = 0;
    }
    return o;
  }, { c: 0, rows: [] });
  rows.reverse();
  const res = rows.flatMap(e => [[values.splice(0, e).map(([, b]) => b).filter(String).join(",")], ...Array(e - 1).fill([null])]);
  return res;
}

Testing:

When this script is run, the following result is obtained. In my test, this custom function could be used for 6,000 rows.

Note:

If the custom function cannot be used, please test the following script. In this script, the values are retrieved from cells "A2:B" and put the result to cells "C2:C". When I tested this script with 6,000 rows, the process time was about 3 seconds.

function myFunction() {
  const sheetName = "Sheet1"; // Please set your sheet name.

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const range = sheet.getRange("A2:B" + sheet.getLastRow());
  const values = range.getValues();
  const len = values.length;
  const { rows } = values.slice().reverse().reduce((o, [a], i) => {
    o.c++;
    if (a !== "" || i == len - 1) {
      o.rows.push(o.c);
      o.c = 0;
    }
    return o;
  }, { c: 0, rows: [] });
  rows.reverse();
  const res = rows.flatMap(e => [[values.splice(0, e).map(([, b]) => b).join(",")], ...Array(e - 1).fill([null])]);
  range.offset(0, 2, res.length, 1).setValues(res);
}

References:

• Custom Functions in Google Sheets
• reduce()
• map()
• Class Sheet
• Class Range

Answer 3

You can try the following LAMBDA-less solution, it won't run into calculation limit but it could be slow because it creates a 2D array that has the size of the amount of rows in column B from row 2 up to the last non-empty row.

=ARRAYFORMULA(
   LET(a_,A2:A,
       b_,B2:B,
       a,SINGLE(a_):INDEX(a_,MATCH(,0/LEN(b_))),
       b,SINGLE(b_):INDEX(b_,MATCH(,0/LEN(b_))),
       cc,COUNTIFS(a,"<>",ROW(a),"<="&ROW(a)),
       SUBSTITUTE(
         SUBSTITUTE(
           TRIM(TOCOL(
             QUERY(SUBSTITUTE(IF(cc=TOROW(a),b,)," ","ζ"),,9E+99))),
           " ",", "),
         "ζ"," ")))

Note: This formula assumes that the values in column A do not repeat.