Hi,

I have 3 sheets.
Sheet 1 contains a 1-Column list of Customer_ID's
Sheet 2 contains a 2-Column list of Product_ID's and Product_Descriptions
Sheet 3 is empty.

The result of the macro I want is (in Sheet 3)
A 3-Column list of Customer_ID, Product_ID and Product_Description where
each customer has a full set of products.

Unfortunately, the list of customers, aswell as the list of products will
vary in length each time the macro has to be run.

How do I do this?

I am obviously missing something, but what links the Customer_ID's on Sheet1
with the Product_ID's/Product_Descriptions? I'm guessing there is other
information on the sheets that you have not told us about... I think we need
to know that information (at least as it relates to linking the customers
with the products).

--
Rick (MVP - Excel)


"Pluggie" <(E-Mail Removed)> wrote in message
news:92409B2D-70E4-4161-BE74-(E-Mail Removed)...
> Hi,
>
> I have 3 sheets.
> Sheet 1 contains a 1-Column list of Customer_ID's
> Sheet 2 contains a 2-Column list of Product_ID's and Product_Descriptions
> Sheet 3 is empty.
>
> The result of the macro I want is (in Sheet 3)
> A 3-Column list of Customer_ID, Product_ID and Product_Description where
> each customer has a full set of products.
>
> Unfortunately, the list of customers, aswell as the list of products will
> vary in length each time the macro has to be run.
>
> How do I do this?

There is no other link and no other information.

The information on sheet 1 and sheet 2 is imported by another macro before
running this one.

So every Customer needs to get the full set of products each time the macro
is run.

Example:

Sheet 1:
Customer_ID
1
2
3

Sheet 2:
ProductID Product_Descr
9 Book
8 Chair

Macro that makes sheet 3:

Customer_ID ProductID ProductDescr
1 8 Chair
1 9 Book
2 8 Chair
2 9 Book
3 8 Chair
3 9 Book

"Rick Rothstein" wrote:

> I am obviously missing something, but what links the Customer_ID's on Sheet1
> with the Product_ID's/Product_Descriptions? I'm guessing there is other
> information on the sheets that you have not told us about... I think we need
> to know that information (at least as it relates to linking the customers
> with the products).
>
> --
> Rick (MVP - Excel)
>
>
> "Pluggie" <(E-Mail Removed)> wrote in message
> news:92409B2D-70E4-4161-BE74-(E-Mail Removed)...
> > Hi,
> >
> > I have 3 sheets.
> > Sheet 1 contains a 1-Column list of Customer_ID's
> > Sheet 2 contains a 2-Column list of Product_ID's and Product_Descriptions
> > Sheet 3 is empty.
> >
> > The result of the macro I want is (in Sheet 3)
> > A 3-Column list of Customer_ID, Product_ID and Product_Description where
> > each customer has a full set of products.
> >
> > Unfortunately, the list of customers, aswell as the list of products will
> > vary in length each time the macro has to be run.
> >
> > How do I do this?
>
>

This should do what you're asking for:

Sub Order_List()
Dim nCust As Long, nProd As Long
'
nCust = ActiveWorkbook.Worksheets("Sheet1").Cells(1,
1).CurrentRegion.Rows.Count - 1
nProd = ActiveWorkbook.Worksheets("Sheet2").Cells(1,
1).CurrentRegion.Rows.Count - 1
'
Application.ScreenUpdating = False
'
ActiveWorkbook.Worksheets("Sheet3").Cells(1, 1) = _
ActiveWorkbook.Worksheets("Sheet1").Cells(1, 1)
ActiveWorkbook.Worksheets("Sheet3").Cells(1, 2) = _
ActiveWorkbook.Worksheets("Sheet2").Cells(1, 1)
ActiveWorkbook.Worksheets("Sheet3").Cells(1, 3) = _
ActiveWorkbook.Worksheets("Sheet2").Cells(1, 2)
For i = 1 To nCust
For j = 1 To nProd
ActiveWorkbook.Worksheets("Sheet3").Cells((i - 1) * nProd + j + 1,
1) = _
ActiveWorkbook.Worksheets("Sheet1").Cells(i + 1, 1)
ActiveWorkbook.Worksheets("Sheet3").Cells((i - 1) * nProd + j + 1,
2) = _
ActiveWorkbook.Worksheets("Sheet2").Cells(j + 1, 1)
ActiveWorkbook.Worksheets("Sheet3").Cells((i - 1) * nProd + j + 1,
3) = _
ActiveWorkbook.Worksheets("Sheet2").Cells(j + 1, 2)
Next j
Next i
'
ActiveWorkbook.Worksheets("Sheet3").Select
Application.ScreenUpdating = True
'
End Sub


HTH,

Eric

"Pluggie" wrote:

> Hi,
>
> I have 3 sheets.
> Sheet 1 contains a 1-Column list of Customer_ID's
> Sheet 2 contains a 2-Column list of Product_ID's and Product_Descriptions
> Sheet 3 is empty.
>
> The result of the macro I want is (in Sheet 3)
> A 3-Column list of Customer_ID, Product_ID and Product_Description where
> each customer has a full set of products.
>
> Unfortunately, the list of customers, aswell as the list of products will
> vary in length each time the macro has to be run.
>
> How do I do this?

I think this will do what you have asked for...

