Loop Matching Arrays (sum values) - VBA Please help

nubranger

Board Regular
Joined
Dec 23, 2019
Messages
53
Office Version
  1. 365
Platform
  1. Windows
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





VBA Code:
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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

nubranger

Board Regular
Joined
Dec 23, 2019
Messages
53
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Dec 23, 2019
Messages
53
Office Version
  1. 365
Platform
  1. Windows
Also. Max number of values to sum is 5. I dont expect to sum more than 5 values/cells.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,447
Messages
5,547,985
Members
410,820
Latest member
Prepost
Top