Filtering formula that across two columns

[du5t1n]

Hi,

My name is Dustin and I need a little help that could save me hours of work. Firstly I have very basic knowledge of excel and its formulas. I think its a simple formula I need but I just don't know how to ask Excel to do it.

This is what I need - (please see attachment)

Firstly I need excel to show me when the prime item number does not match the supply item number. I use =$A1<>$B1 (Highlight).
Secondly (the bit I cant figure out). When it has highlighted that the prime item number does not match the supply item number I need it to show me all other identical prime numbers that have matching supply item numbers.

i can sort and filter out the number i don't need.


Is it possible??


cheers

dustin

[Special-K]

Use Conditional Formatting

Try
Select A2

Condtional Formatting
New Rule
Use a formula to determine...

=(A2<>B2)
Format as red, say

=(A2=B2)
Format as green, say

Use Format painter (paintbrush icon) to copy to other cells

This will highlight all matching number pairs in green and non matching number pairs in red

[LeoTaxi]

filter on cell color ?

Kind regards
Leo

[du5t1n]

HI Special K,

Thanks for your reply.

That bit I can do, What i need to see is only the prime item number if it has been duplicated. Its the duplication I'm interested in, the normal report can be in excess of 30,000 rows sometimes.

[du5t1n]

Any other thoughts??

[FDibbins]

Not sure if this is what you are after?

CF rule...
=AND(COUNTIF($A$2:$A$14,$A2)>1,$A2<>$B2)

[du5t1n]

Not sure if this is what you are after?

CF rule...
=AND(COUNTIF($A$2:$A$14,$A2)>1,$A2<>$B2)

Hi, thanks for your help, unfortunately it hasnt helped . It has highlighted when prime item and supply is identical. I also need it to highlight when the same prime item number has a different supply item number.

Basically if the prime item number appears more than once. Everytime it has appeares whether it matchs supply or not it needs to be highlighted.

Thanks dustin