Sub CombineCustomersWithProducts()
Dim R As Range
Dim X As Long, Z As Long
Dim WS1 As Worksheet, WS2 As Worksheet, WS3 As Worksheet
Dim LastRow1 As Long, LastRow2 As Long, LastRow3 As Long
Set WS1 = Worksheets("Sheet1")
Set WS2 = Worksheets("Sheet2")
Set WS3 = Worksheets("Sheet3")
LastRow1 = WS1.Cells(WS1.Rows.Count, "A").End(xlUp).Row
LastRow2 = WS2.Cells(WS1.Rows.Count, "A").End(xlUp).Row
Set R = WS2.Range("A2:B" & LastRow2)
WS3.Range("A1").Value = WS1.Range("A1").Value
WS2.Range("A1:B1").Copy WS3.Range("B1")
For X = 2 To LastRow1
LastRow3 = WS3.Cells(WS1.Rows.Count, "A").End(xlUp).Row
For Z = LastRow3 + 1 To LastRow3 + R.Rows.Count
WS3.Cells(Z, "A").Value = WS1.Cells(X, "A").Value
Next
R.Copy WS3.Cells(LastRow3 + 1, "B")
Next
End Sub

--
Rick (MVP - Excel)


"Pluggie" <(E-Mail Removed)> wrote in message
news:CB4BFFC9-E667-42A2-AA57-(E-Mail Removed)...
> There is no other link and no other information.
>
> The information on sheet 1 and sheet 2 is imported by another macro before
> running this one.
>
> So every Customer needs to get the full set of products each time the
> macro
> is run.
>
> Example:
>
> Sheet 1:
> Customer_ID
> 1
> 2
> 3
>
> Sheet 2:
> ProductID Product_Descr
> 9 Book
> 8 Chair
>
> Macro that makes sheet 3:
>
> Customer_ID ProductID ProductDescr
> 1 8 Chair
> 1 9 Book
> 2 8 Chair
> 2 9 Book
> 3 8 Chair
> 3 9 Book
>
> "Rick Rothstein" wrote:
>
>> I am obviously missing something, but what links the Customer_ID's on
>> Sheet1
>> with the Product_ID's/Product_Descriptions? I'm guessing there is other
>> information on the sheets that you have not told us about... I think we
>> need
>> to know that information (at least as it relates to linking the customers
>> with the products).
>>
>> --
>> Rick (MVP - Excel)
>>
>>
>> "Pluggie" <(E-Mail Removed)> wrote in message
>> news:92409B2D-70E4-4161-BE74-(E-Mail Removed)...
>> > Hi,
>> >
>> > I have 3 sheets.
>> > Sheet 1 contains a 1-Column list of Customer_ID's
>> > Sheet 2 contains a 2-Column list of Product_ID's and
>> > Product_Descriptions
>> > Sheet 3 is empty.
>> >
>> > The result of the macro I want is (in Sheet 3)
>> > A 3-Column list of Customer_ID, Product_ID and Product_Description
>> > where
>> > each customer has a full set of products.
>> >
>> > Unfortunately, the list of customers, aswell as the list of products
>> > will
>> > vary in length each time the macro has to be run.
>> >
>> > How do I do this?
>>
>>

Hi Eric,

Tried it... had to delete your quotes from the code first and it worked.

Thanks very much!!!

"EricG" wrote:

> This should do what you're asking for:
>
> Sub Order_List()
> Dim nCust As Long, nProd As Long
> '
> nCust = ActiveWorkbook.Worksheets("Sheet1").Cells(1,
> 1).CurrentRegion.Rows.Count - 1
> nProd = ActiveWorkbook.Worksheets("Sheet2").Cells(1,
> 1).CurrentRegion.Rows.Count - 1
> '
> Application.ScreenUpdating = False
> '
> ActiveWorkbook.Worksheets("Sheet3").Cells(1, 1) = _
> ActiveWorkbook.Worksheets("Sheet1").Cells(1, 1)
> ActiveWorkbook.Worksheets("Sheet3").Cells(1, 2) = _
> ActiveWorkbook.Worksheets("Sheet2").Cells(1, 1)
> ActiveWorkbook.Worksheets("Sheet3").Cells(1, 3) = _
> ActiveWorkbook.Worksheets("Sheet2").Cells(1, 2)
> For i = 1 To nCust
> For j = 1 To nProd
> ActiveWorkbook.Worksheets("Sheet3").Cells((i - 1) * nProd + j + 1,
> 1) = _
> ActiveWorkbook.Worksheets("Sheet1").Cells(i + 1, 1)
> ActiveWorkbook.Worksheets("Sheet3").Cells((i - 1) * nProd + j + 1,
> 2) = _
> ActiveWorkbook.Worksheets("Sheet2").Cells(j + 1, 1)
> ActiveWorkbook.Worksheets("Sheet3").Cells((i - 1) * nProd + j + 1,
> 3) = _
> ActiveWorkbook.Worksheets("Sheet2").Cells(j + 1, 2)
> Next j
> Next i
> '
> ActiveWorkbook.Worksheets("Sheet3").Select
> Application.ScreenUpdating = True
> '
> End Sub
>
>
> HTH,
>
> Eric
>
> "Pluggie" wrote:
>
> > Hi,
> >
> > I have 3 sheets.
> > Sheet 1 contains a 1-Column list of Customer_ID's
> > Sheet 2 contains a 2-Column list of Product_ID's and Product_Descriptions
> > Sheet 3 is empty.
> >
> > The result of the macro I want is (in Sheet 3)
> > A 3-Column list of Customer_ID, Product_ID and Product_Description where
> > each customer has a full set of products.
> >
> > Unfortunately, the list of customers, aswell as the list of products will
> > vary in length each time the macro has to be run.
> >
> > How do I do this?