Remove duplicate lines based on another cell field

Hi there!

Im fairly new in Excel, although capable of some formulas and Data tools hacks, but theres a problem that I cant seem to find a solution for either online or with friends.
I have a database of candidates applying to my company stores. The database is already close do 10.000 lines and, as usual, there are some duplicates (people that apply more than once, etc). When Im looking for candidates to a specific store location, I simply filter the database in the City column and pick the candidates.
Ideally, I would want only one line for one specific individual, except when, for example, Jack wants to apply for a store in Washington and also in Denver. Therefore, either I filter Washington or Denver, Jacks name is going to come up.

I already have the Highlight Duplicate Values in the phone number, and if I go with Remove Duplicates Im solving the duplicates problem HOWEVER Im damaging this last example, losing a candidate for Denver just because he already had a line for Washington.

(see databse example attached)

I try to do it manually and order the list from candidates unique ID number, highlight duplicates and manually delete lines where the candidate has selected the same city (a pure duplicate). But with close to 10.000 lines its tiresome.
As the example below of the real database structure (but with false contacts) Im looking for a solution that can automatically delete the latest of the Richard Plumber lines (because it has the same city and its a pure duplicate) BUT keep both of Jack Kent lines, because although his personal information is the same, hes applying for two cities and I have to keep both entries.


I guess Im looking for a way to automate the deletion of a repeated line with the same phone number only if the cell in the City field is the same, and keep it if its different. Is there a way to automate this?

Try:

Formula:

Please Login or Register  to view this content.

Welcome to the forum.

Try this in A9 - does it give what you want?

=UNIQUE(CHOOSECOLS(A1:G7,2,3,4,5,7))

=EXCLUSIVOS(CHOOSECOLS(A1:G7;2;3;4;5;7))

Using all the fields:

Formula:

Please Login or Register  to view this content.

An alternative to the formulas provided here is an option with Power Query

Please Login or Register  to view this content.

Can you not see the difference yourself? You have Sheet3! At the end where you need the closing bracket.

Sorry. Because the formula kept asking me for arguments I didn't know I could end it with a bracket right there.

It works perfectly! Thank you very much indeed!

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

Originally Posted by rickpd

All right!

It worked! Didnt expect a formula for this could be so short

Thank you very much!

You're welcome. Thanks for the rep.