Complex Conditional Formatting

[TrustMyUnit]

Hi all

First time poster, great to be here! I've always been a big fan of Excel

I'm wondering if you could help me with a requirement I have please. We have two columns, one is our part number, and another is a supplier's part number:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Our Part Number[/TD]
[TD]Supplier's Part Number[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]FG-677[/TD]
[/TR]
[TR]
[TD]67890[/TD]
[TD]FG-688[/TD]
[/TR]
[TR]
[TD]24680[/TD]
[TD]FG-699[/TD]
[/TR]
[TR]
[TD]13579[/TD]
[TD]FG-699[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]FG-677[/TD]
[/TR]
</tbody>[/TABLE]

The situation is that duplicate Supplier Part Number's in column B is fine as long as column A is the same for each duplicate. In this example, I would want the row with Our Part Number as 13579 to be highlighted, as it's a duplicated Supplier Part Number, but the Our Part Number on those rows are different.

Is this even possible please?

Thank you so much in advance!
Matt

 

[mole999]

one way, assuming A and B cols above. In C2
=TRIM(A2&B2), drag down then conditional format C for duplicates, you could then eliminate any known duplicates. Then sort by supplier part number. I'm sure there is a way to highlight more than one match, but this will get you close to start with

 

[TrustMyUnit]

one way, assuming A and B cols above. In C2
=TRIM(A2&B2), drag down then conditional format C for duplicates, you could then eliminate any known duplicates. Then sort by supplier part number. I'm sure there is a way to highlight more than one match, but this will get you close to start with

Thanks mole! I really appreciate your answer, and sorry for the delay. We have cracked this, so I wanted to post the answer here for anyone that comes across this thread in future.

We modified the table to be this: https://calc.disroot.org/8xeqi0nxhp8o

Essentially, added a column which conjoined Our Part Number and Supplier Part Number. Made another column which had TRUE/FALSE for whether the Supplier Part Number was duplicated, and yet another column to show TRUE/FALSE for whether the conjoined number was duplicated. If the first column says true, but the second says false, it's shown as YES in the 'Is this a problem?' column.


Thanks again for your help, and I hope this helps someone else!

 

[FormR]

Hi, just a another option..

You could try this to return the Yes/No in the 'Is this a problem' column.

Excel 2013/2016
	A	B	C
1	Our Part Number	Supplier's Part Number	Is problem?
2	12345	FG-677	No
3	67890	FG-688	No
4	24680	FG-699	Yes
5	13579	FG-699	Yes
6	12345	FG-677	No
Sheet1

Cell Formulas
Range	Formula
C2	=IF(COUNTIFS(B:B,B2)-COUNTIFS(A:A,A2),"Yes","No")

Or you could use this formula directly in conditional formatting with the "use a formula" method:

=COUNTIFS($B:$B,$B2)-COUNTIFS($A:$A,$A2)