VBA : Delete Duplicates based on Criteria

Y

Yuenk

New Member
Joined
Nov 17, 2014
Messages
15
Hello everyone,

I don't know <acronym title="visual basic for applications">VBA</acronym> and filtering thousands lines of data won’t cut it.

I have a list of jobs that are being worked on. I want to filter out duplicates based on the Location Status and Serial Number. A location will have a serial number that is a unique number. At a location they may change the plan which will create another line item therefor creating a "duplicate." The old job will show as canceled and the new job will have different Status (ex: Installed or Proposed). Only when there is a duplicate serial number I need the active job in the worksheet and have the cancelled job to be moved to another sheet.

Thank you for any help

[TABLE="class: cms_table_grid, width: 1747"]
<tbody>[TR]
[TD]
[TABLE="class: cms_table, width: 1747"]
<tbody>[TR="class: cms_table_grid"]
[TD]Proper/Error
[/TD]
[TD]District
[/TD]
[TD]Work Number
[/TD]
[TD]WN Status
[/TD]
[TD]WN Type
[/TD]
[TD]Class
[/TD]
[TD]Area
[/TD]
[TD]Version
[/TD]
[TD]Hist Rev #
[/TD]
[TD]Official Version?
[/TD]
[TD]Revision Status
[/TD]
[TD]ID
[/TD]
[TD]Location Status
[/TD]
[TD]Dsgn Action
[/TD]
[TD]Dsgn QTY
[/TD]
[TD]AB Action
[/TD]
[TD]AB QTY
[/TD]
[TD]AB Addition
[/TD]
[TD]Dsgn #
[/TD]
[TD]AB #
[/TD]
[TD]Completion
[/TD]
[TD]Asset
[/TD]
[TD]Serial Number
[/TD]
[TD]Facility
[/TD]
[TD]Address
[/TD]
[/TR]
[TR="class: cms_table_grid"]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]1111555
[/TD]
[TD]SCH
[/TD]
[TD]E_EOS
[/TD]
[TD]OH
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Working
[/TD]
[TD]502
[/TD]
[TD]Installed
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]123456
[/TD]
[TD][/TD]
[TD]13
[/TD]
[TD]591229
[/TD]
[TD]Structure
[/TD]
[TD]123 Main St
[/TD]
[/TR]
[TR="class: cms_table_grid"]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]1111556
[/TD]
[TD]SCH
[/TD]
[TD]E_EOS
[/TD]
[TD]OH
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Working
[/TD]
[TD]451
[/TD]
[TD]Cancelled
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]123456
[/TD]
[TD][/TD]
[TD]13
[/TD]
[TD]591229
[/TD]
[TD]Structure
[/TD]
[TD]123 Main St
[/TD]
[/TR]
[TR="class: cms_table_grid"]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]5556333
[/TD]
[TD]SCH
[/TD]
[TD]E_EOS
[/TD]
[TD]OH
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Working
[/TD]
[TD]502
[/TD]
[TD]Proposed
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]N
[/TD]
[TD][/TD]
[TD]121212
[/TD]
[TD][/TD]
[TD]15
[/TD]
[TD]589053
[/TD]
[TD]Structure
[/TD]
[TD]123 Main St
[/TD]
[/TR]
[TR="class: cms_table_grid"]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]5556333
[/TD]
[TD]SCH
[/TD]
[TD]E_EOS
[/TD]
[TD]OH
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Working
[/TD]
[TD]451
[/TD]
[TD]Cancelled
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]N
[/TD]
[TD][/TD]
[TD]121212
[/TD]
[TD][/TD]
[TD]15
[/TD]
[TD]589053
[/TD]
[TD]Structure
[/TD]
[TD]123 Main St
[/TD]
[/TR]
[TR="class: cms_table_grid"]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]8889668
[/TD]
[TD]SCH
[/TD]
[TD]E_EOS
[/TD]
[TD]OH
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Working
[/TD]
[TD]453
[/TD]
[TD]Proposed
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]N
[/TD]
[TD][/TD]
[TD]151515
[/TD]
[TD][/TD]
[TD]88
[/TD]
[TD]591028
[/TD]
[TD]Structure
[/TD]
[TD]123 Main St
[/TD]
[/TR]
[TR="class: cms_table_grid"]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]8889668
[/TD]
[TD]SCH
[/TD]
[TD]E_EOS
[/TD]
[TD]OH
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Working
[/TD]
[TD]451
[/TD]
[TD]Cancelled
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]N
[/TD]
[TD][/TD]
[TD]151515
[/TD]
[TD][/TD]
[TD]88
[/TD]
[TD]591028
[/TD]
[TD]Structure
[/TD]
[TD]123 Main St
[/TD]
[/TR]
[TR="class: cms_table_grid"]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]7785970
[/TD]
[TD]SCH
[/TD]
[TD]E_EOS
[/TD]
[TD]OH
[/TD]
[TD]77
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Working
[/TD]
[TD]502
[/TD]
[TD]Installed
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]N
[/TD]
[TD][/TD]
[TD]741852
[/TD]
[TD][/TD]
[TD]895
[/TD]
[TD]589053
[/TD]
[TD]Structure
[/TD]
[TD]123 Main St
[/TD]
[/TR]
[TR="class: cms_table_grid"]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]7785970
[/TD]
[TD]SCH
[/TD]
[TD]E_EOS
[/TD]
[TD]OH
[/TD]
[TD]77
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Working
[/TD]
[TD]451
[/TD]
[TD]Cancelled
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]N
[/TD]
[TD][/TD]
[TD]741852
[/TD]
[TD][/TD]
[TD]895
[/TD]
[TD]589053
[/TD]
[TD]Structure
[/TD]
[TD]123 Main St
[/TD]
[/TR]
[TR="class: cms_table_grid"]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]9996556
[/TD]
[TD]SCH
[/TD]
[TD]E_EOS
[/TD]
[TD]OH
[/TD]
[TD]56
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Working
[/TD]
[TD]503
[/TD]
[TD]Installed
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]N
[/TD]
[TD][/TD]
[TD]456321
[/TD]
[TD][/TD]
[TD]659
[/TD]
[TD]642990
[/TD]
[TD]Structure
[/TD]
[TD]123 Main St
[/TD]
[/TR]
[TR="class: cms_table_grid"]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]9996556
[/TD]
[TD]SCH
[/TD]
[TD]E_EOS
[/TD]
[TD]OH
[/TD]
[TD]56
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Working
[/TD]
[TD]451
[/TD]
[TD]Cancelled
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]N
[/TD]
[TD][/TD]
[TD]456321
[/TD]
[TD][/TD]
[TD]659
[/TD]
[TD]642990
[/TD]
[TD]Structure
[/TD]
[TD]123 Main St
[/TD]
[/TR]
[TR="class: cms_table_grid"]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]9996556
[/TD]
[TD]SCH
[/TD]
[TD]E_EOS
[/TD]
[TD]OH
[/TD]
[TD]56
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Approved
[/TD]
[TD]402
[/TD]
[TD]Cancelled
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD]N
[/TD]
[TD][/TD]
[TD]456321
[/TD]
[TD][/TD]
[TD]659
[/TD]
[TD]642990
[/TD]
[TD]Structure
[/TD]
[TD]123 Main St
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
therefore, would you not just look down the list for "cancelled" jobs and remove them.
the location and serial number are a bit superfluous?
 
