Formula to Tag or Delete Duplicate keeping the Rows with paticular Data

Hello to all,

I'm not the best a complex formula's and need some help

I need a formula to mark files as duplicate while comparing multiple columns. I have attached an example.

I have merged 2 different listings of songs. In column "A" I placed a number 1 to indicate the source. Column "B" is the number of the track. Column "C & D" are Title and Artist information. I need to mark the duplicate files as duplicate in Column "E".

If column "B" and "C" are Equal then mark in column "E" duplicate but only mark the file that doesn't have the "1" in Column A. Then I can sort the duplicates and remove.

In some cases I compare 2 columns and other times I compare 3 columns so I need to understand how the formula works so I can change it if I need to.

Thanks for any and all help.

Chuck

There are two parts to this: create what I call a "composite key" and matching the key to the range.

The first part is creating the composite key. In this case you are interested in the combination of track and title. Concatenate them together. I normally put a delimiter between them so they are more human readable. So =B2&":"&C2, yields something like: 11180:I STILL BELIEVE. You can concatenate as many columns as you need to uniquely identify the record.

The formula to identify whether a composite key is unique use the formula: =MATCH(E2,E:E,0)=ROW() - the way this formula works is it uses match to find the first occurrence of the item. Since we're using the whole column, this happens to be the row number. The ROW() part gets the current row number. So if the first time the formula finds the value is on the row with the formula, then it yields TRUE. If the value occurs on a later row, MATCH still finds the first occurrence and the formula yields false.

Filter for TRUE and you have your unique list.

This works great and thanks for the reply, however I need to consider Column A. Column A has the 1 or blank. I need to make false the rows that are duplicate in B & C but only if Column A has a 1. There are numbers in column B that not duplicate numbers and they are being marked True. M goal is to remove the rows that do not have information in B & C but only if the number in B is a duplicate.

Sorry if I am being stupid about this but I really appreciate the help.

Thanks

As I try to understand how these formulas work they remind me of old dos logic commands. I am still not able to accomplish my goal and maybe it's not possible. I have uploaded one more example and created a column with the results I am trying to get by using some type of formula. Thank you for your patience and help.

So the $B$3:$B$14 represents the entire list?

So in my Workbook =IF(A1=1,TRUE,COUNTIF($B$1:$B$44697,B1)=1), Correct!!

Looks like your at a Genus, I believe you've got it.