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.

2weak2flex

Thank you for your quick reply @Danielle. Your assumption is correct about duplicates within a given row. I attempted the formula on my sheet, but unfortunately, it did not yield the same result you achieved. Will the formula not pick up the spaces between words within the Category column? --- FYI: I updated the column name to just CATEGORY.

Formula used:

=IF(AND(SITE@row = BLDG@row, SITE@row = ROOM@row, SITE@row = CATEGORY@row), 1, 0)