Formula to identify / remove duplicates based on one column

[Mr2017]

Hi

I've got three groups of data over 9 rows.

Each group has duplicates and I'd like to delete the duplicate rows based on data in column C, which has Cash Sales.

In the table below,
column A has Product IDs,
column B has Product Names,
column C has Cash Sales and
column D has the Group ID

If a duplicate row is in the data, as is the case with rows 2 and 3, I would like to delete the row where the Cash Sales in column C are either greater OR equal to the other row where there is a duplicate Product ID.

So with rows 2 and 3, row 2 would be deleted because the Cash Sales in column C for row 2 are 100.

Whilst the Cash Sales in column C for the same product in row 3 are 50.

Has anyone done this before? Any thoughts would be greatly appreciated! TIA

[TABLE="width: 470"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Product ID[/TD]
[TD]Product Name[/TD]
[TD]Cash Sales[/TD]
[TD]Group ID[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]Nice Marmalade 70g[/TD]
[TD]100[/TD]
[TD]1234[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]Nice Marmalade 70g[/TD]
[TD]50[/TD]
[TD]1234[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Nice Jam 70g[/TD]
[TD]0[/TD]
[TD]1234[/TD]
[/TR]
[TR]
[TD]5678[/TD]
[TD]Cool Strawberry Juice 50ml[/TD]
[TD]200[/TD]
[TD]5678[/TD]
[/TR]
[TR]
[TD]5678[/TD]
[TD]Cool Strawberry Juice 50ml[/TD]
[TD]100[/TD]
[TD]5678[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Cool Orange Juice 50ml[/TD]
[TD]0[/TD]
[TD]5678[/TD]
[/TR]
[TR]
[TD]2345[/TD]
[TD]Great Raspberry 70ml[/TD]
[TD]500[/TD]
[TD]2345[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Great Grapefruit 70ml[/TD]
[TD]0[/TD]
[TD]2345[/TD]
[/TR]
[TR]
[TD]2345[/TD]
[TD]Great Raspberry 70ml[/TD]
[TD]500[/TD]
[TD]2345[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

How about

Sub Mr2017()
   Dim Cl As Range, Rng As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         If Not .Exists(Cl.Value & "|" & Cl.Offset(, 1).Value) Then
            .Add Cl.Value & "|" & Cl.Offset(, 1).Value, Cl.Offset(, 2)
         ElseIf Cl.Offset(, 2).Value < .Item(Cl.Value & "|" & Cl.Offset(, 1).Value).Value Then
            If Rng Is Nothing Then Set Rng = .Item(Cl.Value & "|" & Cl.Offset(, 1).Value) Else Set Rng = Union(Rng, .Item(Cl.Value & "|" & Cl.Offset(, 1).Value))
            Set .Item(Cl.Value & "|" & Cl.Offset(, 1).Value) = Cl.Offset(, 2)
         Else
            If Rng Is Nothing Then Set Rng = Cl.Offset(, 2) Else Set Rng = Union(Rng, Cl.Offset(, 2))
         End If
      Next Cl
   End With
   If Not Rng Is Nothing Then Rng.EntireRow.Delete
End Sub

 

[Mr2017]

Awesome!! Thanks - worked like Magic!!

Just one last one - do you know if it's possible to identify the rows to be deleted with a formula?

If not, I can still use the VBA code.

Thanks in advance.

 

[Fluff]

Yes it will be possible, but that's beyond my formula ability.

 

[Mr2017]

Ok, no problem.

Thank you.

A final thought I had was how to adapt it to run on multiple sheets?

So at present, it would run on the active sheet. Is it easy to modify it so it runs on n number of sheets?

Eg 3 sheets or 5 sheets?

Thanks in advance.

 

[Fluff]

Yes that's possible, what sheets should it run on?
Also I think I've over complicated things. should it just look at the product Id, or both the ID & the name to identify duplicates?

 

[Mr2017]

Thanks for the prompt response.

The code would ideally run on every worksheet in the file that it's in.

Some files could contain two sheets, but others could contain 10 sheets.

Is it possible to add in a a loop that runs until it's finished running through all the sheets in the workbook?

 

[Fluff]

Can you answer my second question as well?

 

[Mr2017]

Yes - sorry I missed it!

The product ID alone is sufficient!

 

[Fluff]

Ok, how about

Sub Mr2017()
   Dim Cl As Range, Rng As Range
   Dim Ws As Worksheet
   
   With CreateObject("scripting.dictionary")
      For Each Ws In Worksheets
         For Each Cl In Ws.Range("A2", Ws.Range("A" & Rows.Count).End(xlUp))
            If Not .Exists(Cl.Value) Then
               .Add Cl.Value, Cl.Offset(, 2)
            ElseIf Cl.Offset(, 2).Value < .Item(Cl.Value).Value Then
               If Rng Is Nothing Then Set Rng = .Item(Cl.Value) Else Set Rng = Union(Rng, .Item(Cl.Value))
               Set .Item(Cl.Value) = Cl.Offset(, 2)
            Else
               If Rng Is Nothing Then Set Rng = Cl.Offset(, 2) Else Set Rng = Union(Rng, Cl.Offset(, 2))
            End If
         Next Cl
         If Not Rng Is Nothing Then Rng.EntireRow.Delete
         Set Rng = Nothing
         .RemoveAll
      Next Ws
   End With
End Sub