Formula to identify / remove duplicates based on one column

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
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>
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,312
Office Version
  1. 365
Platform
  1. Windows
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
 

Mr2017

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,312
Office Version
  1. 365
Platform
  1. Windows
Yes it will be possible, but that's beyond my formula ability.
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,312
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,312
Office Version
  1. 365
Platform
  1. Windows
Can you answer my second question as well?
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Yes - sorry I missed it!

The product ID alone is sufficient!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,312
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,121
Messages
5,622,861
Members
415,935
Latest member
kes1973

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
Top