2

I am trying to combine text from column A and match it with each possibility of column B.I used the formulas:

in C1:

=transpose(split(join("", arrayformula(rept(filter(A1:A, len(A1:A))&char(9999), counta(B1:B)))), char(9999)))

in D1:

=transpose(split(rept(join(char(9999), filter(B1:B, len(B1:B)))&char(9999), counta(A1:A)), char(9999)))

but when I use it in my list I get these errors in C1 and D1 respectively;

Text result of JOIN is longer than the limit of 50000 characters
Text result of REPT is longer than the limit of 32000 characters

I tested this out with a smaller list of just:

a b c 1 2

and managed to get my list to generate this after combining the two cells:

a 1
a 2
a 3
b 1
b 2
b 3

but the list I am combining has a lot more text in each of the columns.

Any suggestions on how to combine my lists as shown above but with 132 possibilities in column A and 52 possibilities in column B?

Each line has between 70 and 150 characters of text in each row.

Community
  • 1
  • 1
Colin Vatcher
  • 21
  • 1
  • 3

1 Answers1

4

Go to menu Tools → Script Editor...

Paste this code:

function crossJoin(arr1, arr2, delim) {

  delim = delim || '';

  var result = [];
  var row = [];
  for (var i = 0; i < arr1.length; i++) {
    for (var j = 0; j < arr2.length; j++) {
      row = [];
      row.push('' + arr1[0,i] + delim + arr2[0,j]);
      result.push(row);
    }   
  }
  return result;
}

Save project.

Use it as regular function in spreadsheet:

=crossJoin(A1:A132,B1:B52)

Optionaly use delimeter:

=crossJoin(A1:A132,B1:B52, "-")

Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
  • this worked!!!!!!!!!! thank you so much!!!! this saves me so much time. The original formula I was using worked if I was using a small data set. but I put this in and got all 6000+ items. You are fantastic. – Colin Vatcher Feb 28 '17 at 17:54
  • This seems to not work anymore. My scripted function still suffers from the 50 000 characters limitation. – Charles Oct 04 '21 at 15:16