VBA code to sum based on criteria

[Scotty001]

Hi all,

I need a macro that can take a list of parts and make a sum based on the first 8 characters but for the result I need it to return the part with the highest ending, the list would look like this: Lets take first part for example, I need to sum all parts that have first 8 characters 211M1521 (total would be 39) and as result I need it to show me the part with highest ending so result would be 211M152103 39 and I need it to do that for a list of maybe 10k parts. Thank you in advance.
211M152102 13
211M152103 13
211M152103 10
211M152102 3
211M153201 10
211M153201 11
211M153201 13
211M153202 12
211M153202 1
211M153201 10
211M154501 2
211M154501 9
211M154502 6
211M154502 7
211M154501 13
211M154501 13
211M155602
13

 

[Hui]

=SUMPRODUCT((LEFT(A1:A17,8)="211M1521")*(RIGHT(A1:A17,LEN(A1:A17)-FIND(" ",A1:A17))))

I'd use a Named Formula for my data and hence would use:
=SUMPRODUCT((LEFT(Data,8)="211M1521")*(RIGHT(Data,LEN(Data)-FIND(" ",Data))))

 

[Scotty001]

Can't seem to make it work to well also my list contains a lot of different parts so I need it to run in the whole list and get me a result with all the parts I will upload the list as example on firs sheet is the raw data and second list is how I need it to look after. Also the code I'm currently using to add all up is this but I also need it to add up the parts with different ending and the result to be with the highest ending as I said in the first place.

Sub Button1_Click()
Set inf = ActiveWorkbook.Worksheets("information")
Set rez = ActiveWorkbook.Worksheets("rezult")
Range(Cells(5, 1), Cells(10000, 2)).ClearContents
a = 1
Do While inf.Cells(a, 1) <> ""
  x = 5
  Do While rez.Cells(x, 1) <> ""
  If inf.Cells(a, 1) = rez.Cells(x, 1) Then
  rez.Cells(x, 2) = rez.Cells(x, 2) + inf.Cells(a, 2)
  GoTo urmatorul
  End If
  x = x + 1
  Loop
  rez.Cells(x, 1) = inf.Cells(a, 1)
  rez.Cells(x, 2) = inf.Cells(a, 2)
urmatorul:
a = a + 1
Loop
 Range("A5:B5").Select
 Range(Selection, Selection.End(xlDown)).Select
End Sub

 

[Hui]

In Result!C1: =SUMPRODUCT((LEFT('Raw List'!$A$1:$A$1319,8)=LEFT($A1,8))*('Raw List'!$B$1:$B$1319))

copy down

 

[Scotty001]

Ok thank you I see how the formula works now how can I include it in my macro (attached is the exact file I use to calculate a total) so that it sums them up and don't keep duplicates in the list if u run the macro you can understand what I mean and my problem is that I need it to sum them up the same way but keep only the highest ending for those parts, right now it counts them separately.