Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Combining two separate lists and creating new values
I'm sure there's a way to do this but just can't seem to get it. I have two separate lists in Excel. One is customer account numbers, of which there are 50, and one is part numbers, of which there are 350. Each 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 Excel sheet where in column A I have each account number listed and in column B I have each part number listed (meaning that each account number will be listed 350 times as there will be one row for each part number 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 achieve this! I'd appreciate any help :) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining two separate lists and creating new values
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 : I'm sure there's a way to do this but just can't seem to get it. I have two separate lists in Excel. One is customer account numbers, of which there are 50, and one is part numbers, of which there are 350. Each 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 Excel sheet where in column A I have each account number listed and in column B I have each part number listed (meaning that each account number will be listed 350 times as there will be one row for each part number 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 achieve this! I'd appreciate any help :) |
#3
|
|||
|
|||
Quote:
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.
__________________
I hope it can help you. Best regards, Marcilio Lobão --------------------------- Belo Horizonte, Brazil |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining two separate lists and creating new values
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 : 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. |
#5
|
|||
|
|||
Dear Isabelle, Good Evening.
Thank you very much for taking the time and effort on this. Its very much appreciated. Have a nice day. Quote:
__________________
I hope it can help you. Best regards, Marcilio Lobão --------------------------- Belo Horizonte, Brazil |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining two separate lists and creating new values
my pleasure! have a nice day too
-- isabelle Le 2012-05-02 22:29, Mazzaropi a écrit : Dear *Isabelle*, Good Evening. Thank you very much for taking the time and effort on this. Its very much appreciated. Have a nice day. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching and combining information on two separate worksheets | Excel Worksheet Functions | |||
combining data from two separate sheets | New Users to Excel | |||
Values from 3 separate drop-down lists entered into single cell | Excel Worksheet Functions | |||
Combining 2 lists | New Users to Excel | |||
creating drop down lists where you can select multiple values | Excel Discussion (Misc queries) |