Multiple Criteria Match

Question

Excel File

Missing In above O3=2

Hi,What will be the vba code for generation of data from I5:S17 which I have highlighted with yellow.

I am trying to get data by matching vertical column A & Column B (Criteria1 & 2 ) with horizontal row 2 & 3 (Criteria 1 & 2) with breakdown of Criteria 3 i.e Column D in the way presented in column I,K,M,O,Q,S and so on.

Answer 1

The test sub below assumed that the things I asked in my comment will never happen. Also it assumed that column A and column C row value is sorted.

Sub test4()
Dim sh As Worksheet, rg As Range, cell As Range, qty As Range
Dim rgP As Range, rgQ As Range
Dim qty1 As Long, qty2 As Long
Application.ScreenUpdating = False

Set sh = Sheets("Sheet1")

With sh
Set rg = .Range("i2:" & Cells(2, Columns.Count).End(xlToLeft).Address)
End With

For Each cell In rg.SpecialCells(xlConstants)

    With sh.Columns(1)
        .Replace cell.Value, True, xlWhole, , False, , False, False
        Set rgP = .SpecialCells(xlConstants, xlLogical) 'product range column A
        .Replace True, cell.Value, xlWhole, , False, , False, False
    End With
    
    With rgP.Offset(0, 1) 'month range column B
        If cell.Offset(1, 0).Value <> "" Then
        .Replace cell.Offset(1, 0).Value, True, xlWhole, , False, , False, False
        Set rgQ = .SpecialCells(xlConstants, xlLogical).Offset(0, 2) 'QTY range column D
        .Replace True, cell.Offset(1, 0).Value, xlWhole, , False, , False, False
        Else
        Set rgQ = rgP.Offset(0, 3)
        End If
    End With

    qty1 = cell.Offset(2, 0).Value
    qty2 = 0
    Set qty = rgQ(rgQ.Rows.Count, 1)
        Do
        If qty.Row <> rgP(1, 1).Row Then
            If qty2 <= qty1 And qty1 > qty2 + qty.Value Then
                qty2 = qty2 + qty.Value
                sh.Cells(qty.Row, cell.Column).Value = qty.Value
            Else
                sh.Cells(qty.Row, cell.Column).Value = qty1 - qty2
                Exit Do
            End If
        Else
            If qty.Value - (qty1 - qty2) < 0 Then
                sh.Cells(qty.Row, cell.Column).Value = qty.Value - (qty1 - qty2)
            Else
                sh.Cells(qty.Row, cell.Column).Value = qty1 - qty2
            End If
            Exit Do
        End If
        Set qty = qty.Offset(-1, 0)
        Loop
        
Next

Application.ScreenUpdating = True
End Sub

It create a range variable from i2 to whatever last column row 2 with value as rg, then it loop to each cell which has value in rg.

Then it create rgP variable as the range in column A which has value of the looped cell value. Then it check if the looped cell offset 1,0 value is blank then create rgQ range from rgP offset 0,1 (column B) which value is the looped cell offset 1,0, else it set rgQ from rgP offset 0,3.

Now we have a range of qty as rgQ which coming from the looped cell.value and its offset(1,0) value as the vertical criteria which compared with the horizonal criteria (the rows) of column A and column B.

Then it take the value of the looped cell offset 2,0 as qty1
create a zero value as qty2 for a sum comparison
and set a range as qty from the last cell of rgQ.
then it do a sum checking by looping.

In the Do-Loop:
A. as long as the qty cell is not the first cell of rgP:
then it do the calculation as seen in the code.
B. soon the qty cell is the the first cell of rgP, then it's the last thing to do a calculation to compare, which either the result is negative or positive.
(I'm sorry I can't describe it well as it's too difficult for me to explain in English).

Please note, the sub doesn't check whether the looped cell exist in column A or not, and also it doesn't check whether the looped cell.offset(1,0) value exist in rgP.Offset(0, 1) (column B).

Answer 2

All of the information needed to calculate the values was missing from your original question. Based on your follow up comments it seems like you want to do the following:

The sum of each column from row 5 to 17 should equal the value in row 4. Only use values from column D. Start with the last row where the product and month in column A & B match the product and month in row 2 & 3. Then continue using the values from previous rows until the total would exceed row 4.

If the next value in column D would cause the total to exceed row 4, just enter the value needed to equal row 4.

Example Column I:

Target value is 50

Last matching row is row 8.

Row 8 value is 17, total is 17, enter 17 in I8

Row 7 value is 15, total is (17 + 15 = 32), enter 15 in I7

Row 6 value is 10, total is (17 + 15 + 10 = 42), enter 10 in I6

Row 5 value is 10, total would be (17 + 15 + 10 + 10 = 52) which exceeds the target. The value needed to meet the target is (50 - 42 = 8). Enter 8 in I5.

The VBA from another user seems to work, here is a formula to achieve the same.

=IFS(
OR($A5<>G$2,$B5>G$3),"",
AND($A5=G$2,$B5=G$3),MIN($D5,G$4),
G$4-SUM(G6:G$17)=0,"",
TRUE,MIN($D5,G$4-SUM(G6:G$17)))