Finding missing data in column

Closed
tribecablu Posts 1 Registration date Friday August 16, 2013 Status Member Last seen August 16, 2013 - Aug 16, 2013 at 03:27 PM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Aug 17, 2013 at 02:39 AM
Hello,


I have two columns of data column A and column B. There is data in Column B that does not exist in Column A. I am trying to use a formula thru conditional formatting to highlight the cells in column B that are missing column A. I am using formula=IF(COUNTIF(B2:B2217,A2)=0,A2,"") It is highlighting data as I requested however I can figure out no rhyme or reason to it. Some of the highlighted cells are missing from column A but some of them are not. Likewise some of the data not highlighted is not missing at all. So I cannot figure out what I am doing wrong. The data starts at cell A2 and B2. Column B goes from B2 to B2217 and column A goes from A2 to A1431. The data is just a bunch of 5 digit numbers in both columns but columns are in text formatted. Example below

Column A Column B
100480 276471
100482 12104
100615 288009
10159 639658
102503 620649
102572 542415
102596 631164
102657 498996
102664 643365
102688 541777
102701 598405
102718 597651
102732 597682
102848 717240
102916 717202
102923 717172
102930 646359
102954 72066








<config>Windows 7 / Internet Explorer 10.0</config

2 replies

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Aug 17, 2013 at 02:38 AM
try somethilng like this

=IF(COUNTIF($B$2:B19,A2),A2,"")
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Aug 17, 2013 at 02:39 AM
sorry formula worng.
correct formula is
=IF(COUNTIF($B$2:$B$19,A2),A2,"")
0