Excel VBA Total number of charges on a bank statement without counting blanks

wafflesncoke

New Member
Joined
Nov 15, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
So I get sent a large file that includes bank statement charges. The charges show as date/merchant/amount due to formatting of when they are copied from one system into excel prior to being sent there are "Blanks" listed under amount. The merchant name duplicates on the blank amount rows so a countif i cannot get to work because it counts the duplicated merchant names. I need to count the number of charges from a specific merchant for a certain dollar amount per day. The merchant name and amount and days all change from file to file. I would normally do this by setting filter manually and selecting to unshow blanks on amount then filter merchant then filter through the days the move on to next merchant etc etc this takes alot of time. Im sure there is a way to write some VBA to count this data for me

I cant post the statement but i can describe the best i can... In excel manually scrolling down without any filters i scroll to cell A312 it has the date is is merged and centered with A312-315 next is the merchant name in cells B312 but that entire row to either side is blank then in cell B313 is the merchant name again(this is the row i need to keep and count the cells mentioned above) in cell B314 is the state B315 is the country. I just need row 313 in this instance row 312 314 315 are essentially blank excluding the merge and center and B312 having a merchant name
 
So an example like the following is what you are looking for?

DuplicateRowsCounterAndRemover.xlsm
ABCDE
1Number of ChargesDateDollar AmountMerchant
229/2/202113.99Netflix
329/1/202115.99Netflix
419/2/20214.99Ebay
519/1/202154.96Walmart
619/1/202134.17Walmart
7
Sheet56
 
Upvote 0

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Try the following:

VBA Code:
Sub CondenseData_CountAndRemoveDuplicates()
'
    Dim DestinationRowCounter   As Long
    Dim MatchCount              As Long
    Dim SortedArraySlot         As Long
    Dim SourceLastRowB          As Long
    Dim SourceRow               As Long
    Dim wsDestination2LastRowA  As Long
    Dim wsDestination3LastRowA  As Long
    Dim MatchListArray          As Object
    Dim RowsToDelete            As String
    Dim SortedArray             As Variant, SortedNoDupesArray  As Variant, UnsortedArray   As Variant
    Dim wsDestination2          As Worksheet, wsDestination3    As Worksheet, wsSource      As Worksheet
'
    Set wsSource = Worksheets("Sheet1")                                                         ' <--- Set this to the source sheet name
    SourceLastRowB = wsSource.Range("B" & Rows.Count).End(xlUp).Row - 3                         ' Get last used row -3 of Source sheet column B
'
    Sheets.Add After:=Worksheets(Sheets.Count)                                                  ' Add a new sheet to end of this workbook
    Set wsDestination2 = Worksheets(Sheets.Count)                                               ' Set wsDestination2 to last sheet in workbook
'
    wsDestination2.Range("A1:D1").Value = Array("Date", "Merchant", "Transaction", "Amount")    ' Insert the Header for 2nd Destination Sheet
    wsDestination2.Columns("A:A").NumberFormat = "m/d/yyyy"                                     ' Format the column A of 2nd destination sheet to "m/d/yyyy"
'
    DestinationRowCounter = 1                                                                   ' Initialize DestinationRowCounter
'
    For SourceRow = 3 To SourceLastRowB Step 4                                                  ' SourceRow Loop
        DestinationRowCounter = DestinationRowCounter + 1                                       '   Increment DestinationRowCounter
'
        With wsDestination2
'           Copy condensed data from Source sheet to 2nd sheet
            .Range("A" & DestinationRowCounter).Resize(, 4).Value = wsSource.Range("A" & SourceRow).Resize(, 4).Value
        End With
    Next                                                                                        ' Loop back
'
    wsDestination2LastRowA = wsDestination2.Range("A" & Rows.Count).End(xlUp).Row               ' Get last row used of sheet2 column A
'
    UnsortedArray = wsDestination2.Range("A2:D" & wsDestination2LastRowA)                       ' Save unsorted values into 2D 1 based array RC
'
'--------------------------------------------------------------------------------------------- Start of sheet 3
'
    Sheets.Add After:=Worksheets(Sheets.Count)                                                  ' Add a new sheet to end of this workbook
    Set wsDestination3 = Worksheets(Sheets.Count)                                               ' Set wsDestination3 to last sheet in workbook
