PDA

View Full Version : [SOLVED] Matching two sets of numbers with duplicates



austenr
09-28-2005, 09:05 AM
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
09-28-2005, 09:41 AM
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
09-28-2005, 09:52 AM
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
09-28-2005, 10:32 AM
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
09-28-2005, 10:55 AM
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.