Combine two lists

[rconverse]

Hello,

I have list of product IDs and a list of warehouses. The list of product IDs is about 2900 skus. There are 20 warehouses. How can I take those two lists and combine them into one. So, for each product, I'd like it listed by each warehouse. This would mean my product ID list would go from 2900*20.

Thank you!
Roger

 

[Norie]

Roger

Where do you have the data?

Are there separate worksheets for each warehouse?

 

[rconverse]

Roger

Where do you have the data?

Are there separate worksheets for each warehouse?

Right now it's two lists in one worksheet.

 

[Norie]

So you have 2 lists?

One with the warehouse IDs and one for the SKUs?

 

[rconverse]

So you have 2 lists?

One with the warehouse IDs and one for the SKUs?

That is correct.

 

[xenou]

Sku's across and product codes down ... then you can use a formula:

<img alt="worksheet" src="http://northernocean.net/etc/mrexcel/20110615.png" />

Formula in B2 is:
=B$1&"-"&$A2

-------------------------------------------------------------------------------

I'm getting too tired to think of a better solution but for kicks, a vba solution which will give you one long column and crash if you get over 65,536 rows in Excel 2003:

UNTESTED:

Dim a, b, c
Dim i as Long, j As Long, k As Long

a = Range("A1:A2900").Value
b = Range("B1:B20").Value
Redim c(1 to (Ubound(a,1) * Ubound(b,1)))

For i = 1 to Ubound(a)
    For j = 1 to Ubound(b)
        k = k + 1
        c(k) = a(i) & "-" & b(i)
    Next j
Next i

Range("C1").resize(1 to Ubound(c)).Value = Application.Transpose(c)

-------------------------------------------------------------------------------


For what it's worth I would just import the two worksheets into Access as two tables and run a query: SELECT [SKU] & '-' & [Warehouse] As Warehouse_Sku FROM Table1,Table2;
I think that would also do the trick.

 

[rconverse]

Thanks. I went ahead and just created that query in access. Thanks!