macro for defining range

I have a large amount of data in a spreadsheet.  I am tracking various data by part number.  This workbook is sorted by Part number (column "A").   I am wanting to write a macro to find the largest number (in Column "G") for each group of duplicate part numbers.  Once this number is found from the group I need to associate the machine (column "E") to it.  I would expect for it to produce Machine #200 for part number 1 below.  Anyone have an idea on how to do this? 

Can I ask why you want to use a Macro as opposed to just formulas?

A formula to put an astrisk in rows corresponding to the machine with the greatest number in "G" for each part# in "A". Paste into any unused cell in row 2:

=IF(G2=MAX(INDIRECT("G"&MATCH(A2,A:A,0)&":G"&MATCH(A2,A:A,0)+COUNTIF(A:A,A2)-1)),"*","")

The MATCH/COUNTIF formulas establish a vertical range for each part number. Using "&" and the text "G" we create a fake range of cells, which then get thrown into the "INDIRECT" formula, which make that fake range a real range. The MAX formula picks the largest amount from this range. Finally, the IF formula tests whether the amount equals the MAX amount for that part #. The advantage of this method is that it would allow you to identify cases where two machines on the same part# have an equal "amount".

Another option with formulas would be to have a separate sheet with your list of part numbers, listed once each, say in column A:

=INDIRECT("'[SHEETNAME]'!E"&MATCH(MAX(INDIRECT("G"&MATCH(A2,'[SHEETNAME]'!A:A,0)&":G"&MATCH(A2,'[SHEETNAME]'!A:A,0)+COUNTIF('[SHEETNAME]'!A:A,A2)-1)),INDIRECT("G"&MATCH(A2,'[SHEETNAME]'!A:A,0)&":G"&MATCH(A2,'[SHEETNAME]'!A:A,0)+COUNTIF('[SHEETNAME]'!A:A,A2)-1),0)+MATCH(A2,'[SHEETNAME]'!A:A,0)-1)

There are ways to do this with macros too, but they're much more complex, IMO, so unless you literally need something in VBA to trigger a process that will begin production or whatever, I'd stick to the formulas.

-Clive