Deleting Duplicates in a Column based on a value in next column

[RugerGP100]

in Excel 2010:

I currently get Excel reports given to me from my companies CRM tool. It shows me activity history in every account. There are many duplicate values in the Account name column and I need to delete the duplicates leaving only the value for the time the account reached the farthest stage in our sales cycle (This is not necessarily the most recent move). Let's say the stages are 1-4 with 4 being a closed deal and 1 being an introductory call. I need to delete all the rows with duplicate data that are not the highest value in advanced Stage. In the following example I would only want to keep Rows 4 and 9. THANKS FOR YOUR HELP AND INSIGHT!!!!


[TABLE="width: 500"]
<tbody>[TR]
[TD]ROW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Account[/TD]
[TD]Advanced to Stage[/TD]
[TD]Source[/TD]
[TD]Month[/TD]
[TD]Rep[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ABC Company[/TD]
[TD]2[/TD]
[TD]Referral[/TD]
[TD]March[/TD]
[TD]Steve[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ABC Company[/TD]
[TD]1[/TD]
[TD]Referral[/TD]
[TD]March[/TD]
[TD]Steve[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]ABC Company[/TD]
[TD]3[/TD]
[TD]Referral[/TD]
[TD]March[/TD]
[TD]Steve[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]ABC Company[/TD]
[TD]2[/TD]
[TD]Referral[/TD]
[TD]March[/TD]
[TD]Steve[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]123 Enterprises[/TD]
[TD]2[/TD]
[TD]NY Times[/TD]
[TD]June[/TD]
[TD]Nancy[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]123 Enterprises[/TD]
[TD]3[/TD]
[TD]NY Times[/TD]
[TD]June[/TD]
[TD]Nancy[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]123 Enterprises[/TD]
[TD]3[/TD]
[TD]NY Times[/TD]
[TD]June[/TD]
[TD]Nancy[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]123 Enterprises[/TD]
[TD]4[/TD]
[TD]NY Times[/TD]
[TD]June[/TD]
[TD]Nancy[/TD]
[/TR]
</tbody>[/TABLE]

 

[Asala42]

Are you looking for a quick & easy approach or a fully automated one?

Here's the quick&easy version:
(I'm assuming your data range is A1:E100, with row 1 as a header row).

1) Click anywhere in your data range
2) Data>Sort
3) Sort with 2 layers. First layer: Account ascending. 2nd layer: Stage number descending
4) Insert a label in F1 (any text is fine, for example "Latest Stage")
5) In F2 type the formula: =IF(A2<>A1,1,0)
6) Copy this formula down the length of the table. 1's will be next to the latest stage for each account.
7) Clear the formulas in column F. (via copy/paste special as values)
8) Resort the table by column F descending.
9) Ctrl+F to find the first zero entry in the table. Select and clear all values below and including that row (select the row, shift+end+down arrow, delete)
10) Optional: delete column F - it's no longer needed.

This will leave you with only 1 record per account entry, at the accounts' latest stage.

 

[MickG]

Try this:-
Results sheet (2)

[COLOR="Navy"]Sub[/COLOR] MG29Aug18
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] K
[COLOR="Navy"]Dim[/COLOR] c           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.count).End(xlUp))
    [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn.Offset(, 1)
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]If[/COLOR] Dn.Offset(, 1) > .Item(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Dn.Offset(, 1)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .Keys
    c = c + 1
    Sheets("Sheet2").Range("A" & c).Resize(, 5).Value = .Item(K).Offset(, -1).Resize(, 5).Value
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick