The formula you're reaching for is straightforward, and the frustration you're feeling comes from a common blind spot in how spreadsheets evaluate cell ranges. You want to flag duplicate coordinate pairs in columns E and F, treating them as a single unit. The solution is a `COUNTIFS` function that checks both columns simultaneously, not a `COUNTIF` that only looks at one. That's the root of the inconsistency you're seeing.
When you duplicate rows 2-10 into rows 18-26 and only some are flagged, the issue is almost certainly that your formula is comparing individual cells rather than the pair. A formula like `=COUNTIF(E:E, E2)>1` will count how many times the value in E2 appears in column E, but it ignores column F entirely. If column E has repeated values that happen to pair with different F values, you get false positives. Conversely, a unique pair where E repeats but F is different will not be flagged when it should be. The fix is `=COUNTIFS(E:E, E2, F:F, F2)>1`. This function checks both columns for an exact match of the combined pair, then returns "Duplicate" when the count exceeds one.
This is a small syntax change, but it reflects a larger truth about working with data in spreadsheets. Legacy tools treat cells as isolated containers, but your data lives in relationships. A coordinate pair is a single logical unit, two numbers that together define a location, a transaction, or a record. When you force a spreadsheet to see them as separate, you fight its design. The AI-native approach is to let the tool understand the connection, not to contort your data into a shape the formula expects. You're already thinking in pairs; now your formula can too.
Apply `COUNTIFS` to the first row of your data, drag it down, and watch column G light up consistently. Test it by intentionally duplicating a pair, E and F both matching a previous row, and you'll see "Duplicate" appear every time. No more partial flags, no more second-guessing. That reliability is the baseline for any tool that claims to handle real-world data. Once you have it, you can stop debugging formulas and start asking the next question: what else should your spreadsheet be able to see for you?