Loop Matching Arrays (sum values) - VBA Please help

[nubranger]

I have to match both columns below. To get a match you need to sum/combine some values in TC column and combine some values in AE column. (e.g-1 sum of Items 1 & 2 of TC Column (20,378) will match to the sum of items 2 & 3 of AE Column (20,378) e.-2 sum of items 15,18,19 of TC Column will match to the sum of items 12, 14, 15, 16, 20 of AE column). No single amount to sum values matching. To get a match you always need to sum values in both columns.

In my original code, these columns are stored in two different arrays. So I have to perform the loop matching inside these arrays. Hence, the reason why my code begins with storing the values in two arrays below.

This is what I want to achieve in my loop:
IF TCval 1 + TCval 3 = AEval1 + AEval2 + AEvall3 etc.. Then
Color the matched cells in yellow and remove the matched values in both arrays then re-perform the loop again.

Thanks for the help.

ITEM	TC		AE
TOTAL	434,507.00		434,507.00
1​	10,123.00		17,205.00
2​	10,255.00		5,223.00
3​	10,344.00		15,155.00
4​	10,488.00		12,234.00
5​	10,564.00		2,234.00
6​	10,621.00		3,467.00
7​	10,732.00		34,567.00
8​	10,888.00		22,354.00
9​	10,999.00		20,609.00
10​	11,021.00		1,245.00
11​	11,109.00		8,893.00
12​	11,200.00		23,672.00
13​	11,311.00		21,087.00
14​	8,829.00		12,958.00
15​	22,839.00		23,049.00
16​	1,123.00		54,432.00
17​	29,205.00		7,890.00
18​	154,202.00		14,654.00
19​	33,743.00		23,490.00
20​	44,911.00		96,673.00
21​			13,416.00

Sub test1()

    Dim tcArrayV() As Variant
    Dim aeArrayV() As Variant
    Dim GetTcIndex As Long, a As Integer, b As Integer, h As Integer, i As Integer
    Dim tcSize As Long, tcIndex As Long
    Dim aeSize As Long, aeIndex As Long
    ReDim tcArrayV(tcSize)
    ReDim aeArrayV(aeSize)

    Dim aeRow As Long
    Dim tcRow As Long
    
    tcSize = 1: tcIndex = 0: aeSize = 1: aeIndex = 0
    aeRow = Worksheets("Sheet1").Cells(Rows.Count(), 4).End(xlUp).Row
    tcRow = Worksheets("Sheet1").Cells(Rows.Count(), 2).End(xlUp).Row

    'Store all in array
    For a = 3 To aeRow
        aeArrayV(aeIndex) = CDec(Worksheets("Sheet1").Cells(a, 4).Value)
        aeSize = aeSize + 1
        ReDim Preserve aeArrayV(aeSize)
        aeIndex = aeIndex + 1
    Next a
    
    For b = 3 To tcRow
        tcArrayV(tcIndex) = CDec(Worksheets("Sheet1").Cells(b, 2).Value)
        tcSize = tcSize + 1
        ReDim Preserve tcArrayV(tcSize)
        tcIndex = tcIndex + 1
    Next b

    For h = 0 To UBound(tcArrayV) - 2
                                
        GetTcIndex = (UBound(tcArrayV) - LBound(tcArrayV) + 1) - 2 ' 'Get total number of entries
        
        For i = 0 To UBound(aeArrayV) - 2
        
            'what to do here?
    
    Next h
End Sub

 

[nubranger]

Not sure if it is possible to remove the matched values in the array. Maybe move to last index then Redim? or is there a better way to do the matching?

 

[nubranger]

Also. Max number of values to sum is 5. I dont expect to sum more than 5 values/cells.