List all Permutations of two lists

[Anthonyinnz]

Greetings. Can someone please help me with this. I have been struggling without making much progress

I want to create a list of all Customers Products and Prices on Sheet 1 from a list of Customers and a list of Products.
The Customers list is on Sheet2 and the Product List is on Sheet3, (or both could be on Sheets 2 if easier)

For example, the Customers could be John and Bill, and the Products List could be
A B C
Product Quantity Price
Widget 1 10.00
Widget 10 9.00
Gizmo 1 2.00

The result on Sheet1 would be
A B C D
Customer Product Quantity Price
John Widget 1 10.00
John Widget 10 9.00
John Gizmo 1 2.00
Bill Widget 1 10.00
Bill Widget 10 9.00
Bill Gizmo 1 2.00

Thank you - Anthony

[shg]

There's a workbook that will do this with two methods, one using formulas and one with VBA, at https://www.box.com/s/47b28f19d794b25511be. You'll need to use a separate lookup formula to get the product quantity and cost.

[Anthonyinnz]

Thanks - have checked that out and it is pretty close.
The macro version would probably suit me best, as then the user does not need to copy any formuales.
Unfortunatly my understanding is not sufficient - I have tried to modify it to meet my requirements but I am currently lost :o)

[shg]

Sorry, I have no idea how to respond to that.

[Anthonyinnz]

I think I need a bit of code that does the following but dont know the syntax - can you help me with that?
(Assuming Sheet2 Columns A=Customers, B=Products, C=Quantity, D=Price)

CustomerCount = Rows in Column A
ProductCount = Rows in Column B
row = 1
For C = 1 to CustomerCount
For P = 1 to ProductCount
Sheet1!Cell("A" & row) = Cell("A" & C)
Sheet1!Cell("B" & row) = Cell("B" & P)
Sheet1!Cell("C" & row) = Cell("C" & P)
Sheet1!Cell("D" & row) = Cell("D" & P)
row = row + 1
Next P
Next C

Thanks Anthony

[mike7952]

If you can upload an example of your workbook with the expected results Im sure someone could help you.

[Anthonyinnz]

Example attached - with two Customers and three Products - in reality there would be many more.
The data would be entered on the Customer and Product tabs, and the output required is on Sheet1 (the Import tab)
Saved as .xls for loading here but .xlsm would be fine.

Thanks Anthony

[Anthonyinnz]

In my attached example, I put the Customers List and the Products list on different sheets, to highlight the fact that they are two separate lists. In practise I could put them as separate columns on the same sheet, if that makes it easier.

[Anthonyinnz]

Eventually Managed to Resolve it myself thanks.

I opted to have my Customerlist on Sheet2, my Productlist on Sheet3, and generate my data on Sheet1 via the following

Option Explicit
Sub Process()
Dim CustomerRow As Long
Dim ProductRow As Long
Dim OutputRow As Long
Sheet1.Cells.Clear 'Get rid of any previous values
OutputRow = 1
Sheet1.Cells(OutputRow, "A") = "Customer"
Sheet1.Cells(OutputRow, "B") = "Product"
Sheet1.Cells(OutputRow, "C") = "Quantity"
Sheet1.Cells(OutputRow, "D") = "Price"
CustomerRow = 2 ' Allow for Headings in Row 1
Do Until Sheet2.Cells(CustomerRow, "A") = ""
ProductRow = 2 ' Allow for Headings in Row 1
Do Until Sheet3.Cells(ProductRow, "B") = ""
OutputRow = OutputRow + 1
Sheet1.Cells(OutputRow, "A") = Sheet2.Cells(CustomerRow, "A")
Sheet1.Cells(OutputRow, "B") = Sheet3.Cells(ProductRow, "A")
Sheet1.Cells(OutputRow, "C") = Sheet3.Cells(ProductRow, "B")
Sheet1.Cells(OutputRow, "D") = Sheet3.Cells(ProductRow, "C")
ProductRow = ProductRow + 1
Loop
CustomerRow = CustomerRow + 1
Loop
End Sub