I am currently comparing a value in column A: "1234" with the value in column B "1234" if this matches then look at the value in column E "01/01/2000" and compare this with the value in the below row in column E "01/01/2000" if this all matches then move to the next row and compare the same sets of data. If they match then repeat the process, if the next line does not match then take the values in column C and sum them together and place the sum of all the rows which match in the first number set which matched in column D. I only want to look at the first 4 characters from rows A and B any numbers which come after this I do not want included for example :12345 would only take 1234.
Table:
A B Money Column1 Closing
1234 1234 200.00 500.00 01/01/2000
1234 1234 300.00 01/01/2000
1256 1543 400.00 400.00 01/01/2001
1256 1585 500.00 500.00 01/01/2000
Here is what I have tried to far:
Sub DemoNew()
Dim dict1 As Object
Dim c1 As Variant, k As Variant
Dim currWS As Worksheet
Dim i As Double, lastRow As Double, tot As Double
Dim number1 As Double, number2 As Double, firstRow As Double
Set dict1 = CreateObject("Scripting.Dictionary")
Set currWS = ThisWorkbook.Sheets("Sheet1")
'get last row withh data in Column A
lastRow = currWS.Cells(Rows.Count, "A").End(xlUp).Row
'put unique numbers in Column A in dict1
c1 = Range("A2:B" & lastRow)
For i = 1 To UBound(c1, 1)
If c1(i, 1) <> "" Then
'make combination with first 4 characters
dict1(Left(c1(i, 1), 4) & "," & Left(c1(i, 2), 4)) = 1
End If
Next i
'loop through all the numbers in column A
For Each k In dict1.keys
number1 = Split(k, ",")(0)
number2 = Split(k, ",")(1)
tot = 0
firstRow = 0
For i = 2 To lastRow
If k = Left(currWS.Range("A" & i).Value, 4) & "," & Left(currWS.Range("B" & i).Value, 4) Then
If firstRow = 0 Then
firstRow = i
End If
tot = tot + currWS.Range("C" & i).Value
End If
Next i
currWS.Range("D" & firstRow) = tot
Next k
End Sub
Please see attachment below
Example6.PNG
Bookmarks