Combination Matching - Sum up cells to match with single value

[nubranger]

I was able to properly match the ones highlighted in yellow in VBA which are 1:1 match (w/ within 3 days limit matching). The macro automatically highlights the cells in yellow and a match is made. Now, how do I make a macro to do combination matching. For example, in Column AE, the cells highlighted in green were manually summed up to match with the green cell in "Total charges" column which is 18,480 (I will also apply the rule to match within 3 days).

Thanks.

 

[jasonb75]

Without seeing your existing code, there is no way of knowing what will work with it.

You could try storing the AE amounts in an array as you check the 1:1 matches, substituting any 1:1 match for zero in the array, then loop through and sum non zero elements until you find a match or exhaust combinations in the 3 day range.

 

[nubranger]

Without seeing your existing code, there is no way of knowing what will work with it.

You could try storing the AE amounts in an array as you check the 1:1 matches, substituting any 1:1 match for zero in the array, then loop through and sum non zero elements until you find a match or exhaust combinations in the 3 day range.

Thanks for the reply Jason. I'm not yet that familiar with arrays.. still learning.

This is what I have now.

the AE and Total charges are saved in different sheets.

Option Explicit

'Declare
Dim wb As Workbook
Dim aeRprt As Worksheet
Dim tcRprt As Worksheet
Dim aeRow As Long
Dim tcRow As Long
Dim Search1 As Variant
Dim Search2 As Variant

Dim red As Long
Dim a As Long
Dim b As Long
Dim c As Long
Dim d As Long
Dim e As Long
Dim f As Long


Private Sub match1()

'Set Sheets
Set aeRprt = Sheets("AE")
Set tcRprt = Sheets("TC")
Set wb = ThisWorkbook


'Define last row count
aeRow = aeRprt.Cells(Rows.Count(), 1).End(xlUp).Row
tcRow = tcRprt.Cells(Rows.Count(), 1).End(xlUp).Row

'Get # of days from the date
For a = 2 To tcRow
   tcRprt.Cells(a, "C") = Split(tcRprt.Cells(a, "A"), "/")(0)
Next a

'1:1 Matching
For b = 2 To tcRow
    Set Search1 = tcRprt.Cells(b, 2)
    Set Search2 = tcRprt.Cells(b, 3)
    For c = 2 To aeRow
        If aeRprt.Cells(c, 2) = Search1 Then
        If Search2 - aeRprt.Cells(c, 1) >= 0 And Search2 - aeRprt.Cells(c, 1) <= 3 Then
        If aeRprt.Cells(c, 2).Interior.Color = 16777215 And Search1.Interior.Color = 16777215 Then
        aeRprt.Cells(c, 2).Interior.Color = 65535
        Search1.Interior.Color = 65535
        Exit For
        End If
        End If
        End If
        
Next c
Next b

End Sub

 

[nubranger]

I think i can also do a static loop to sum values then compare. But not sure how.

 

[jasonb75]

I'm not yet that familiar with arrays.. still learning.

Me too

Thinking ahead of possible scenarios that could break things. Is it possible that 2 summed sections could overlap?

For example, allocating AE Days 14 and 16 to 1 entry in the second table, with 15 and 17 (or even just 15) allocated to another?

 

[nubranger]

Me too

Thinking ahead of possible scenarios that could break things. Is it possible that 2 summed sections could overlap?

For example, allocating AE Days 14 and 16 to 1 entry in the second table, with 15 and 17 (or even just 15) allocated to another?

Yep. That is possible. though it is rare.

I was trying to do static loop adding noncolored cells. but my code is not working:

Private Sub combination()

'Set Sheets
Set aeRprt = Sheets("AE")
Set tcRprt = Sheets("TC")
Set wb = ThisWorkbook


'Define last row count
aeRow = aeRprt.Cells(Rows.Count(), 1).End(xlUp).Row
tcRow = tcRprt.Cells(Rows.Count(), 1).End(xlUp).Row

Dim currSum As Long

For d = 2 To tcRow

    Set Search3 = tcRprt.Cells(d, 2)
    Set Search4 = tcRprt.Cells(d, 3)
    
    If Search3.Interior.Color = 16777215 Then
    
    Search3.Select ' just checkin
    
        For e = 2 To aeRow

            If aeRprt.Cells(e, 2).Interior.Color = 16777215 Then
            If Search3 - aeRprt.Cells(e, 1) >= 0 And Search4 - aeRprt.Cells(e, 1) <= 3 Then
            currSum = currSum + aeRprt.Range(e, 2).Value
            If currSum = Search3 Then
            MsgBox "Found Match"
            Exit For
            End If
            End If
            End If
            
        Next e
    End If
Next d
        
End Sub

 

[jasonb75]

Yep. That is possible. though it is rare.

I was really hoping you wouldn't say that.
I had something in mind that I think I could get working with no overlap, possibly even with a 1:1 match in the middle of a summed section, but 2 summed sections overlapping might be a challenge.

I'll give it some thought through the day and try a few things out when I get home later.

 

[nubranger]

Ok I finally managed to make a working code. This will basically highlight the Total charges matched with values summed up in AE. My only issue now is how do I highlight those values combined in AE when matched?

Private Sub combination()

'Set Sheets
Set aeRprt = Sheets("AE")
Set tcRprt = Sheets("TC")
Set wb = ThisWorkbook


'Define last row count
aeRow = aeRprt.Cells(Rows.Count(), 1).End(xlUp).Row
tcRow = tcRprt.Cells(Rows.Count(), 1).End(xlUp).Row

Dim currSum As Long

For d = 2 To tcRow

    Set Search3 = tcRprt.Cells(d, 2)
    Set Search4 = tcRprt.Cells(d, 3)
    
    If Search3.Interior.Color = 16777215 Then
    
    Search3.Select
    
        For e = 2 To aeRow

            If aeRprt.Cells(e, 2).Interior.Color = 16777215 Then
            If Search3 - aeRprt.Cells(e, 1) >= 0 And Search4 - aeRprt.Cells(e, 1) <= 3 Then
            currSum = currSum + aeRprt.Cells(e, 2).Value
            If currSum = Search3 Then
            Search3.Interior.Color = 5296274
            Exit For
            End If
            End If
            End If
            
        Next e
    currSum = 0
    End If
Next d
        
End Sub

 

[jasonb75]

I can't look at it in detail at the moment, but a quick thought on your post above.

            If Search3 - aeRprt.Cells(e, 1) >= 0 And Search4 - aeRprt.Cells(e, 1) <= 3 Then
            If n = 0  Then n = e
            currSum = currSum + aeRprt.Cells(e, 2).Value

        Next e
    currSum = 0
    aeRprt.Range("B" & n & ":B" & e).Interior.Color = 5296274
    End If

 

[nubranger]

I can't look at it in detail at the moment, but a quick thought on your post above.

            If Search3 - aeRprt.Cells(e, 1) >= 0 And Search4 - aeRprt.Cells(e, 1) <= 3 Then
            If n = 0  Then n = e
            currSum = currSum + aeRprt.Cells(e, 2).Value

        Next e
    currSum = 0
    aeRprt.Range("B" & n & ":B" & e).Interior.Color = 5296274
    End If

It highlight all the values in AE sheet.