Duplicate Entry Formula for Data Across Multiple Columns

2weak2flex

Hi, I'm hoping someone can share the correct formula to indicate a Duplicate entry where all multiple columns all have to be 'true' for the indicator to show a duplicate row/entry.

Example: To trigger the "DUPLICATE ENTRY' status, I want to make sure SITE, BLDG, ROOM, and DESCRIPTION/CATEGORY all have the same value, not just one of them. I used this formula to get the duplicate flag to work on just the ROOM column:

=IFERROR(IF(LEN(ROOM1) = 0, "", IF(COUNTIFS(ROOM:ROOM, ROOM1) = 1, 0, 1)), 0

Danielle Arteaga

It's a little unclear what you want. Are you looking for duplicates within a column or across a row?

I'm going to assume you mean within a row based on the formula you shared. If that's the case, I would not recommend using the LEN to locate your duplicates, because that will yield false-positives when the values in your cells are different but coincidentally have the same length. Instead, you can use a formula that directly compares the literal values across the SITE, BLDG, ROOM and DESCRIPTION/CATEGORY columns

I used this formula in the first three rows of the screen cap below:

=IF(AND(SITE@row = BLDG@row, SITE@row = ROOM@row, SITE@row = [DESCRIPTION/CATEGORY]@row), 1, 0)

On the fifth row, I used this formula:

=IF(AND(LEN(SITE@row) = LEN(BLDG@row), LEN(SITE@row) = LEN(ROOM@row), LEN(SITE@row) = LEN([DESCRIPTION/CATEGORY]@row)), 1, 0)

Note that it threw the flag for duplicates, because each word in each of the cells is 4 characters in length. But, the words themselves are unique, so there is no duplicate on the row.

Hopefully this helps.