Forum Discussion

iMajeed's avatar
iMajeed
Copper Contributor
Nov 16, 2023
Solved

Duplicates in one column with different values in another column

I have excel sheet that have column with duplicate in one column and have different values in another column TRUE / FALSE in ( T_F ) column, i need from excel highlight entire row if the value contai...
excel
Formulas and Functions
office 365
  • Lorenzo's avatar
    Lorenzo
    Nov 17, 2023

    iMajeed 

    OK, so a single Cond. Format. rule is enough:

    1. Deleted all existing Conditional Formatting rules on your Table
    2. Select all Columns + Rows of your Table
    3. Go to Home (tab) > Conditional Formatting > New Rule... > Use a formula to determine which cells to format
    4. Under Format values where this formula is true enter (adjust $A$2874 as necessary):

     

    =AND(COUNTIF($A$2:$A$2874, $A2) > 1, NOT($L2))​

     

    • Click on Format... at the bottom right > Choose how you want to format...

     

NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Nov 17, 2023

iMajeed 

To highlight entire rows where the value in the "T_F" column contains only TRUE and FALSE, you can use conditional formatting with a formula. Follow these steps:

  1. Select the entire range of your data.
  2. Go to the "Home" tab in the Excel ribbon.
  3. Click on "Conditional Formatting" in the toolbar.
  4. Choose "New Rule" from the dropdown menu.
  5. In the "New Formatting Rule" dialog, select "Use a formula to determine which cells to format."
  6. Enter the following formula, assuming your data starts from the first row (adjust the range as needed):

=AND(COUNTIF($A:$A,$A1)>1, COUNTIF($B:$B,"TRUE")>0, COUNTIF($B:$B,"FALSE")>0)

This formula checks if there are duplicates in column A ($A:$A) and if there is at least one "TRUE" and one "FALSE" in column B ($B:$B).

  1. Click the "Format" button to set the formatting options for the highlighted rows.
  2. Choose the formatting style you prefer (e.g., a fill color) and click "OK."
  3. Click "OK" again in the "New Formatting Rule" dialog.

This should highlight entire rows where the "T_F" column contains both TRUE and FALSE values.

Keep in mind that this formula assumes your data starts from the first row, and you may need to adjust the column letters and row numbers based on the actual location of your data.

The text was created with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

 

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

Resources