Make and regularly update a list of all combinations of two lists of unique values

[Sherelle]

Hi,

I am working on a spreadsheet where there is a list of unique store codes (500+) and a list of unique product codes (300+). I need to populate and regularly refresh a list of all combinations of the store and products. Can anyone help me work out a way to do this, please?

For example:

Column 1 - Column 2
Store 1 - Product 1
Store 1 - Product 2
Store 1 - Product 3
Store 1 - Product 4
Store 1 - Product 5 etc.
Store 2 - Product 1
Store 2 - Product 2
Store 2 - Product 3
Store 2 - Product 4
Store 2 - Product 5 etc.

Thanks in advance for your help.

[jeffreybrown]

How about a pivot table?

[Sherelle]

Thanks for the idea. I am pulling data from 4 different sheets within one workbook to create one 'master' sheet of information (which is where I need a list of stores > products), which can then feed into a pivot table so that we can analysie the customers sales etc. Therefore, initially I was thinking a pivot table might not work due to the different formats of the 4 different sheets, however I will have a play with it and see.

Also someone offline wrote me a macro that would work as well, so one way or another it should get resolved soon... Thanks

[Jakobshavn]

If the store id's are in column A of Sheet1 and the product id's are in column B of Sheet1, then this macro:

Sub MakeComb()
Dim s1 As Worksheet
Dim s2 As Worksheet
Dim N1 As Long, N2 As Long, K As Long, I As Long, J As Long
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
K = 1
N1 = s1.Cells(Rows.Count, 1).End(xlUp).Row
N2 = s1.Cells(Rows.Count, 2).End(xlUp).Row
For I = 1 To N1
    For J = 1 To N2
        s2.Cells(K, 1) = s1.Cells(I, 1)
        s2.Cells(K, 2) = s1.Cells(J, 2)
        K = K + 1
    Next
Next
End Sub

will produce the combinations in columns A & B of Sheet2

[Sherelle]

Thanks, that works great!!