Duplication detection after initial row of information

Char Norman

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)

Paul Newcome

Try this instead:

=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)

Char Norman

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?

Paul Newcome

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)

Char Norman

Thanks Paul.

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

Paul Newcome

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

Char Norman

Here you go

Paul Newcome

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.

Char Norman

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.

Thanks for your help!

Paul Newcome

You need to put it in a column that is not being referenced by the formula. Did you insert the auto-number column?