Forum Discussion

jvass92's avatar
jvass92
Copper Contributor
Aug 19, 2021
Solved

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. ...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Aug 19, 2021

    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.

HansVogelaar's avatar
HansVogelaar
MVP
Aug 19, 2021

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.

LPaxGF11's avatar
LPaxGF11
Copper Contributor
Mar 07, 2024

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
    Mar 08, 2024

    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) )

     

Resources