Forum Discussion
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 contains TRUE and FALSE only , i have attached the file . Thanks,
OK, so a single Cond. Format. rule is enough:
- Deleted all existing Conditional Formatting rules on your Table
- Select all Columns + Rows of your Table
- Go to Home (tab) > Conditional Formatting > New Rule... > Use a formula to determine which cells to format
- 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...
16 Replies
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:
- Select the entire range of your data.
- Go to the "Home" tab in the Excel ribbon.
- Click on "Conditional Formatting" in the toolbar.
- Choose "New Rule" from the dropdown menu.
- In the "New Formatting Rule" dialog, select "Use a formula to determine which cells to format."
- 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).
- Click the "Format" button to set the formatting options for the highlighted rows.
- Choose the formatting style you prefer (e.g., a fill color) and click "OK."
- 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.
Anyone help me with this matter please, because I’m dealing with thousands of rows.
Thank you
Hi iMajeed
Will be glad to help but...
#1 i have attached the file. There's no file attached, a picture only
#2 i need from excel highlight entire row if the value contains TRUE and FALSE only. How can a formula in column T_F returns TRUE and FALSE? It's either TRUE or FALSE. Please clarify the condition (TRUE or FALSE in T_F) should be used to highlight the corresponding row
ThanksLorenzo for your reply, the ( techcommunity ) here not support the file (.xlsx) but i raised the excel file on onedrive via this link Book3.xlsx , let me clarify my inquiry more, for Example i have unit with number 23********** repeat 3 times ( Duplicates ) but in column ( L ) have value True also false , how can make the excel highlight the row for unit 23********** repeated 3 times but only if have value True also false in column ( L ) .
Thanks
