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

 

[Cubist]

Which version of Excel are you using?

 

[jay_hl]

Which version of Excel are you using?

Thanks for the reply. The latest M365 version.

 

[Cubist]

Thanks for the reply. The latest M365 version.

Would you be interested in a formula solution? However, there's a character limit so I'm not sure it'll work on the size of your data. If it doesn't work we can proceed to VBA.

 

[jay_hl]

Would you be interested in a formula solution? However, there's a character limit so I'm not sure it'll work on the size of your data. If it doesn't work we can proceed to VBA.

Yes open for all ideas.

 

[Cubist]

Try:

Book1
	C	D	E	F	G	H	I	J	K	L
3				Store 1	Store 2
4	Item 1			1				Store 1	Item 1	1
5	Item 2			2	4			Store 1	Item 2	2
6	Item 3							Store 1	Item 4	2
7	Item 4			2	5			Store 1	Item 5	1
8	Item 5			1	3			Store 1	Item 7	3
9	Item 6				2			Store 2	Item 2	4
10	Item 7			3				Store 2	Item 4	5
11	Item 8				5			Store 2	Item 5	3
12								Store 2	Item 6	2
13								Store 2	Item 8	5
Sheet1

Cell Formulas
Range	Formula
J4:L13	J4	=LET(t,SORT(TEXTSPLIT(TEXTJOIN(",",,TOCOL(F3:G3 & "|" & C4:C11 & "|" & F4:G11)),"|",",")),FILTER(t,CHOOSECOLS(t,3)<>""))
Dynamic array formulas.

 

[jay_hl]

Thanks

Try:

Book1
	C	D	E	F	G	H	I	J	K	L
3				Store 1	Store 2
4	Item 1			1				Store 1	Item 1	1
5	Item 2			2	4			Store 1	Item 2	2
6	Item 3							Store 1	Item 4	2
7	Item 4			2	5			Store 1	Item 5	1
8	Item 5			1	3			Store 1	Item 7	3
9	Item 6				2			Store 2	Item 2	4
10	Item 7			3				Store 2	Item 4	5
11	Item 8				5			Store 2	Item 5	3
12								Store 2	Item 6	2
13								Store 2	Item 8	5
Sheet1

Cell Formulas
Range	Formula
J4:L13	J4	=LET(t,SORT(TEXTSPLIT(TEXTJOIN(",",,TOCOL(F3:G3 & "|" & C4:C11 & "|" & F4:G11)),"|",",")),FILTER(t,CHOOSECOLS(t,3)<>""))
Dynamic array formulas.

for your efforts Cubist. As suspected its giving a #Calc error when expanded to the full range, so does seems to be hitting the character limit you suggested.

Secondly I also realise I might have over simplified my original ask. Many of the cells in the input matrix actually have 0 in the quantities, which need to be ignored in the output. I had missed this as the format of the cells doesnt show zeros, however upon checking, technically the cell does contain 0. The output for upload to the ordering system only needs to contain > 0 quantities.

Appreciate your efforts on this.

 

[Cubist]

If so, can you provide a more representative sample of your data? How is it actually lay out on the sheet? Where do you want the output to go (Note the output is 5,000 x 100 = 500K row)?

 

[jay_hl]

If so, can you provide a more representative sample of your data? How is it actually lay out on the sheet? Where do you want the output to go (Note the output is 5,000 x 100 = 500K row)?

Thanks for your help here Cubist. Tried to explain in the below a bit more.

I checked again and the Item range are approx 2500 (with some blanks to add new future products to), and the same with the stores which is 35 columns including blanks for new future entires. There are a lot of other data between B and BE, hence the stores ordering start in Column BF, with Store titles in row 5.

The Order Output (which should be copied values into a new tab) is only a list where quantities to order in the matrix is > 0. So it wouldnt be a full 2500 x 35 list with every combination.

It also only needs to copy data where the Item and Store name cell isnt blank. Usually there are about 50 items ordered per store, as the full 2500 item catalogue has a lot of legacy products. So the Order list would be more like 50 products and currently 20 active stores = 1000 entries.

Hope that helps, and thanks again.

 

[Akuini]

@jay_hl, try this:
I put the result in sheet2.

Sub jay_hl_1()
Dim i As Long, j As Long, n As Long, k As Long, p As Long
Dim va, vb, vc

n = Range("A" & Rows.Count).End(xlUp).Row
va = Range("A5:A" & n)
p = Cells(5, "CO").End(xlToLeft).Column
vb = Range(Cells(5, "BF"), Cells(n, p))
ReDim vc(1 To 500000, 1 To 4)
For j = 1 To UBound(vb, 2)
    For i = 2 To UBound(vb, 1)
        If vb(i, j) > 0 Then
            k = k + 1
            vc(k, 1) = vb(1, j)
            vc(k, 2) = va(i, 1)
            vc(k, 3) = "PCS"
            vc(k, 4) = vb(i, j)
        End If
    Next
Next

'put the result in sheet2
Sheets("Sheet2").Activate
Range("A:D").ClearContents
Range("A3").Resize(k, 4) = vc

End Sub

In your example in post #9, Range H5:K5 is really blank, right? there is no word "(blank)" in it?