'
    wsDestination3.Range("A1:D1").Value = Array("Number of Charges", "Date", "Dollar Amount", "Merchant")   ' Insert the Header for 3rd Destination Sheet
    wsDestination3.Columns("B:B").NumberFormat = "m/d/yyyy"                                     ' Format the column B of 3rd destination sheet to "m/d/yyyy"
    wsDestination3.Range("A2:D" & wsDestination2LastRowA) = UnsortedArray                       ' Copy array to 3rd destination sheet
'
'   RANGE SORTER ... Least important column to most important column 4,2,1
    wsDestination3.Range("A2:D" & wsDestination2LastRowA).Sort Key1:=wsDestination3.Range("D2"), Order1:=xlDescending, _
        Key2:=wsDestination3.Range("B2"), Order1:=xlAscending, _
        Key3:=wsDestination3.Range("A2"), Order1:=xlAscending, Header:=xlNo
'
    SortedArray = wsDestination3.Range("A2:D" & wsDestination2LastRowA)                         ' Save sorted values into 2D 1 based array RC
'
    RowsToDelete = vbNullString                                                                 ' Initialize RowsToDelete string to blank
'
    For SortedArraySlot = UBound(SortedArray) To 2 Step -1                                          ' Backward loop through SortedArray
        If SortedArray(SortedArraySlot, 1) = SortedArray(SortedArraySlot - 1, 1) Then               '   If SortedArraySlot 1 = Previous slot then ...
            If SortedArray(SortedArraySlot, 2) = SortedArray(SortedArraySlot - 1, 2) Then           '       If SortedArraySlot 2 = Previous slot then ...
                If SortedArray(SortedArraySlot, 3) = SortedArray(SortedArraySlot - 1, 3) Then       '           If SortedArraySlot 3 = Previous slot then ...
                    If SortedArray(SortedArraySlot, 4) = SortedArray(SortedArraySlot - 1, 4) Then   '           If SortedArraySlot 4 = Previous slot then ...
                        RowsToDelete = RowsToDelete & SortedArraySlot & ":" & SortedArraySlot & "," '           Save row to delete to RowsToDelete string
'
                        If Len(RowsToDelete) > 240 Then                                             '       If length of RowsToDelete string > 240 then ...
                            If Right(RowsToDelete, 1) = "," Then                                    '       If RowsToDelete string ends with a comma then ...
                                RowsToDelete = (Left(RowsToDelete, Len(RowsToDelete) - 1))          '           remove the comma from the end of the string
                            End If
'
                            wsDestination3.Range(RowsToDelete).EntireRow.Delete                 '   Delete all of the rows saved to RowsToDelete in one swoop
                            RowsToDelete = vbNullString                                         '   Erase RowsToDelete string
                        End If
                    End If
                End If
            End If
        End If
    Next                                                                                        ' Loop back
'
    If Len(RowsToDelete) > 0 Then                                                               ' If there are rows to delete in RowsToDelete string then ...
        If Right(RowsToDelete, 1) = "," Then                                                    '   If RowsToDelete string ends with a comma then ...
            RowsToDelete = (Left(RowsToDelete, Len(RowsToDelete) - 1))                          '       remove the comma from the end of the string
        End If
'
        wsDestination3.Range(RowsToDelete).EntireRow.Delete                                     '   Delete all of the rows saved to RowsToDelete in one swoop
        RowsToDelete = vbNullString                                                             '   Erase RowsToDelete string
    End If
'
    wsDestination3LastRowA = wsDestination3.Range("A" & Rows.Count).End(xlUp).Row               ' Get last row used of sheet3 column A
'
    SortedNoDupesArray = wsDestination3.Range("A2:D" & wsDestination3LastRowA)                  ' Save sorted no duplicate values into 2D 1 based array RC
'
    Set MatchListArray = CreateObject("System.Collections.ArrayList")                           ' Initialize MatchListArray
'
    MatchCount = 0                                                                              ' Initialize MatchCount
'
    For SortedNoDupesArraySlot = 1 To UBound(SortedNoDupesArray)                                                ' Initialize SortedNoDupesArray loop
        For SortedArraySlot = 1 To UBound(SortedArray)                                                          '   Initialize SortedNoDupesArray loop
            If SortedArray(SortedArraySlot, 1) = SortedNoDupesArray(SortedNoDupesArraySlot, 1) Then
'           If SortedArray slot 1 = SortedNoDupesArray slot 1 then ...
'
                If SortedArray(SortedArraySlot, 2) = SortedNoDupesArray(SortedNoDupesArraySlot, 2) Then
'               If SortedArray slot 2 = SortedNoDupesArray slot 2 then ...
'
                    If SortedArray(SortedArraySlot, 3) = SortedNoDupesArray(SortedNoDupesArraySlot, 3) Then
