Matching two sets of numbers with duplicates

[austenr]

I am trying to match two sets of numbers. One of the sets has duplicates in it and I cannot remove them before matching. Does anyone know how to get around it? My formula is as follows:


=IF(COUNTIF($A$2:$A$500,B2)=0,B2"")

And I need to display in column C numbers that do not match.

Thanks

[Bob Phillips]

I am trying to match two sets of numbers. One of the sets has duplicates in it and I cannot remove them before matching. Does anyone know how to get around it? My formula is as follows:


=IF(COUNTIF($A$2:$A$500,B2)=0,B2"")

And I need to display in column C numbers that do not match.

Thanks

Missing a comma



=IF(COUNTIF($A$2:$A$500,B2)=0,B2,"")

or alternatively


=IF(NOT(COUNTIF($A$2:$A$500,B2)),B2,"")

[austenr]

Thanks. One more thing, I have a number in text format as 01010. When I cobvert it to a number, how can I keep it from suppressing the leading zero?

[Bob Phillips]

Thanks. One more thing, I have a number in text format as 01010. When I cobvert it to a number, how can I keep it from suppressing the leading zero?

You can't keep it from supressing that zero as a 'number' doesn't have leading zeroes, but you can 'show' it with that leading zero by formatting the cell as 00000

[austenr]

It was a zip code, I formatted it as Special and then as Zip Code to get it to work. Thought that there might have been an easier way.