multi dimensional pivot table

Question

I am struggling with a problem.

Delivery	1 Pallet	2 Pallets	3 Pallet	4 Pallet	5 pallet	6 Pallet	7 Pallet	8 Pallet	9 Pallet	10 Pallets
A1	€ 198	€ 269	€ 382	€ 476	€ 589	€ 663	€ 738	€ 830	€ 913	€ 1,006
B1	€ 208	€ 279	€ 409	€ 531	€ 629	€ 716	€ 799	€ 906	€ 1,008	€ 1,109
C1	€ 214	€ 284	€ 413	€ 536	€ 636	€ 726	€ 812	€ 913	€ 1,015	€ 1,117
D1	€ 347	€ 503	€ 721	€ 900	€ 1,082	€ 1,196	€ 1,310	€ 1,464	€ 1,616	€ 1,771

I have 4x10 table of values as above, and need to find combination of the sum of values to 10 Pallets indicated by the column heading. This hard to explain but I need a table showing all the different combinations of value sums that equal to 10

Therefore I need a table where for example (A1=10 B1=0 C1=0 D1=0) = 1,006 and (A1=3 B1=2 C1=2 D1=3) = 1,666.

Can anyone help me please

Answer 1

To make it more flexible I added a variable that you can change, though I hope your table only goes through Y5, which is 24 columns of pallet prices.

Sub TestMacro24()

Dim i As Integer

Dim j As Integer

Dim k As Integer

Dim l As Integer

Dim lRow As Long

Dim iTotal As Integer

Dim iSize As Integer

iSize = 24  'Set Size Here

lRow = 8

Cells(lRow, 1).Value = "A1"

Cells(lRow, 2).Value = "B1"

Cells(lRow, 3).Value = "C1"

Cells(lRow, 4).Value = "D1"

Cells(lRow, 5).Value = "Total"

For i = 0 To iSize

For j = 0 To iSize - i

For k = 0 To iSize - i - j

l = iSize - i - j - k

lRow = lRow + 1

iTotal = IIf(l <> 0, Cells(2, l + 1).Value, 0) + _

    IIf(k <> 0, Cells(3, k + 1).Value, 0) + _

    IIf(j <> 0, Cells(4, j + 1).Value, 0) + _

    IIf(i <> 0, Cells(5, i + 1).Value, 0)

Cells(lRow, 1).Value = l

Cells(lRow, 2).Value = k

Cells(lRow, 3).Value = j

Cells(lRow, 4).Value = i

Cells(lRow, 5).Value = iTotal

Next k

Next j

Next i

End Sub

Answer 2

Hello

Thank you very much

I have another one now that has 24 Pallets, therefore size is A1:Z5

How would I change this macro to enable this to work?

Regards

Matthew

Answer 3

With your table as given in cells A1:K5, and nothing else on the sheet - run this macro.

Sub TestMacro2()

Dim i As Integer

Dim j As Integer

Dim k As Integer

Dim l As Integer

Dim lRow As Long

Dim iTotal As Integer

lRow = 8

Cells(lRow, 1).Value = "A1"

Cells(lRow, 2).Value = "B1"

Cells(lRow, 3).Value = "C1"

Cells(lRow, 4).Value = "D1"

Cells(lRow, 5).Value = "Total"

For i = 0 To 10

For j = 0 To 10 - i

For k = 0 To 10 - i - j

l = 10 - i - j - k

lRow = lRow + 1

iTotal = IIf(l <> 0, Cells(2, l + 1).Value, 0) + _

    IIf(k <> 0, Cells(3, k + 1).Value, 0) + _

    IIf(j <> 0, Cells(4, j + 1).Value, 0) + _

    IIf(i <> 0, Cells(5, i + 1).Value, 0)

Cells(lRow, 1).Value = l

Cells(lRow, 2).Value = k

Cells(lRow, 3).Value = j

Cells(lRow, 4).Value = i

Cells(lRow, 5).Value = iTotal

Next k

Next j

Next i

End Sub