Need to highlight duplicates in two columns?
I need to highlight any duplicates in two separate columns.
Such as below screen shot: Need all # highlighted that are in Unit # column and the Asset on Rent Data. Its easy in excel, but can't figure it out on Smart Sheets.
Answers
-
To mimic the Highlight Duplicates Functionality in excel I use a Checkbox Column Formula & a Cond. Formatting Rule (2nd Image). In this case I just used 2 checkboxes & 2 format rules
Unit # Dupe formula:=IF(COUNTIF([Asset on Rent Data]:[Asset on Rent Data], [Unit #]@row) > 0, 1, 0)
This looks at the entire Asset on Rent Data column and checks the box if the value in Unit # shows up in that column.
Asset Dupe formula:
=IF(COUNTIF([Unit #]:[Unit #], [Asset on Rent Data]@row) > 0, 1, 0)
Just inverse the range & cell we are checking against here. Then 2 rules, if Unit# Dupe is checked, make Unit # column red & vice versa for Asset Dupe.
Larry Cummings
https://primeconsulting.com/
Principal Consultant | Prime Consulting Group
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 431 Global Discussions
- 152 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 74 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!