In Excel, remove duplicates from one column based on the values in another column, either through VBA or a combination of formulas/functions -

i'm having trouble trying achieve in accurate , automated way. i've tried approaches discussed here, here , here, none work in scenario.

i have spreadsheet thousands of rows of data. data organised follows:

• column contains ip addresses in general format
• column b contains date/time in following custom format (d/mm/yyyy h:mm)
• column c contains duration in following custom format (h:mm:ss)

this data contains number of duplicates need remove, based on ip address in column a. however, criteria need remove whichever duplicates not longest duration. better explain scenario, see sample image below:

i need way remove duplicates of particular ip address not contain longest duration ip address. so, using above example, row 3 deleted because duration of 1 minute shorter 36 minutes in row 4 contains same ip address.

another example rows 5, 6 , 7 removed durations shorter row 8 has same ip address longer duration. of course, rows containing unique ip addresses left alone. end result using above sample follows:

of course, in sample above data nicely sorted ip address first , duration second. in real life isn't case, that's easy enough me prior solution, if necessary.

the key thing in cases ip address may duplicated once, in others may duplicated many times over. need ensure 1 longest duration remains. in event multiple instances of ip address has same longest duration, want them kept. is, if ip address repeated ten times , longest duration hour 2 of times, both of them need remain.

i'm happy solution this, using formulas, functions or macros.

you can solve task using helper column (column d).

1. insert following array formula cell d2:

   =if($c2=max(if($a2=$a$2:$a$50,$c$2:$c$50,-1)),"remain","remove")

   where 50 - last row of table

   remember press ctrl+shift+enter complete array formula correctly.

2. copy/paste formula other cells.

3. Аpply filter column d "remove" value

4. delete filtered rows.