VBA code to compare and total values

[ssddeell]

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>

	A	B	C	D	E	F
1	Company Name	Quantity 1	Quantity 2	Quantity 3	Quantity 4	Total
2	ABC	1 1 <tbody> </tbody>			1	10
3	ABC		3
4	ABC	6
5	EFG			4		11
6	EFG				7
7	RST	3				19
8	RST			9
9	RST			5
10	RST				2
11	UVW	4				4
12	XYZ		5			5

<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>
</tbody>

 

[ssddeell]

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.

 

[Akuini]

Here's one way to do it:

[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]

 

[ssddeell]

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.

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F	G
1	Company Name	Quantity 1	Quantity 2	Quantity 3	Quantity 4
2	ABC				1	ABC	10
3	ABC		3			EFG	11
4	ABC	6				RST	19
5	EFG			4		UVW	4
6	EFG				7	XYZ	5
7	RST	3
8	RST			9
9	RST			5
10	RST				2
11	UVW	4
12	XYZ		5
Sheet2

Cell Formulas
Range	Formula
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.

 

[Akuini]

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.

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)

 

[ssddeell]

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.

 

[ssddeell]

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.

 

[ssddeell]

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.

 

[Fluff]

Re: VBA code to compare and total values- Resolved

Glad we could help & thanks for the feedback