VBA for Ordering List

[jay_hl]

Hello

I have a large table containing 5000 products down the left side, and 100 stores across the top. The number in the table, is the quality of products to order for that particular store.

To upload the order to the system I need a flat list of only those items which need ordering. I have given simplified structures of both the input matrix, and output needed. (PCS is always the same and standard).

Hope someone can help produce something which creates the example in columns J to M

Thanks in advance

Jay

 

[jay_hl]

The array in vk at the moment is one continuous list, with no second header row, or blank rows. The ask is to help tweak the VBA to include the blank lines when Column 2 changes, and then the header row when the value in Column A changes.

Thanks again

 

[Akuini]

Can describe which part is the data & which part is the result?

 

[Akuini]

deleted: wrong post

 

[jay_hl]

The data is from another tab, and similar to the original ask where you kindle provided the original code back in April, builds an array. This is now written into vk as a continuous list and seems to work fine across the 6 columns

I need to start to include blank lines into the array where the second column value changes, and then also include 2 blank lines and the header row (row 1) where the first column changes. This is where I hope to have the output in the table I just posted.

I have tried to adapt the code but unfortunatly have failed, and hence reaching out again

 

[Akuini]

The data is from another tab, and similar to the original ask where you kindle provided the original code back in April, builds an array.

Can post the data that produce the result in post #20 via xl2bb?

 

[jay_hl]

Sure. I have had to clense the data, so the mapping between SKU's/Barcodes are random numbers, but the quantities and stores should match

																																								Output	Output	Output	Output	Output	Output	Output	Output	Output	Output	Output	Output
																																								32	12	171	12	12	131	0	24	85	0	0	12
																																								Store 1	Store 1	Store 1	Store 2	Store 2	Store 2	Store 3	Store 3	Store 3	Store 4	Store 4	Store 4
SKU	Barcode	Description																																						Store 2	Store 3	Store 4	Store 1	Store 3	Store 4	Store 1	Store 2	Store 4	Store 1	Store 2	Store 3
1	3006659376829	Desc 1																																											12			7
2	9794474246254	Desc 2																																						12		6					7	6
3	4578862098120	Desc 3																																								15			15
4	6093075590909	Desc 4																																								8			7
5	5850270193908	Desc 5																																														6
6	7445040026902	Desc 6																																								8			8			10
7	2266579890238	Desc 7																																														14
8	7376956442152	Desc 8																																													7
9	5999485927439	Desc 9																																							6			6							6
10	8944458302678	Desc 10																																								8
11	2367021730597	Desc 11																																											6
12	8689534241867	Desc 12																																							6			6							6
13	4991814575809	Desc 13																																						10		14					10	13
14	6397169214139	Desc 14																																									12		28
15	1734449358502	Desc 15																																								7			6
16	2138913350369	Desc 16																																								17			17
17	5494634880055	Desc 17																																								10			7			8
18	7846224389136	Desc 18																																						10
19	8023509111679	Desc 19																																								22
20	2276231770354	Desc 20																																								7
21	4015975643396	Desc 21																																														8
22	9968914563439	Desc 22																																								7						7
23	6349435210786	Desc 23																																								29			19
24	3605446742584	Desc 24																																											6			6
25	2776773813583	Desc 25																																								13

 

[Akuini]

Ok, I'll try to amend the code tomorrow when I have time.

 

[jay_hl]

Thanks very much. I also realise I might have messed up the first number in the array, which doesnt seem to be coming across in the code that I have butchered together. "the first 12 doenst come through somehow".

 

[jay_hl]

Ok I think I just fixed the first number issue. So one down, but two more items still to go (blank lines and the header row).

At last I feel like im trying to crack this, although lots of trial and error!

Dim i As Long, j As Long, n As Long, k As Long, p As Long, q As Long
Dim va, vb, vc, vd, ve, vf, vg, vk
n = Range("A" & Rows.Count).End(xlUp).Row
va = Range("A5:A" & n)
vb = Range("B5:B" & n)
vc = Range("C5:C" & n)
p = Range("A1:BZ1").Find(What:="Output", LookIn:=xlValues, lookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column
q = Range("A1:BZ1").Find(What:="Output", LookIn:=xlValues, lookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False).Column

ve = Range(Cells(3, p), Cells(3, q))
vf = Range(Cells(4, p), Cells(4, q))
vg = Range(Cells(5, p), Cells(n, q))

ReDim vk(1 To 1000000, 1 To 6)
            vk(1, 1) = "Move " & Chr(10) & "From"
            vk(1, 2) = "Move " & Chr(10) & "To"
            vk(1, 3) = "SKU"
            vk(1, 4) = "Barcode"
            vk(1, 5) = "Description"
            vk(1, 6) = "Quantity to Transfer"
            k = 1
  For j = 1 To UBound(vg, 2)
    For i = 2 To UBound(vg, 1)
        If vg(i, j) <> "" Then
            k = k + 1
            vk(k, 1) = ve(1, j)
            vk(k, 2) = vf(1, j)
            vk(k, 3) = va(i, 1)
            vk(k, 4) = vb(i, 1)
            vk(k, 5) = vc(i, 1)
            vk(k, 6) = vg(i, j)
        End If
    Next
Next

 

[Akuini]

Try this:
Data in Sheet1 & result in Sheet2 (change to suit):

Sub jay_hl_4()
Dim i As Long, j As Long, n As Long, k As Long, p As Long, q As Long, m As Long
Dim va, vb, vc, vd, ve, vf, vg, vk
Sheets("Sheet1").Activate 'adjust sheet's name!
n = Range("A" & Rows.Count).End(xlUp).Row
va = Range("A5:A" & n)
vb = Range("B5:B" & n)
vc = Range("C5:C" & n)
p = Range("A1:BZ1").Find(What:="Output", LookIn:=xlValues, lookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column
q = Range("A1:BZ1").Find(What:="Output", LookIn:=xlValues, lookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False).Column

ve = Range(Cells(3, p), Cells(3, q)) 'first store
vf = Range(Cells(4, p), Cells(4, q)) 'second store
vg = Range(Cells(5, p), Cells(n, q))

ReDim vk(1 To 1000000, 1 To 6)
            vk(1, 1) = "Move " & Chr(10) & "From"
            vk(1, 2) = "Move " & Chr(10) & "To"
            vk(1, 3) = "SKU"
            vk(1, 4) = "Barcode"
            vk(1, 5) = "Description"
            vk(1, 6) = "Quantity to Transfer"
            k = 1
  For j = 1 To UBound(vg, 2)
    For i = 2 To UBound(vg, 1)
        If vg(i, j) <> "" Then
            If k > 1 Then
                If ve(1, j) = vk(k, 1) Then
                    If vf(1, j) <> vk(k, 2) Then k = k + 1 'insert blank row
                Else
                    k = k + 3 'insert blank row
                    For m = 1 To 6 ' insert header
                        vk(k, m) = vk(1, m)
                    Next
                End If
            End If
                
        k = k + 1
        vk(k, 1) = ve(1, j) 'first store
        vk(k, 2) = vf(1, j) 'second store
        vk(k, 3) = va(i, 1)
        vk(k, 4) = vb(i, 1)
        vk(k, 5) = vc(i, 1)
        vk(k, 6) = vg(i, j)

        End If
    Next
Next

Sheets("Sheet2").Activate 'adjust sheet's name!
Cells.ClearContents
Range("A1").Resize(k, 6) = vk

End Sub