Macro Help - combining two or more lists of data in a loop

[jlhop66]

I am new to Macros, and I am learning new was to make repetitive tasks easier.


Here is what I need help with. I have 3 list of data and I need to combine them.

1. Item list (5 to 100 items in a list)
2. Quantity list
3. Store list (2 to 10 stores)

For every item in my list, I need to add the list of stores to distribute to, and the quantity needed.

Example of order sheet

	Stores
Items	74	22	70	71	59
1001	2	2	2	2	2
2002	1	1	1	1	1
3003	4	4	4	4	4
4004	6	6	6	6	6
5005	3	3	3	3	3
6006	8	8	8	8	8

I need a macro to convert the order array list into column form such as Items “A”, Store “B” and Qty “C”

Item	QTY	Store
1001	2	74
1001	2	22
1001	2	70
1001	2	71
1001	2	59
2002	1	74
2002	1	22
2002	1	70
2002	1	71
2002	1	59
3003	4	74
3003	4	22
3003	4	70
3003	4	71
3003	4	59

I have been trying for 3 weeks diffrent loop combinations, and I just cannot figure this out.

Please Help

 

[VBA Geek]

Returned in Columns I:K as per below. Assumed your raw data is in A:F as per below

Sub Transpe()
Dim iData As Variant
Dim iCoun As Integer
Dim iRow  As Integer
Dim fData As Variant
iRow = Range("A" & Rows.Count).End(xlUp).Row
iData = Range("A2:F" & iRow).Value
ReDim fData(1 To (UBound(iData) - 1) * 5, 1 To 3)
For iCoun = LBound(fData, 1) To UBound(fData, 1)
    fData(iCoun, 1) = iData(Int((iCoun - 1) / 5) + 2, 1)
    fData(iCoun, 2) = iData(Int((iCoun - 1) / 5) + 2, 2 + ((iCoun - 1) Mod 5))
    fData(iCoun, 3) = iData(1, 2 + ((iCoun - 1) Mod 5))
Next iCoun
Range("I1").Resize(, 3).Value = Array("Item", "QTY", "Store")
Range("I2").Resize(UBound(fData, 1), 3).Value = fData
End Sub

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K
1		Stores							Item	QTY	Store
2	Items	74	22	70	71	59			1001	2	74
3	1001	2	2	2	2	2			1001	2	22
4	2002	1	1	1	1	1			1001	2	70
5	3003	4	4	4	4	4			1001	2	71
6	4004	6	6	6	6	6			1001	2	59
7	5005	3	3	3	3	3			2002	1	74
8	6006	8	8	8	8	8			2002	1	22
9									2002	1	70
10									2002	1	71
11									2002	1	59
12									3003	4	74
13									3003	4	22
14									3003	4	70
15									3003	4	71
16									3003	4	59
17									4004	6	74
18									4004	6	22
19									4004	6	70
20									4004	6	71
21									4004	6	59
22									5005	3	74
23									5005	3	22
24									5005	3	70
25									5005	3	71
26									5005	3	59
27									6006	8	74
28									6006	8	22
29									6006	8	70
30									6006	8	71
31									6006	8	59

<tbody>
</tbody>

Sheet2​

 

[jlhop66]

Thank you very much. after I walked through the steps a few times, it made sence what was happening. I appriciate the help, the tool works great.