VBA - sum of Miles in Column B = to Name in Column A

[smythcounty]

Hello,

I know this is going to be simple for you all but I've fried my brain trying to get this two work.

I have names of people in column A and miles traveled in column B. How can I get the total sum of miles traveled by each person in VBA?

A (Name) B (Miles)
Jim Smith 20
Nancy Cook 21
John Daly 25
Jim Acosta 26
John Daly 35
Nancy Cook 84
Steve Durst 86
Steve Durst 44
John Daly 55

Jim Smith Total =?
Nancy Cook Total =?
John Daly Total =?
Jim Acosta Total = ?

I hope that makes sense.

Thanks!

 

[smythcounty]

Hopefully this helps out

 

[Crystalyzer]

Why VBA? Are you processing it further? if so, what is the end goal?

 

[Crystalyzer]

Why not use SUMIF formula?

 

[smythcounty]

Why VBA? Are you processing it further? if so, what is the end goal?

Yes, this is a large CSV that I'm filtering and preparing for a daily report in VBA. This is just one of the items needed.

 

[smythcounty]

Why VBA? Are you processing it further? if so, what is the end goal?

Maybe this will let you see more into my project

 

[Crystalyzer]

Can you post a sample of the data using the XL2BB tool (instructions in signature)? This way I can see column and rows and assist with a vba solution.

 

[Fluff]

How about

Sub smythcounty()
   Dim Ary As Variant
   Dim Dic As Object
   Dim i As Long
   
   Ary = Range("A2:B" & Range("A" & Rows.Count).End(xlUp)).Value2
   
   With CreateObject("scripting.dictionary")
      For i = 1 To UBound(Ary)
         .Item(Ary(i, 1)) = .Item(Ary(i, 1)) + Ary(i, 2)
      Next i
      Sheets.Add.Name "Results"
      Sheets("Results").Range("A2").Resize(.Count, 2).Value = Application.Transpose(Array(.Keys, .Items))
   End With
End Sub

 

[smythcounty]

VBA Upload.xlsx
	A	B	C	D	E	F	G	H	I
1	Truck	Driver	Date	Start Time	End Time	Miles	Total Miles	DEPARTURE TIME	ARRIVAL TIME
2	101	Jsmith	7/28/2021	06:27:09	-	0.43		6:27:09 AM	6:37:00 AM
3	101	Jsmith	7/28/2021	-	-	0.12		6:47:00 AM	6:49:00 AM
4	101	Jsmith	7/28/2021	-	-	0.12		7:15:17 AM	7:19:07 AM
5	101	Jsmith	7/28/2021	-	-	0.06		7:25:18 AM	7:29:08 AM
6	101	Jsmith	7/28/2021	-	-	6.21		7:37:18 AM	8:00:52 AM
7	101	Jsmith	7/28/2021	-	-	17.03		8:09:19 AM	8:26:20 AM
8	101	Jsmith	7/28/2021	-	-	56.36		8:31:20 AM	9:41:22 AM
9	101	Jsmith	7/28/2021	-	-	3.29		9:49:23 AM	10:29:24 AM
10	101	Jsmith	7/28/2021	-	-	0.12		10:57:25 AM	10:59:25 AM
11	101	Jsmith	7/28/2021	-	-	51.45		11:09:25 AM	12:39:14 PM
12	101	Jsmith	7/28/2021	-	-	30.01		12:47:29 PM	1:37:30 PM
13	101	Jsmith	7/28/2021	-	-	6.28		2:03:33 PM	2:25:27 PM
14	101	Jsmith	7/28/2021	-	-	45.05		2:35:27 PM	3:23:29 PM
15	101	Jsmith	7/28/2021	-	-	0.19		3:31:34 PM	3:37:34 PM
16	101	Jsmith	7/28/2021	-	17:25:31	51.51		4:11:36 PM	5:19:38 PM
17	102	Ljohnson	7/28/2021	03:03:10	-	142.98		3:35:11 AM	6:05:16 AM
18	102	Ljohnson	7/28/2021	-	-	5.53		6:15:16 AM	6:31:17 AM
19	102	Ljohnson	7/28/2021	-	-	0.43		7:01:18 AM	7:09:18 AM
20	102	Ljohnson	7/28/2021	-	-	0.19		7:17:18 AM	7:21:19 AM
21	102	Ljohnson	7/28/2021	-	-	0.12		7:43:19 AM	7:47:19 AM
22	102	Ljohnson	7/28/2021	-	-	0.81		7:57:31 AM	8:13:20 AM
23	102	Ljohnson	7/28/2021	-	-	49.96		8:19:21 AM	9:35:32 AM
24	102	Ljohnson	7/28/2021	-	-	0.37		9:41:32 AM	9:47:24 AM
25	102	Ljohnson	7/28/2021	-	-	0.12		10:47:26 AM	10:49:33 AM
26	102	Ljohnson	7/28/2021	-	-	0.56		11:21:27 AM	11:33:27 AM
27	102	Ljohnson	7/28/2021	-	-	160.25		11:39:27 AM	2:47:34 PM
28	102	Ljohnson	7/28/2021	-	-	0.25		2:57:34 PM	3:01:34 PM
29	102	Ljohnson	7/28/2021	-	-	0.43		3:15:35 PM	3:29:35 PM
30	102	Ljohnson	7/28/2021	-	-	0.12		3:43:35 PM	3:43:45 PM
31	102	Ljohnson	7/28/2021	-	-	0.31		3:49:36 PM	3:51:47 PM
32	102	Ljohnson	7/28/2021	-	-	0.43		3:59:36 PM	4:03:47 PM
33	102	Ljohnson	7/28/2021	-	-	0.5		4:13:37 PM	4:29:37 PM
34	102	Ljohnson	7/28/2021	-	-	0		4:35:46 PM	4:37:37 PM
35	102	Ljohnson	7/28/2021	-	-	0.06		4:43:38 PM	4:45:38 PM
36	102	Ljohnson	7/28/2021	-	17:18:34	0.31		5:09:38 PM	5:18:34 PM
manifest-by-vehicle-active-07-2

Can you post a sample of the data using the XL2BB tool (instructions in signature)? This way I can see column and rows and assist with a vba solution.

Can you post a sample of the data using the XL2BB tool (instructions in signature)? This way I can see column and rows and assist with a vba solution.

 

[Fluff]

As your data is not in columns A & B (as per your op) my code is now

Sub smythcounty()
   Dim Ary As Variant
   Dim Dic As Object
   Dim i As Long
  
   Ary = Range("B2:F" & Range("B" & Rows.Count).End(xlUp).Row).Value2
  
   With CreateObject("scripting.dictionary")
      For i = 1 To UBound(Ary)
         .Item(Ary(i, 1)) = .Item(Ary(i, 1)) + Ary(i, 5)
      Next i
      Sheets.Add.Name ="Results"
      Sheets("Results").Range("A2").Resize(.Count, 2).Value = Application.Transpose(Array(.Keys, .Items))
   End With
End Sub

Edited to correct code