Upvote 0
Yuenk,

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


So that we can get it right on the first try:

3. What are the actual worksheet names?

Can you post screenshot of the actual raw data worksheet before, and, after a macro?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot try one of the following:

Excel Jeanie
Download

MrExcel HTML Maker
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


O, you can upload your workbook to Box Net,

sensitive data changed

mark the workbook for sharing

and provide us with a link to your workbook.
 
Upvote 0
SteveO59L said:
therefore, would you not just look down the list for "cancelled" jobs and remove them.
the location and serial number are a bit superfluous?
Click to expand...


There are thousands of lines of data I'm working with. When there's just one canceled it needs to stay. Like I said I cant just filter for canceled. I need to remove the canceled when its associated with a duplicate serial number.
 
Upvote 0
1. PC
2. Excel 2010
3. The table I provided is the format it will be in. There are other misc columns that extend to AI. The name of the worksheet is called "Logica"

This is how I would like it to look.
[TABLE="class: grid, width: 2067"]
<tbody>[TR]
[TD]Proper/Error
[/TD]
[TD]District
[/TD]
[TD]Work Number
[/TD]
[TD]WN Status
[/TD]
[TD]WN Type
[/TD]
[TD]Class
[/TD]
[TD]Area
[/TD]
[TD]Version
[/TD]
[TD]WR hist Rev #
[/TD]
[TD]Official Version?
[/TD]
[TD]Revision Status
[/TD]
[TD]ID
[/TD]
[TD]Location Status
[/TD]
[TD]Dsgn Action
[/TD]
[TD]Dsgn QTY
[/TD]
[TD]AB Action
[/TD]
[TD]AB QTY
[/TD]
[TD]AB Addition
[/TD]
[TD]Dsgn #
[/TD]
[TD]AB #
[/TD]
[TD]Completion
[/TD]
[TD]Asset
[/TD]
[TD]Serial Number
[/TD]
[TD]Facility
[/TD]
[TD]Address
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]1111555
[/TD]
[TD]SCH
[/TD]
[TD]E_EOS
[/TD]
[TD]OH
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Working
[/TD]
[TD]502
[/TD]
[TD]Installed
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]123456
[/TD]
[TD][/TD]
[TD]13
[/TD]
[TD]591229
[/TD]
[TD]Structure
[/TD]
[TD]123 Main St
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]5556333
[/TD]
[TD]SCH
[/TD]
[TD]E_EOS
[/TD]
[TD]OH
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Working
[/TD]
[TD]502
[/TD]
[TD]Proposed
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]N
[/TD]
[TD][/TD]
[TD]121212
[/TD]
[TD][/TD]
[TD]15
[/TD]
[TD]589053
[/TD]
[TD]Structure
[/TD]
[TD]123 Main St
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]8889668
[/TD]
[TD]SCH
[/TD]
[TD]E_EOS
[/TD]
[TD]OH
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Working
[/TD]
[TD]453
[/TD]
[TD]Proposed
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]N
[/TD]
[TD][/TD]
[TD]151515
[/TD]
[TD][/TD]
[TD]88
[/TD]
[TD]591028
[/TD]
[TD]Structure
[/TD]
[TD]123 Main St
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]7785970
[/TD]
[TD]SCH
[/TD]
[TD]E_EOS
[/TD]
[TD]OH
[/TD]
[TD]77
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Working
[/TD]
[TD]502
[/TD]
[TD]Installed
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]N
[/TD]
[TD][/TD]
[TD]741852
[/TD]
[TD][/TD]
[TD]895
[/TD]
[TD]589053
[/TD]
[TD]Structure
[/TD]
[TD]123 Main St
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Proper
[/TD]
[TD]E1
[/TD]
[TD]9996556
[/TD]
[TD]SCH
[/TD]
[TD]E_EOS
[/TD]
[TD]OH
[/TD]
[TD]56
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Working
[/TD]
[TD]503
[/TD]
[TD]Installed
[/TD]
[TD]I
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]N
[/TD]
[TD][/TD]
[TD]456321
[/TD]
[TD][/TD]
[TD]659
[/TD]
[TD]642990
[/TD]
[TD]Structure
[/TD]
[TD]123 Main St
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


