Checkbox dependent on two rows with identical data in the same column

Jared Ross

Posted this last week, but it seems to have died on the vine. I Regularly refresh different databases throughout the year by making a copy of the old one and importing the new data into it. This results in a ton of redundant data, and I'm looking to quickly cull it.

After sorting my grid by IDNumber, how can I get the "To Delete" column to automatically check in rows that have identical "IDNumber" ("duplicate ID" would be checked via formula "=IF(COUNTIF(IDNumber:IDNumber, IDNumber@row) > 1, 1)" and identical "Location" entry values, but "NewEntry" is NOT checked?? (rows 2 & 11).

Similarly, how could I get "Location Change" to automatically check in rows that have duplicate "IDNumber" values ("Duplicate ID" would be checked) and "NewEntry" is checked (this is manually checked after I import all new data), but the "Location" values are different? (rows 3 & 8).

Jeff Reisman

@Jared Ross

First, add a helper column to create a unique value of IDNumber and Location, called NumLoc:

=IDNumber@row +"-"+Location@row

Result: 112-Los Angeles, 121-Tennessee, etc.

Try this in the "To Delete" column:

=IF(AND(COUNTIF(NumLoc:NumLoc, NumLoc@row) > 1, NewEntry@row <> 1), 1)

English: If the count of rows with this same IDNumber and Location (NumLoc) is greater than 1, AND if the NewEntry checkbox on this row isn't checked, then check the "To Delete" checkbox.

For the Location Change column:

=IF(AND(COUNTIF(IDNumber:IDNumber, IDNumber@row) > 1, COUNTIF(NumLoc:NumLoc, NumLoc@row) <= 1, NewEntry@row = 1), 1)

English: If there are rows with duplicate IDNumber, AND not duplicate rows with the same IDNumber / Location combination, AND if the NewEntry checkbox is checked, then check the Location Change checkbox.

Julie Fortney

Hi Jared,

I think you can solve this with a helper column:

Create a new column called "ID Number - Location." Enter this column formula: =JOIN(IDNumber@row:Location@row, " - ")

Once that is added, try these formulas for "To Delete" and "New Location":

To Delete =IF([New Entry]@row = 0, 0, IF(COUNTIF([ID Number - Location]:[ID Number - Location], [ID Number - Location]@row) > 1, 1))

New Location =IF([New Entry]@row = 0, 0, IF(COUNTIF([ID Number - Location]:[ID Number - Location], [ID Number - Location]@row) = 1, 1))

Jeff Reisman

@Julie Fortney

Awesome - two slightly different approaches to the same problem, which both solve it, submitted at the same time!