combining two lists

karmaisgreat · 04-13-2006, 03:45 PM

I hope you can help it will save a week of copy and paste. In column A I
have a list of account names. Lets say ABCDEFG, but there are 1000 names.
In column B I have a list of products, lets call them 1-2-3-4-5-6. I need to
create a two column list that combines the data so the account name repeats
for each product:

A 1
A 2
A 3
B 1
B 2
B 3
C 1

And so on..... Any help please!

**mrice** · 04-13-2006, 04:11 PM

I would suggest the following (should take a few minutes - find something more enjoyable for the rest of the week).

Use the following macro.

Sub CopyStuff()
Set AccountNameRange = Application.InputBox("Select account name range", , , , , , , 8)
Set ProductRange = Application.InputBox("Select product name range", , , , , , , 8)
Set StartCell = Application.InputBox("Select cell in answer column", , , , , , , 8)
TargetColumn = StartCell.Column
For Each Account In AccountNameRange
For Each Product In ProductRange
Cells(65536, TargetColumn).End(xlUp).Offset(1, 0) = Account
Cells(65536, TargetColumn).End(xlUp).Offset(0, 1) = Product
Next Product
Next Account

End Sub

bpeltzer · 04-13-2006, 04:20 PM

My inelegant approach:
1) clear column B
2) in the first row of your table, in column B, enter product 1.
3) place the cursor on the fill-handle of that cell (bottom right; the
cursor will change to a smallish square) and double-click to fill that
product for each customer
4) go to the first row of data and click in column A of that row
5) select your entire data set from column A only (ctrl+shift+down arrow to
select all the data) and copy (ctrl+c)
6) advance to the first blank cell in column A (ctrl+down arrow, then down
arrow)
7) paste (ctrl+v)
8) right-arrow to get to the first blank cell in column B, and enter the
next product
9) use the fill handle to fill in the product for each customer, as in step 3
Go back to the first row that has the latest product in it and click in
column A, then repeat steps 5-9 for the next product. Repeat for products 4,
5 and 6.
Finally, select columns A and B and use Data > Sort. In the sort dialog,
choose to sort by Column A then by Column B.

"karmaisgreat" wrote:

> I hope you can help it will save a week of copy and paste. In column A I
> have a list of account names. Lets say ABCDEFG, but there are 1000 names.
> In column B I have a list of products, lets call them 1-2-3-4-5-6. I need to
> create a two column list that combines the data so the account name repeats
> for each product:
>
> A 1
> A 2
> A 3
> B 1
> B 2
> B 3
> C 1
>
> And so on..... Any help please!

QC Coug · 04-13-2006, 04:20 PM

Set it up using formulas with if statements. Is there a way to attach an
example so you can see it?

QC Coug

"karmaisgreat" wrote:

> I hope you can help it will save a week of copy and paste. In column A I
> have a list of account names. Lets say ABCDEFG, but there are 1000 names.
> In column B I have a list of products, lets call them 1-2-3-4-5-6. I need to
> create a two column list that combines the data so the account name repeats
> for each product:
>
> A 1
> A 2
> A 3
> B 1
> B 2
> B 3
> C 1
>
> And so on..... Any help please!