Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Highlight duplicate cell in another sheet

Copper Contributor
Hello,
I need some assistance if possible please.

Data in sheet 1, col B.
Data in sheet 2, col B.

If data in Sheet 2 Col B is the same as Sheet 1 Col B, highlight the duplicate in sheet 2.

Thanks
13 Replies
best response confirmed by jvass92 (Copper Contributor)
Solution

@jvass92 

Select column B on Sheet2. Cell B1 should be the active cell in the selection.

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

 

=ISNUMBER(MATCH(B1, 'Sheet 1'!B:B, 0))

 

Substitute the real name of Sheet 1.

Click Format...

Activate the Fill tab.

Select a highlight color.

Click OK, then click OK again.

Thanks so much.

@Hans Vogelaar This worked perfect, even though numerous other sites said it was impossible. THANK YOU!

@jvass92 

 

Thanks! I too have been searching for a solution to this. Everybody says it can't be done.

@Hans Vogelaar is there a way to highlight only if it matches two columns?

 

Currently working on an excel tool for work (logistics), and am trying to get our internal list of preferred origin zip & destination zip to auto-highlight on bid data so it is easy for us to identify our focus. 

 

I have two conditional formatting rules set up, but I would like to combine them into one rule and only highlight if both origin & destination match our master file.

 

Ideally, I'd be able to highlight any zip codes that are within a given radius of our internal data. Let me know if you have any work arounds or ideas!

@Kcsurk 

How do you determine the distance?

For the sake of ease, I'd set both origin & destination radius at 50 miles each

@Kcsurk 

Do you have that information in your workbook? If so, could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

@Hans Vogelaar I've linked the document. Was discussing with my boss and he thinks a VLookUp between two separate workbooks would work, but he's unsure how to set that up.

 

Open to any suggestions you have & appreciate the assistance!

 

Workbook 

@Kcsurk 

I cannot access that file. You'll have to share it.

Best way for me to share it with you?

@Kcsurk 

Click on my user picture and send me a private message. You should be able to attach the workbook there.

1 best response

Accepted Solutions
best response confirmed by jvass92 (Copper Contributor)
Solution

@jvass92 

Select column B on Sheet2. Cell B1 should be the active cell in the selection.

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

 

=ISNUMBER(MATCH(B1, 'Sheet 1'!B:B, 0))

 

Substitute the real name of Sheet 1.

Click Format...

Activate the Fill tab.

Select a highlight color.

Click OK, then click OK again.

View solution in original post