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

[wafflesncoke]

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

 

[johnnyL]

I cant post the statement but i can describe the best i can...

You don't have to post the actual statement, but I think you will need to give us some examples, you can make up/alter the data as you feel needed. The samples you provide should include the before and the after that you would like to see happen.

 

[johnnyL]

Something like the following is what I envision thus far as the before data:

Book1
	A	B	C	D	E	F	G
311
312	11/15/2021	Wells Fargo
313		Wells Fargo
314		New York
315		USA
316
Sheet1

Just need some more info from you.

 

[wafflesncoke]

Book1
	A
1	Number of Charge
Sheet3

Book1
	A	B	C	D
1	Number of Charge	Date	Dollar Amount	Merchant
2	2	9/1/2021	15.99	Netflix
3	2	9/2/2021	13.99	Netflix
Sheet3

 

[johnnyL]

Why does that not match the info provided in post #1?

 

[wafflesncoke]

Sorry new to this minisheet thing. Try this again. This should be the data that i get for the transactions at first

Book1
	A	B	C	D
1	Date	Merchant	Transaction	Amount
2	M/D/Y	Name/Location	Type
3	9/1/2021	Netflix	Purchase	15.99
4		Netlifx
5		State
6		Country
7	9/2/2021	Netflix	Purchase	13.99
8		Netlifx
9		State
10		Country
11	9/1/2021	Netflix	Purchase	15.99
12		Netlifx
13		State
14		Country
15	9/2/2021	Netflix	Purchase	13.99
16		Netlifx
17		State
18		Country
Sheet1

Then I add filters and select the amount column and uncheck blanks so it looks like this

Book1
	A	B	C	D
1	Date	Merchant	Transaction	Amount
3	9/1/2021	Netflix	Purchase	15.99
7	9/2/2021	Netflix	Purchase	13.99
11	9/1/2021	Netflix	Purchase	15.99
15	9/2/2021	Netflix	Purchase	13.99
Sheet2

I then have to go through the list and put it a separate sheet like this

Book1
	A	B	C	D
1	Number of Charges	Date	Dollar Amount	Merchant
2	2	9/1/2021	15.99	Netflix
3	2	9/2/2021	13.99	Netflix
Sheet3

 

[johnnyL]

Ok I have code to create up to sheet 3. What happens if you added two different charges on the same day to Walmart for different amounts? How would that make sheet3 look?

 

[wafflesncoke]

If there were 2 charges to walmart on the same date for different amounts it would be listed on separate lines on the third sheet.

Book1.xlsx
	A	B	C	D
1	Number of Charges	Date	Dollar Amount	Merchant
2	2	9/1/2021	15.99	Netflix
3	2	9/2/2021	13.99	Netflix
4	1	9/2/2021	45.26	Walmart
5	1	9/2/2021	31.26	Walmart
Sheet3

 

[johnnyL]

@wafflesncoke, couple questions for you before I wrap up the code:

1) How do you want the data sorted in sheet3? I am not referring to the column order. I am referring to the order of relevance.
ie. I assume the dates have the highest priority (oldest to newest), after that do you want the merchants sorted alphabetically by dates or the amounts(highest to lowest) by dates or ...?

So I need to know the order of relevance, from highest to lowest, and the sort preference of low to high or high to low for all four.
ie. Dates(low to high / Merchants(low to high) / Amounts(High to low) / last choice can not be set due to the previous three preferences.

2) Are you wanting to retain the second sheet or delete it which would leave just the first sheet and the final result sheet?

 

[wafflesncoke]

The count of how many charges high to low then date high to low then dollar amount high to low merchant order doesn’t matter that much

And if it’s possible to delete sheet 2 that would be perfect