Duplicates Highlight with condition from second column

akampianakis

New Member
Joined
Apr 8, 2022
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
Hello world,
so I have the sheet as seen in the attachment (I have included a picture)
I have 2 columns, A for machines, B for products. I want to highlight those products that are created from both machines , so something like if duplicate and column A in the range of the duplicates contains both A1 and A2, highlight the range/cell, or something that identifies that.
Is that possible?
Thanks!
Example Pic.png
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Ignore - Not quite write , that was duplicates and not for A1 or B1
i'll think
 
Upvote 0
=COUNTIFS($A$2:$A$100,"A1",$B$2:$B$100,$B2)+COUNTIFS($A$2:$A$100,"A2",$B$2:$B$100,$B2)>1

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
A2:B100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=COUNTIFS($A$2:$A$100,"A1",$B$2:$B$100,$B2)+COUNTIFS($A$2:$A$100,"A2",$B$2:$B$100,$B2)>1

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

Book1
AB
2a1fred
3a1john
4a1jill
5a1gill
6a2jill
7a2sally
8a2fred
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:B8Expression=COUNTIFS($A$2:$A$8,"A1",$B$2:$B$8,$B2)+COUNTIFS($A$2:$A$8,"A2",$B$2:$B$8,$B2)>1textNO
 
Upvote 0

Forum statistics

Threads
1,223,881
Messages
6,175,161
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top