Formula to identify / remove duplicates based on one column

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
644
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Product IDProduct NameCash SalesGroup ID
1234Nice Marmalade 70g1001234
1234Nice Marmalade 70g501234
2Nice Jam 70g01234
5678Cool Strawberry Juice 50ml2005678
5678Cool Strawberry Juice 50ml1005678
5Cool Orange Juice 50ml05678
2345Great Raspberry 70ml5002345
7Great Grapefruit 70ml02345
2345Great Raspberry 70ml5002345

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
How about
Code:
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
 
Upvote 0
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.
 
Upvote 0
Yes it will be possible, but that's beyond my formula ability.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
Can you answer my second question as well?
 
Upvote 0
Yes - sorry I missed it!

The product ID alone is sufficient!
 
Upvote 0
Ok, how about
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top