There will be other canceled that wont have a duplicate serial number. Those canceled that dont have a duplicate serial number I would like for them to stay
 
Upvote 0
Yuenk,

When you respond to your helper(s), please use their site ID/username/handle.

This will keep thread clutter to a minimum and make the discussion easier to follow.


Sample raw data in worksheet Logica (not all columns are shown for brevity, and, so that the data will dsisplay correctly in the MrExcel display area):


Excel 2007
AMNOTVWXYZAI
1Proper /ErrorLocation StatusDsgn ActionDsgn QTYAB #AssetSerial NumberFacilityAddressZAI
2ProperInstalledI112345613591229Structure123 Main St
3ProperCancelledI112345613591229Structure123 Main St
4ProperProposedI112121215589053Structure123 Main St
5ProperCancelledI112121215589053Structure123 Main St
6ProperProposedI115151588591028Structure123 Main St
7ProperCancelledI115151588591028Structure123 Main St
8ProperInstalledI1741852895589053Structure123 Main St
9ProperCancelledI1741852895589053Structure123 Main St
10ProperInstalledI1456321659642990Structure123 Main St
11ProperCancelledI1456321659642990Structure123 Main St
12ProperCancelledI1456321659642990Structure123 Main St
13
Logica


After the macro:


Excel 2007
AMNOTVWXYZAI
1Proper /ErrorLocation StatusDsgn ActionDsgn QTYAB #AssetSerial NumberFacilityAddressZAI
2ProperInstalledI112345613591229Structure123 Main St
3ProperProposedI112121215589053Structure123 Main St
4ProperProposedI115151588591028Structure123 Main St
5ProperInstalledI1741852895589053Structure123 Main St
6ProperInstalledI1456321659642990Structure123 Main St
7
8
9
10
11
12
13
Logica


