VBA : Delete Duplicates based on Criteria

Y

Yuenk

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

I don't know VBA 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: grid, width: 1747"]
<tbody>[TR]
[TD]
[TABLE="width: 1747"]
<tbody>[TR="class: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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
8
Views
1K
hiker95
H
Y
  • Question Question
Macro almost works. Doesn't delete as desired
Replies
9
Views
561
Andrew Poulsom
Andrew Poulsom

Forum statistics

Threads
1,225,269
Messages
6,183,974
Members
453,200
Latest member
cthun0117

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