VBA code to compare and total values

S

ssddeell

New Member
Joined
Apr 24, 2018
Messages
9
I'm trying to create VBA code (or functions if possible) that will identify all matching entries in column A and then total the values in a range from columns B through E from the rows with the matching entries, and then continue on to the next set of matching entries in column A.

For example, the code would identify that the column A cells values A2, A3, and A4 match, total the values in range B2:E4, and enter the total in cell F2 (or F4). The code then would continue down column A identifying that cells A5 and A6 match, total the values in range B5:E6, and enter the total in cell F5 (or F6). And so on... The entries in column A will be sorted so like entries are always in order. See example below (the different colors are just for emphasis).

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 123px"><col width="123"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Company Name[/TD]
[TD="align: center"]Quantity 1[/TD]
[TD="align: center"]Quantity 2[/TD]
[TD="align: center"]Quantity 3[/TD]
[TD="align: center"]Quantity 4[/TD]
[TD="align: center"]Total[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]ABC[/TD]
[TD="align: center"][TABLE="width: 0"]
<tbody>[TR]
[TD="align: center"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]ABC[/TD]
[TD][/TD]
[TD="align: center"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]ABC[/TD]
[TD="align: center"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]EFG[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]4[/TD]
[TD][/TD]
[TD="align: center"]11[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]EFG[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]RST[/TD]
[TD="align: center"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]19[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]RST[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]RST[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]RST[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD]UVW[/TD]
[TD="align: center"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD]XYZ[/TD]
[TD][/TD]
[TD="align: center"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]5[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Note: The values got a bit messed-up in row 2 in my example; cell B2 that has a "1 1" value should be blank.
 
Upvote 0
Here's one way to do it:

Code: 
[B][COLOR=Royalblue]Sub[/COLOR][/B] a1077148b[B]()[/B]
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1077148-vba-code-compare-total-values.html[/COLOR][/I]
[B][COLOR=Royalblue]Dim[/COLOR][/B] i [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B][B],[/B] j [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B][B],[/B] k [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B][B],[/B] rr [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] va [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Variant[/COLOR][/B][B],[/B] vb [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Variant[/COLOR][/B]
 
rr [B]=[/B] Range[B]([/B][COLOR=brown]"A"[/COLOR] [B]&[/B] Rows.count[B]).[/B][B][COLOR=Royalblue]End[/COLOR][/B][B]([/B]xlUp[B]).[/B]row [B]+[/B] [B][COLOR=crimson]1[/COLOR][/B]
va [B]=[/B] Range[B]([/B][COLOR=brown]"A2:E"[/COLOR] [B]&[/B] rr[B])[/B]
 
[B][COLOR=Royalblue]ReDim[/COLOR][/B] vb[B]([/B][B][COLOR=crimson]1[/COLOR][/B] [B][COLOR=Royalblue]To[/COLOR][/B] UBound[B]([/B]va[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B]),[/B] [B][COLOR=crimson]1[/COLOR][/B] [B][COLOR=Royalblue]To[/COLOR][/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B]
 
[B][COLOR=Royalblue]For[/COLOR][/B] i [B]=[/B] [B][COLOR=crimson]1[/COLOR][/B] [B][COLOR=Royalblue]To[/COLOR][/B] UBound[B]([/B]va[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B] [B]-[/B] [B][COLOR=crimson]1[/COLOR][/B]
k [B]=[/B] i
        [B][COLOR=Royalblue]Do[/COLOR][/B]
               
            [B][COLOR=Royalblue]For[/COLOR][/B] j [B]=[/B] [B][COLOR=crimson]2[/COLOR][/B] [B][COLOR=Royalblue]To[/COLOR][/B] [B][COLOR=crimson]5[/COLOR][/B]
                vb[B]([/B]k[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B] [B]=[/B] vb[B]([/B]k[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B] [B]+[/B] va[B]([/B]i[B],[/B] j[B])[/B]
            [B][COLOR=Royalblue]Next[/COLOR][/B] j
            i [B]=[/B] i [B]+[/B] [B][COLOR=crimson]1[/COLOR][/B]
        [B][COLOR=Royalblue]Loop[/COLOR][/B] [B][COLOR=Royalblue]While[/COLOR][/B] UCase[B]([/B]Trim[B]([/B]va[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])))[/B] [B]=[/B] UCase[B]([/B]Trim[B]([/B]va[B]([/B]i [B]-[/B] [B][COLOR=crimson]1[/COLOR][/B][B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])))[/B]
i [B]=[/B] i [B]-[/B] [B][COLOR=crimson]1[/COLOR][/B]
[B][COLOR=Royalblue]Next[/COLOR][/B] i
Range[B]([/B][COLOR=brown]"F2"[/COLOR][B]).[/B]Resize[B]([/B]UBound[B]([/B]vb[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B]),[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B] [B]=[/B] vb
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]
 
Upvote 0
Thanks, I appreciate it. When I use the code, I get a type mismatch run-time error at the line vb(k, 1) = vb(k, 1) + va(i, j).


I looked over the code to try and fix, but I'm not familiar with much of it. I would attach the example file I'm using, but I do not have permission to attach files.
 
Upvote 0
How about

Excel 2013/2016
ABCDEFG
1Company NameQuantity 1Quantity 2Quantity 3Quantity 4
2ABC1ABC10
3ABC3EFG11
4ABC6RST19
5EFG4UVW4
6EFG7XYZ5
7RST3
8RST9
9RST5
10RST2
11UVW4
12XYZ5
Sheet2
Cell Formulas
RangeFormula
G2=SUMPRODUCT((A$2:A$12=F2)*(B$2:E$12))
F2{=INDEX(A$2:A$12,MATCH(0,COUNTIF(F$1:F1,A$2:A$12),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
ssddeell said:
Thanks, I appreciate it. When I use the code, I get a type mismatch run-time error at the line vb(k, 1) = vb(k, 1) + va(i, j).


I looked over the code to try and fix, but I'm not familiar with much of it. I would attach the example file I'm using, but I do not have permission to attach files.
Click to expand...

Maybe you got something that is not number in col B:E, could be a space.
Ok, try changing this line:

vb(k, 1) = vb(k, 1) + va(i, j)

to this:

If IsNumeric(va(i, j)) Then vb(k, 1) = vb(k, 1) + va(i, j)
 
Upvote 0
Thank you very much, great idea. I'm away from my PC at the moment and will give this a try. I need to use SUMPRODUCT more often.
 
Upvote 0
Any also thanks again for the follow-up on the VBA code. I may indeed have a space or other non-numeric value. I'll look for that typo as well as check out the new code. I do very much appreciate all responses.
 
Upvote 0
Re: VBA code to compare and total values- Resolved

Re the new line of VBA code, it now works great! I also checked to see if I had any non-numeric value(s), but could not find any, but again it now works great.

Re the formula approach, it works great as well. I actually prefer this approach for me personally as I can follow the logic and adjust easier. For example, I decided to total the quantity in each column rather than total the entire range. For me, this is very easy to do via a formula.

Anyway, again thanks to all.
 
Upvote 0
Re: VBA code to compare and total values- Resolved

Glad we could help & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Re-distributing numbers against a total value.
Replies
6
Views
229
DazCD
D
L
  • Question Question
Macro to correct entries across multiple sheets
Replies
1
Views
187
Lux Aeterna
L
L
  • Question Question
How to create a column that adds cells with a cell in common
Replies
6
Views
72
Fluff
Fluff
V
  • Question Question
Tracking individual codes by date and quantity
Replies
1
Views
36
Vorkosigan
V
F
  • Question Question
Rounding values to 2 decimel while keeping total 100%
2 3 4
Replies
38
Views
2K
awoohaw
awoohaw

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

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