Hello,
I'm new to VBA and am in over my head. Up until now I have been successfully building a program to format a monthly data output into something I can compare to another worksheet with ease.
I have since reached an obstacle that I cannot find a solution for online. Essentially what I have is a worksheet of hundreds of accounts with a total charge for each account next to it. When there are multiple entries for one account, I have written a code to add a blank row under each grouping of identical accounts to add a subtotal for each group.
The issue is that each group of accounts varies and will continue to vary with each new report. My thought process behind a way to solve this might be complex. What I have done is assigned a "1" in column A to each duplicated account and 0's for the non duplicates. The idea is that when I use sumproduct, it will only add the values for which accounts are duplicated as the 0's will end up with a null result.
I am really at a loss as to how to do this. I will try and draw out a sample worksheet below as I cannot figure out how to attach it. Any help would be appreciated. Also not that I cannot use a pivot table as it is neccessary that I see the comments section of each individual account entry (instead of them being grouped up).
-------A ------ B -------- C
1 ---- 0/1 ---- ACCT# ---- $
2 ---- 0 ----- 165 ------ 5.00
3 ---- 0 ----- 167 ------ 6.00
4 ---- 1 ----- 161 ------ 4.00
5 ---- 1 ----- 161 ------ 3.00
6 ---------------------- SUMPRODUCT
7 ---- 0 ----- 169 ------ 4.00
8 ---- 1 ----- 171 ------ 5.00
9 ---- 1 ----- 171 ------ 2.00
10 ---- 1 ----- 171 ------ 3.00
11 ---- 1 ----- 171 ------ 1.00
12 ------ ---- ---- -----SUMPRODUCT
13 ---- 0 ----- 173 ------ 6.00
Thanks in advance!!
I'm new to VBA and am in over my head. Up until now I have been successfully building a program to format a monthly data output into something I can compare to another worksheet with ease.
I have since reached an obstacle that I cannot find a solution for online. Essentially what I have is a worksheet of hundreds of accounts with a total charge for each account next to it. When there are multiple entries for one account, I have written a code to add a blank row under each grouping of identical accounts to add a subtotal for each group.
The issue is that each group of accounts varies and will continue to vary with each new report. My thought process behind a way to solve this might be complex. What I have done is assigned a "1" in column A to each duplicated account and 0's for the non duplicates. The idea is that when I use sumproduct, it will only add the values for which accounts are duplicated as the 0's will end up with a null result.
I am really at a loss as to how to do this. I will try and draw out a sample worksheet below as I cannot figure out how to attach it. Any help would be appreciated. Also not that I cannot use a pivot table as it is neccessary that I see the comments section of each individual account entry (instead of them being grouped up).
-------A ------ B -------- C
1 ---- 0/1 ---- ACCT# ---- $
2 ---- 0 ----- 165 ------ 5.00
3 ---- 0 ----- 167 ------ 6.00
4 ---- 1 ----- 161 ------ 4.00
5 ---- 1 ----- 161 ------ 3.00
6 ---------------------- SUMPRODUCT
7 ---- 0 ----- 169 ------ 4.00
8 ---- 1 ----- 171 ------ 5.00
9 ---- 1 ----- 171 ------ 2.00
10 ---- 1 ----- 171 ------ 3.00
11 ---- 1 ----- 171 ------ 1.00
12 ------ ---- ---- -----SUMPRODUCT
13 ---- 0 ----- 173 ------ 6.00
Thanks in advance!!
Last edited:
