Match duplicate data in 2 columns

[Kris]

I am trying to find a faster way to complete my variance reports. Up until
this time I have been manually marking out the duplicates and at times there
could be 30 or more pages of information. Too time consuming.

Recently, I was given the below formula to format a worksheet with 2 columns
of numbers and I need to highlight the duplicate entries and then delete the
highlighted numbers and SUM the remaining numbers per column to find the
total variance. However, I have tried entering the below formula in
conditional formatting but it is not returnng the results I need:

Formula is: =COUNTIF($C$1:$D$150, C1)>1

Worksheet One
Column A Column B
500 562
602 399
599 602
6899 602
235 500
687795 0

However, in Column A number 602 is diplasyed twice in Column B; therefore
highlighting both 602 in column B. I need a formula that will only highlight
the MATCHES in each column. So if Column A has 602 and B has 2 entries of 602
it will highlight 1 of the 602 in Column B. Oh, then of course delete the
duplicates then add each column witht the remaining numerials to find the
total variance.

Thanks

 

[Bob Phillips]

Use a formula of

=COUNTIF(A$1:A1,A1)>1

in your CF.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)