In Excel, remove duplicates from one column based on the values in another column, either through VBA or a combination of formulas/functions
<p>I'm having trouble trying to achieve this in an accurate and automated way. I've tried the approaches discussed <a href="https://stackoverflow.com/questions/29745335/conditionally-removing-duplicates-in-excel-based-on-highest-value-in-a-column">here</a>, <a href="https://stackoverflow.com/questions/15306596/how-to-remove-duplicate-with-certain-condition">here</a> and <a href="https://stackoverflow.com/questions/10996126/removing-duplicates-with-condition-in-excel">here</a>, but none work in my scenario.</p>

<p>I have a spreadsheet with thousands of rows of data. Data is organised as follows:</p>

<ul>
<li>Column A contains IP addresses in <strong>General format</strong></li>
<li>Column B contains Date/Time in the following Custom format (<strong>d/mm/yyyy h:mm</strong>)</li>
<li>Column C contains duration in the following Custom format (<strong>h:mm:ss</strong>)</li>
</ul>

<p>This data contains a number of duplicates I need to remove, based on the IP address in Column A. However, the criteria I need is to remove whichever duplicates are <strong>not</strong> the longest duration. To better explain my scenario, see sample image below:</p>

<p><a href="https://i.stack.imgur.com/M7cpc.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/M7cpc.jpg" alt="enter image description here"></a></p>

<p>I need a way to remove all duplicates of a particular IP address that do not contain the longest duration for that IP address. So, using the above example, row 3 would be deleted because the duration of 1 minute is shorter than 36 minutes in row 4 that contains the same IP address.</p>

<p>Another example is that rows 5, 6 and 7 would also be removed as all their durations are shorter than row 8 which has the same IP address but a longer duration. Of course, any rows already containing unique IP addresses would be left alone. The end result using my above sample would be as follows:</p>

<p><a href="https://i.stack.imgur.com/uw0xV.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/uw0xV.jpg" alt="enter image description here"></a></p>

<p>Of course, in my sample above all the data was nicely sorted by IP address first and Duration second. In real life this isn't the case, but that's something easy enough for me to do prior to any solution, if necessary.</p>

<p><strong>The key thing is that in some cases an IP address may be duplicated once, in others it may be duplicated many times over. I just need to ensure that only the one with the longest duration remains. In the event that multiple instances of an IP address has the same longest duration, then I want them all kept. That is, if an IP address is repeated ten times and its longest duration is an hour for two of those times, then both of them need to remain.</strong></p>

<p>I'm happy with any solution for this, be it using formulas, functions or macros. </p>