Combine two lists to create separate records for each item

[Devoto18]

Hello all,

I did a search for this and didn't find an answer. Maybe I'm not using the correct phrasing for what I want to do.

I have to lists - a list of stores and a list of items. I want to combine them so that each store has every item.

Example:

Stores Items
123 ABC
456 DEF
789 GHI

I want the result to be:
Column A Column B
123 ABC
123 DEF
123 GHI
456 ABC
456 DEF
456 GHI
789 ABC
789 DEF
789 GHI

I have 250 stores and 100 items. Is there any easy way to do this? Thanks.

 

[stanleydgromjr]

Devoto18,

I have to lists - a list of stores and a list of items. I want to combine them so that each store has every item.

I am assuming that the list of Stores and the list of Items have no duplicates.


Before the macro:

Excel Workbook
	A	B	C	D	E
1	Stores	Items
2	123	ABC
3	456	DEF
4	789	GHI
5	1234
6	2345
7	3456
8
9
10
11
12
13
14
15
16
17
18
19
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E
1	Stores	Items		Stores	Items
2	123	ABC		123	ABC
3	456	DEF		123	DEF
4	789	GHI		123	GHI
5	1234			456	ABC
6	2345			456	DEF
7	3456			456	GHI
8				789	ABC
9				789	DEF
10				789	GHI
11				1234	ABC
12				1234	DEF
13				1234	GHI
14				2345	ABC
15				2345	DEF
16				2345	GHI
17				3456	ABC
18				3456	DEF
19				3456	GHI
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

On the 'Insert' menu, click 'Module'.

Copy the below code, and paste it into the Module (on the right pane).

Option Explicit
Sub CreateNewList()
Dim SLR As Long, ILR As Long, NLR As Long, i As Long
  Application.ScreenUpdating = False
  SLR = Cells(Rows.Count, 1).End(xlUp).Row
  ILR = Cells(Rows.Count, 2).End(xlUp).Row
  Range("A1:B1").Copy Range("D1")
  For i = 2 To SLR Step 1
    NLR = Cells(Rows.Count, 4).End(xlUp).Row + 1
    Cells(i, 1).Copy Range(Cells(NLR, 4), Cells(NLR + ILR - 2, 4))
    Range(Cells(2, 2), Cells(ILR, 2)).Copy Cells(NLR, 5)
  Next i
  Application.ScreenUpdating = True
End Sub

With your data on the active worksheet as displayed above, run the "CreateNewList" macro.


Have a great day,
Stan

 

[Devoto18]

Thanks a lot, stanleydgromjr! That worked perfectly.

I've been trying to work through Walkenbach's Excel 2003 Power Programming and I just can't get VBA to be anything other than greek to me. I don't know how you guys make it look so easy.

Thanks again for taking the time.

D

 

[stanleydgromjr]

Devoto18,

I've been trying to work through Walkenbach's Excel 2003 Power Programming and I just can't get VBA to be anything other than greek to me. I don't know how you guys make it look so easy.

We all started from scratch at some time. You just need something to create for yourself that will make your job easier and faster. You then become more valuable. But, because you can turn work around faster than everyone else, you get more work to do.


Try some of these:

Excel Tutorials and Tips - VBA - macros - training
http://www.mrexcel.com/articles.shtml

How to Learn to Write Macros
http://articles.excelyogi.com/playin...ba/2008/10/27/

Click here and scroll down to Getting Started with VBA.
http://www.datapigtechnologies.com/ExcelMain.htm

If you are serious about learning VBA try
http://www.add-ins.com/vbhelp.htm


And, when you begin to feel more comfortable writing code, try answering some of the posts here on MrExcel, and
http://www.excelforum.com/index.php
http://www.ozgrid.com/
http://www.vbaexpress.com/portal.php


Have a great day,
Stan