Compare columns and highlight unique values

[Scouse]

Hi,


This is my first posting. I have two columns of data which I need to compare in order to find those values which do not appear in either column. Once found I intend using conditional formating to highlight those values which are not duplicated in either column.


For example


Column A Column B


42.680 741.980

640.830 138.040

741.980 757.280

745.380 640.830

-70.200 -70.200

14.850 138.040

138.040 42.680

690.980

1,440.070


I have tried the following formula which compares a value in column A against the whole of column B to see if its unique or not, but I need a formula that can locate a unique value whether it be in column A or B.

=IF(ISNUMBER(MATCH($A2,$B:$B,0)),"Duplicated","Unique")

Thanks

 

[keymaster]

You can use countif formula to find if the value is repeated anywhere in the range.


Assuming the values are in the range A1:B10, you can write countif like this


=if(COUNTIF($A$1:$B$10,A1)>1,"Duplicate","Unique")


You can use this inside CF to highlight unique values.


Also, see this example, you can find the solution: http://chandoo.org/wp/2008/03/13/want-to-be-an-excel-conditional-formatting-rock-star-read-this/

 

[Scouse]

Thanks Chandoo for your help. I have a problem though which I did not explain on my original posting, which is that the two columns of data are not next to each other, as each set of values has other columns before and after, as they are part of a much larger financial report. The data also is duplicated several times, but as long as the data is formatted the same each time the reports are imported I will be OK.


As follows, I just need to check cols A and D

Column A Column B Column C Column D Column E Column F Column G


42.680 ST GBP 741.980 ST 741.980 GBP

640.830 ST GBP 138.040 ST 138.040 GBP

741.980 ST GBP 757.280 ST 757.280 GBP

745.380 ST GBP 640.830 ST 640.830 GBP

-70.200 ST GBP -70.200 ST -70.200 GBP

1,440.070ST GBP

 

[Hui]

Scouse

You can use conditional formatting in Excel 2007 to easily do what you want

Select the data areas or the entire columns A:G

Go to conditional formating

New Rule

Format Only Unique or duplicate values

Format all Unique Values

Set your Formatting

Ok to apply


I would select the whole columns as the Text values ST and GBP will then not be highlighted as they are not unique

 

[Scouse]

I have to use Excel 2003 version standard edition. Sorry should have said what version I am using.

 

[SAMBASIVA RAO]

=if(sumproduct((A1:G6=A1)*1)>1,"Duplicate","Unique")

 

[Hui]

and to use Sambasiva's idea in a conditional format use


=SUMPRODUCT(($A$1:$G$6=A1)*1)=1

 

[Scouse]

Just wanted to say big thank everybody for all your help.