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:

Please Login or Register  to view this content.

will produce the combinations in columns A & B of Sheet2

[Sherelle]

Thanks, that works great!!