'                   If SortedArray slot 3 = SortedNoDupesArray slot 3 then ...
'
                        If SortedArray(SortedArraySlot, 4) = SortedNoDupesArray(SortedNoDupesArraySlot, 4) Then
'                       If SortedArray slot 4 = SortedNoDupesArray slot 4 then Match has been found ...
                            MatchCount = MatchCount + 1                                                                 '           Increment MatchCount
                        End If
                    End If
                End If
            End If
        Next                                                                                                            '   Loop back
'
        MatchListArray.Add MatchCount                                                                                   '   Save MatchCount to MatchListArray
        MatchCount = 0                                                                                                  '   Reset MatchCount
    Next                                                                                                                ' Loop back
'
    wsDestination3.Range("D2:D" & wsDestination3LastRowA).Cut wsDestination3.Range("C2:C" & wsDestination3LastRowA)     ' Move column D data to column C
    wsDestination3.Range("B2:B" & wsDestination3LastRowA).Cut wsDestination3.Range("D2:D" & wsDestination3LastRowA)     ' Move column B data to column D
    wsDestination3.Range("A2:A" & wsDestination3LastRowA).Cut wsDestination3.Range("B2:B" & wsDestination3LastRowA)     ' Move column A data to column B
'
    wsDestination3.Range("A2").Resize(MatchListArray.Count, 1).Value = Application.Transpose(MatchListArray.ToArray)    ' Display MatchListArray to column A
'
'   RANGE SORTER ... Least important column to most important column 3,2,1
    wsDestination3.Range("A2:D" & wsDestination3LastRowA).Sort Key1:=wsDestination3.Range("D2"), Order1:=xlAscending
    wsDestination3.Range("A2:D" & wsDestination3LastRowA).Sort Key1:=wsDestination3.Range("C2"), Order1:=xlDescending
    wsDestination3.Range("A2:D" & wsDestination3LastRowA).Sort Key1:=wsDestination3.Range("B2"), Order1:=xlDescending
    wsDestination3.Range("A2:D" & wsDestination3LastRowA).Sort Key1:=wsDestination3.Range("A2"), Order1:=xlDescending ', Header:=xlNo
'
    wsDestination3.UsedRange.EntireColumn.AutoFit                                                                       ' Resize columns to fit headers/data
'
    Application.DisplayAlerts = False                                                                                   ' Disable DisplayAlerts to stop warning
    wsDestination2.Delete                                                                                               ' Delete sheet2
    Application.DisplayAlerts = True                                                                                    ' Turn DisplayAlerts back on
End Sub
 
Upvote 0
Solution
When I run it on my personal machine with my test data it works right, but then when i run it on my work station when it finishes running it works on some things but then alot it doesnt, it ends up putting the state location in the time column and the rest of the columns are blank. Ive gone over the code 20 times looking for typos as i cant copy the data to my work machine it must be retyped. Ive tried making different transactions in my test one but nothing makes its fail. Do you know what might cause that to happen. Im going to keep trying ill update you if i figure it out. Idk if it makes is easier or harder but im now realizing i didnt mention this when i first made the post. When I run these I mainly only look for a specific merchant, so in our examples say i was looking for just netflix out of all the charges.
 
Upvote 0
Ok set a break point (red dot) at the beginning of this code on your workstation version:

VBA Code:
    For SourceRow = 3 To SourceLastRowB Step 4                                                  ' SourceRow Loop
        DestinationRowCounter = DestinationRowCounter + 1                                       '   Increment DestinationRowCounter
'
        With wsDestination2
'           Copy condensed data from Source sheet to 2nd sheet
            .Range("A" & DestinationRowCounter).Resize(, 4).Value = wsSource.Range("A" & SourceRow).Resize(, 4).Value
        End With
    Next

Step through that bit of code and see if the sheet2 is loaded properly while stepping through it.

That will verify if the first part of the code had been typed in properly or not.
 
Upvote 0
So I double checked the code in the portion and its typed correctly. When stepping through it is date in both the date and merchant space, then the merchant is going in the transaction.
 
Upvote 0
Well, After some further looking. I realize the double dates were my fault as the data provided had 2 date columns which i removed one and it fixed the above problem. Now as for what it is actually doing, In the original example I did not thing about accounts also including the the deposits which are single line. So the step 4 is causing it to mess up when it gets to actual account. I believe I need some way to search through the transaction in the merchant name column to find the specific merchant in question
 
Upvote 0

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top