Remove duplicates based on multiple column values

floggingmolly

Board Regular
Joined
Sep 14, 2019
Messages
85
I have a spreadsheet with 12 columns. I need to remove duplicates but its based on the values in several columns.
Column C has a location number, example 110.
Column E has an employee #, example V901
Column I has a status, either Y or N

The problem is each employee can be working out of more than one location, Like Employee V901 can be working from location 110 and 112 and can be listed more than once at each location.
If employee V901 is listed at location 110 two times, and the status in one is Y and one is N, then I need to remove the N.
If employee V901 is listed at location 110 two times, and the status for both is N, then I need to delete one of the N.

So I need to check for a duplicate by employee # and by location, if both match, then I need to delete one of those whether the status is both N or one N and one Y.
The employee can be listed at two locations, that's why I need to check both the employee number and the location number.

Not sure if this is possible or if it makes sense. Any assistance would be greatly appreciated.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

mehidy1437

Active Member
Joined
Nov 15, 2019
Messages
270
Office Version
  1. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
What will be the situation, if status in both is Y? Will it be okay for you to add an additional helper column to do the task?
 

floggingmolly

Board Regular
Joined
Sep 14, 2019
Messages
85
If both are Y, that complicates it even more. LOL. Then I need to check column K to see which of their contract numbers is the most current. I can use helper columns if needed. I've tried everything but can't get it to come out right.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,033
Messages
5,639,655
Members
417,102
Latest member
bcselect

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