Create a combined list from two separate lists

Question

I'm using Google Sheets to create a product list which combines two sets of data. I'm wondering if there is a function which will effectively create a list which is a product of the two individual lists.

I can achieve this in a number of ways such as creating an array and then using an offset calculation which I copy down the output column but I'm hoping to find a solution which will take the two lists as arrays and output all of the combinations.

I'm wanting to do it this way because the list lengths are dynamic.

I've looked at ={list_1;list_2} but this just placed the second list under the first list, but it does it as a single cell function.

I've created a spreadsheet which illustrates what I'm trying to achieve and what I've managed to achieve so far...

https://docs.google.com/spreadsheets/d/1Q3lexm06utmI1IE2HmjtNjBKHZc771KVNp1dDjL3jxs/edit?usp=sharing

score 0 · Answer 1 · answered Mar 26 '19 at 19:18

0

If your lists are in A and B columns, put this in the C column and copy down until the length of the product of the two list lengths.

=CONCAT(INDEX(A:A,INT((ROW()-1)/COUNT(B:B))+1),INDEX(B:B,MOD(ROW()-1,COUNT(B:B))+1))

I suppose you could throw in some logic that hides the output if you go too far and then just fill the whole column.

This is adapted from here.

answered Mar 26 '19 at 19:18

shim

9,289
12
69
108

Hi @shim, I'm familiar with this solution but I was looking for a single cell formula, something which works at the array level because, as I mentioned in the posts, the arrays can be of dynamic length. I already have a couple of working solutions, this being one of them the other being the offset solution OFFSET(list_1,TRUNC((ROW()-ROW(list_2))/COLUMNS(list_1),0),MOD(ROW()-ROW(list_2),COLUMNS(list_1)),1,1) which effectively indexes through individual cells as the formula is copied down. – argyrg Mar 26 '19 at 20:27
If you have solved your problem already you are welcome to post an answer to your question. – shim Mar 26 '19 at 20:28
Thanks again for your feedback. While I know that there are ways to accomplish the desired output my question is more focused on finding a solution that will work by entering a formula / function in one cell, without the need to copy down, and which will take two lists of dynamic length as the inputs. – argyrg Mar 26 '19 at 20:36

score 0 · Answer 2 · answered Mar 28 '19 at 17:47

I found the answer elsewhere on stackoverflow.com.

The answer is: =ArrayFormula(transpose(split(rept(concatenate(A2:A&char(9)),counta(B2:B)),char(9)))&" "&transpose(split(concatenate(rept(B2:B&char(9),counta(A2:A))),char(9))) )

and I found it here

Generate all possible combinations for Columns in Google SpreadSheets

score 0 · Answer 3 · answered Mar 29 '19 at 06:32

wrap it into SUBSTITUTE to get rid of that space:

=ARRAYFORMULA(SUBSTITUTE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(A3:A&CHAR(9)),COUNTA(B3:B)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(B3:B&CHAR(9),COUNTA(A3:A))),CHAR(9)))," ",""))

and to sort it use:

=ARRAYFORMULA(SORT(SUBSTITUTE(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(A3:A&CHAR(9)),COUNTA(B3:B)),CHAR(9)))&" "&
 TRANSPOSE(SPLIT(CONCATENATE(REPT(B3:B&CHAR(9),COUNTA(A3:A))),CHAR(9)))," ",""),1,1))

Create a combined list from two separate lists

3 Answers3

Linked