And, the results are in a new worksheet Results (not all columns are shown for brevity):


Excel 2007
AMNOTVWXYZAI
1Proper /ErrorLocation StatusDsgn ActionDsgn QTYAB #AssetSerial NumberFacilityAddressZAI
2ProperCancelledI112345613591229Structure123 Main St
3ProperCancelledI112121215589053Structure123 Main St
4ProperCancelledI115151588591028Structure123 Main St
5ProperCancelledI1741852895589053Structure123 Main St
6ProperCancelledI1456321659642990Structure123 Main St
7ProperCancelledI1456321659642990Structure123 Main St
8
Results



See my next reply for the macro code.
 
Upvote 0
Yuenk,

With your raw data in worksheet Logica grouped together in column W by Serial Number:

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Sub DeleteDupesPlus()
' hiker95, 12/10/2014, ME823298
Dim w1 As Worksheet, wr As Worksheet
Dim a As Variant, o As Variant
Dim i As Long, j As Long, c As Long
Dim r As Long, lr As Long, rr As Long, n As Long, nn As Long
Application.ScreenUpdating = False
Set w1 = Sheets("Logica")
With w1
  lr = .Cells(Rows.Count, "W").End(xlUp).Row
  ReDim a(1 To lr, 1 To 35)
  nn = Application.CountIf(.Columns(13), "Cancelled")
  ReDim o(1 To nn, 1 To 35)
  For r = 2 To lr
    n = Application.CountIf(.Columns(23), .Cells(r, 23).Value)
    For rr = r To r + n - 1
      If .Cells(rr, 13) <> "Cancelled" Then
        i = i + 1
        For c = 1 To 35
          a(i, c) = .Cells(rr, c)
        Next c
      ElseIf .Cells(rr, 13) = "Cancelled" Then
        j = j + 1
        For c = 1 To 35
          o(j, c) = .Cells(rr, c)
        Next c
      End If
    Next rr
    r = r + n - 1
  Next r
  .Range("A2:AI" & lr).ClearContents
  .Range("A2").Resize(UBound(a, 1), UBound(a, 2)) = a
End With
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wr = Sheets("Results")
With wr
  .UsedRange.Clear
  .Range("A1:AI1").Value = w1.Range("A1:AI1").Value
  .Range("A2").Resize(UBound(o, 1), UBound(o, 2)).Value = o
  .Columns("A:AI").AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the DeleteDupesPlus macro.
 
Last edited:
Upvote 0
Thanks Hiker for the help. This script removes the canceled but it removes ALL canceled. Unfortunately I need to keep the canceled that aren’t that don’t have a duplicate serial number.
 
Upvote 0
Yuenk,

My macro works correctly based on:

Your original raw data reply #1, and, your results on your reply #5, are identical to the screenshots in my reply #6?????
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Excel VBA Macro steps through ok but when run it causes duplication
Replies
2
Views
4K
Gassy
G
Y
  • Question Question
VBA : Delete Duplicates based on Criteria
Replies
0
Views
341
Yuenk
Y
Y
  • Question Question
Macro almost works. Doesn't delete as desired
Replies
9
Views
561
Andrew Poulsom
Andrew Poulsom

Forum statistics

Threads
1,225,229
Messages
6,183,729
Members
453,185
Latest member
radiantclassy

Share this page

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