Auto-search/highlight

Walt L

Hello,

I'm wondering if there is ANY kind of way that if, when data is entered, there could be some kind of "auto highlight" or notification that there is duplicate data on another line.

Here's my scenario:

I have multiple users across a few departments. The data is tied to a job # (stays the same) but the job could be rescheduled 1x, multiple times, to different dates and often causes confusion on the members that need to reschedule it. What is the correct date? Which entry is the latest one? etc.

So, we've asked folks to do the strikethrough of the entries that are no longer applicable but that often times does NOT happen.

My ask is if there's a way that if, when someone enters a new entry for a job to be scheduled, the old entry(entries) could be called out (highlighted in a designed color, etc.), be made visible to the team member entering the information, anything like that?

Thanks

Ryan Sides

Hi Walt, you're looking to create what we call a "helper" column. It's just another column on your sheet that helps. Right click and insert a column, call it Duplicate. In the first row add a formula that checks for any Duplicates of your Job #, like this...

=COUNTIFS([Job #]:[Job #], =[Job #]@row)

Make that a column formula.

Then you can create a Conditional Format (look in the icon list above your columns) and set it so it highlights a row when the Duplicate is greater than 1.

Ryan Sides

Hi Walt, you're looking to create what we call a "helper" column. It's just another column on your sheet that helps. Right click and insert a column, call it Duplicate. In the first row add a formula that checks for any Duplicates of your Job #, like this...

=COUNTIFS([Job #]:[Job #], =[Job #]@row)

Make that a column formula.

Then you can create a Conditional Format (look in the icon list above your columns) and set it so it highlights a row when the Duplicate is greater than 1.

Walt L

Thanks. I will try that.

1 question though - what type of column would my "Duplicate" column be?

Ryan Sides

Text would be fine since the formula is counting occurrences.

Walt L

Thank you

Ryan Sides

My pleasure! Have a good one