Formula to determine Duplicate items copied from one sheet to another

mbsamuel6

@Paul Newcome

Thanks for the response. What I was trying to accomplish is I have three other sheets that has their rows copied to the one sheet I had screen shot above.

When those rows are copied over there are some duplicates. Was trying to find a way to remove those duplicates automatically. The rank column ranks the number of times a duplicate appears. If a row is a duplicate, the first time it appears should be "1" in the rank column. The next time it appears it should be a "2," meaning its a duplicate. If it appears a third time, it would be a rank of "3" and so on. So if its a rank "3", that means a row with the same column name "Name of Project" appeared two other times and that "3" is the third time it appeared.

Then in the remove column, every a row that has a rank of more then 1 (the other duplicates) would be removed with an automation by its rows being copied over to another sheet.

That was my approach. I am open to do your approach above, just not sure where to start as I got lost in this thread

Paul Newcome

@mbsamuel6 Are you using an auto-number or created date column in any of the three source sheets?

mbsamuel6

Hey @Paul Newcome I am using a created date column on the source sheets. I made an auto number column on the destination sheet called "job#"

Paul Newcome

@mbsamuel6 In that case I would suggest an IF/COUNTIFS.

=IF([Created Date]@row <> MIN(COLLECT([Created Date]:[Created Date], [Name of Marketing Project]:[Name of Marketing Project], [Name of Marketing Project]@row)), 1)

Basically we pull together all of the Created dates that match the [Name of Marketing Project] and say that if the Created date "@row" is not the first one then check the box.

mbsamuel6

@Paul Newcome

That worked perfectly and much less complicated then my original approach. Thank you very much!!!

Paul Newcome

@mbsamuel6 Happy to help. 👍️