Duplicate Entry Formula for Data Across Multiple Columns
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
Answers

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 falsepositives 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.

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)
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives