Find missing numbers only

[cciitman]

I am trying to compare two columns of numbers against each other. Problem is they are not necessarily going to be lined up even after being sorted and neither is a master list which to compare against. I need to have a third and forth column created to show numbers that don't have a match in the other column. This data will be imported so the numbers of rows will grow and shrink each time. The data will be coming from two different programs to find missing information between the two. Any help or ideas are greatly appreciated.
Thanks

 

[DaleWatson123321]

For starters, can you please specify: Access or Excel ???

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[cciitman]

I would prefer to do it in Excel but am open to using Access to do it.

 

[Dreamboat]

Use VLOOKUP. Either your numbers will be found or they won't.

For specific instructions/example on VLOOKUP, you can download MyVlookup (free) at:

http://www.thewordexpert.com/tipwarez.htm

http://www.TheWordExpert.com

 

[LeKirk]

Use MATCH. Beside your first column (say B1:B50) of figures add the formula in A1 - MATCH(lookup_value,lookup_array,match_type) where lookup_value points to the first number in the master column (B1:B50), lookup_array points to the range of figures in your second column (say C1:C50) and match type 0 is used to find the first value that is exactly equal to lookup_value. Lookup_array can be in any order but it is best if sorted in numerical order.

Copy the formula down the whole of the column A1:A50 and you should see, where there is a match between the columns, a number. This number is the row number counting from the top where the match will be found in your lookup_array. If there is no match, the formula returns #N/A