Combining two separate lists and creating new values

[ginase]

I'm sure there's a way to do this but just can't seem to get it. I hav
two separate lists in Excel. One is customer account numbers, of whic
there are 50, and one is part numbers, of which there are 350. Eac
account number only appears once and each part number only appears once


I need to upload these to another program and so need to create an Exce
sheet where in column A I have each account number listed and in colum
B I have each part number listed (meaning that each account number wil
be listed 350 times as there will be one row for each part numbe
against that account number). In total there will be 17500 entries.
What I'm trying to avoid doing is having to copy and paste to achiev
this!

I'd appreciate any help :

 

[isabelle]

hi ginase,

assume that the data is on Sheet1, account numbers are in the range A1:A50 and part numbers in the range B1:B350
the result will be placed on Sheet2


Sub test()
Dim i As Integer, x As String, y As String, n As Integer
For i = 1 To 17500 Step 350
n = n + 1
With Sheets("Sheet2")
Sheets("Sheet1").Range("A" & n).Copy .Range(.Cells(i, 1), .Cells(i + 349, 1))
Sheets("Sheet1").Range("B1:B350").Copy .Range(.Cells(i, 2), .Cells(i + 349, 2))
Application.CutCopyMode = False
End With
Next
End Sub


--
isabelle




Le 2012-04-24 10:48, ginase a écrit :

 

[Mazzaropi]

hi ginase,
assume that the data is on Sheet1, account numbers are in the rang
A1:A50 and part numbers in the range B1:B350
the result will be placed on Sheet2

Sub test()
Dim i As Integer, x As String, y As String, n As Integer
For i = 1 To 17500 Step 350
n = n + 1
With Sheets("Sheet2")
Sheets("Sheet1").Range("A" & n).Copy .Range(.Cells(i, 1), .Cells(i
349, 1))
Sheets("Sheet1").Range("B1:B350").Copy .Range(.Cells(i, 2), .Cells(
+ 349, 2))
Application.CutCopyMode = False
End With
Next
End Sub

Dear *Isabelle*, Good Morning.

Your code works perfectly. Congratulations.

I tried to adapt this code to a three column of data.
But I´m getting errors.
Please, could you help me with this?

I have three columns of data:
A1:A50 - Music partitures
B1:B20 - Musical instruments
C1:C3 - Participation

50 itens * 20 itens * 3 itens = 3,000 itens

If anyone can help I will apreciate a lot.
I´m a beginner in VBA.

Thanks in advance.

Have a nice day

 

[isabelle]

hi,

Sub test()
Dim i As Integer, n1 As Integer, n2 As Integer
n1 = 1
For i = 1 To 3000 Step 3
n2 = n2 + 1
With Sheets("Sheet2")
Sheets("Sheet1").Range("A" & n1).Copy .Range(.Cells(i, 1), .Cells(i + 2, 1))
Sheets("Sheet1").Range("B" & n2).Copy .Range(.Cells(i, 2), .Cells(i + 2, 2))
Sheets("Sheet1").Range("C1:C3").Copy .Range(.Cells(i, 3), .Cells(i + 2, 3))
Application.CutCopyMode = False
End With
If n2 = 20 Then n1 = n1 + 1: n2 = 0
Next
End Sub



--
isabelle



Le 2012-04-30 14:30, Mazzaropi a écrit :

 

[Mazzaropi]

Dear *Isabelle*, Good Evening.

Thank you very much for taking the time and effort on this.
Its very much appreciated.

Have a nice day.

 

[isabelle]

my pleasure! have a nice day too

--
isabelle



Le 2012-05-02 22:29, Mazzaropi a écrit :