# Duplication detection after initial row of information

✭✭✭✭

I have reviewed some of the other solutions for duplicate detetion but am finding those formulas are not tailored to my situation. I need to keep the initial row and hide the duplicates.

In the formula I'm using, I need the church or organization row with the oldest date to remain unhidden, while the remaining rows with the same church or organization information are hidden. My formula may not be working because >1 is inaccurate logic because I need a logic the identifies the oldest date only?

=IF(AND([Church or Organization]@row <> "", [Church or Organization]:[Church or Organization], @cell = [Church or Organization]@row, [Date Form Completed]:[Date Form Completed], @cell = [Date Form Completed]@row)) > 1)

• ✭✭✭✭✭✭

=IF(COUNTIFS([Church or Organization]:[Church or Organization], @cell = [Church or Organization]@row, [Date Form Completed]:[Date Form Completed], @cell < [Date Form Completed]@row) > 0, 1)

• ✭✭✭✭

Hi Paul,

I created a column titled "Identify 1st entry of church/org" and placed this formula in that column converting the column to the cell formula. That worked great and after I created a filter to remove all the blank cells in the "Identify 1st entry of church/org" I was able to remove most of the duplicates.

Issue I ran into is for the rows where the date form completed and the church or organization were on the same. See below. How can I add to the formula to pull the first 'row' with the same date & church/org?

• ✭✭✭✭✭✭

You will need to insert an auto-number type column (called "Auto" in this example) that has no special formatting and then adjust your formula this way:

=IF(COUNTIFS([Church or Organization]:[Church or Organization], @cell = [Church or Organization]@row, Auto:Auto, @cell < Auto@row) > 0, 1)

• ✭✭✭✭

Thanks Paul.

I created the column and entered the formula. It comes up with the lovely #UNPARSEABLE

• ✭✭✭✭✭✭

Can you show the formula open in the sheet as if you are about to edit it?

• ✭✭✭✭

Here you go

• ✭✭✭✭✭✭

I don't see either of the referenced columns in the screenshot. It is also hard to tell if there is any color coding to any of the range / column references because of the highlighting. I would start with double checking the column names are spelled in the formula exactly how you have them in the sheet.

• ✭✭✭✭

I checked the column/range references and I did miss verbiage. I corrected that and now I am receiving #CIRCULAR REFERENCE. Pic below has the colors identifying the columns/ranges.