Forum Discussion

jvass92's avatar
jvass92
Copper Contributor
Aug 19, 2021

Highlight duplicate cell in another sheet

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

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

    • coetzeetania's avatar
      coetzeetania
      Copper Contributor

      Hi there I need some assistance please.

       

      Need to link a number value to a different sheet and worksheet to highlight if they are both on the different sheets

       

       

      This is far far far above my excel knowledge please!

       

      • HansVogelaar's avatar
        HansVogelaar
        MVP

        coetzeetania 

        Let's say you want to highlight values that occur in column C on the Aug and Sept sheets, in rows 2:1000.

        Select C2:C1000 on the Aug sheet. C2 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(XMATCH(C2, Sept!$C$2:$C$1000))

        Click Format...

        Activate the Fill tab.

        Select a color.

        Click OK, then click OK again.

         

        It's similar for the Sept sheet, with the formula =ISNUMBER(XMATCH(C2, Aug!$C$2:$C$1000))

    • LPaxGF11's avatar
      LPaxGF11
      Copper Contributor

      HansVogelaar I'm trying to do something similar.  I have multiple sheets in a workbook where I add a new sheet each month. 

       

      Column headers are the same each month, but the data changes.  I have names (Last, First) in Column A (primary account holder) and sometimes in Column B (their spouse, if applicable) on each sheet. 

       

      I need to highlight names on the current month's sheet that have appeared within the previous 11 sheets (1 year of rolling data).  Each tab name is the Month and Year (Feb 2024, Mar 2024, etc.) the data applies to. 

       

      I can substitute "=ISNUMBER" for "=ISTEXT" in the formula you provided, but I don't know how to have the formula apply to X number of other sheets in the workbook.  

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor

        LPaxGF11 

         

        Current month with Cond. Format. rule applied to column A:

         

        A Power Query based option. With:
        - Monthly sheets formatted as Tables
        - Tables named MMM_YYYY (Nov_2023, Dec_2023, Jan_2024...)
        - A Helper Sheet (to hide) where a Power Query table list names found in previous Months
        - A Defined Name (RangeNamesInPreviousMonths) that refers to the above table

         

        On current month Table, Cond. Format. rule based on formula:

        =ISNUMBER( MATCH($A2, RangeNamesInPreviousMonths, 0) )

         

    • Kcsurk's avatar
      Kcsurk
      Copper Contributor

      HansVogelaar 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!

Resources