comparing two lists in excel

[petevang]

I have two columns of data (A and B) whos cells consist of only numbers in order from 1 to 50. Columns A's ranges from 1 to 50, and every whole number is present. Column B is missing a few numbers. I want to see what numbers are not in column B but are in column A. Is their a formula that could highlight the missing cells?

[Bryan Hessey]

in cell C1 put

=VLOOKUP(A1,B$1:B$99,1,FALSE)

and formula-copy this for the number of rows.

'B$1:B$99' should be the extent of the data in column B

The #N/A rows are the items you are looking for.

--

I have two columns of data (A and B) whos cells consist of only numbers in order from 1 to 50. Columns A's ranges from 1 to 50, and every whole number is present. Column B is missing a few numbers. I want to see what numbers are not in column B but are in column A. Is their a formula that could highlight the missing cells?

[George Nicholson]

In a third column you could use VLookup to search for an exact match. If it
returns #N/A then the value is not in the list:
=IF(ISERROR(VLookup(A2,B$2:B$50,1,False)),"Missing","") (copied down the
length of values in A)

Paraphrased: If the exact value of A is not present in the range B2:B50
(i.e., if Vlookup returns the #N/A error), return "Missing", otherwise
return "".

Of course, adjust the ranges to your actual data layout.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"petevang" <[email protected]> wrote in
message news:[email protected]...
>
> I have two columns of data (A and B) whos cells consist of only numbers
> in order from 1 to 50. Columns A's ranges from 1 to 50, and every
> whole number is present. Column B is missing a few numbers. I want to
> see what numbers are not in column B but are in column A. Is their a
> formula that could highlight the missing cells?
>
>
> --
> petevang
> ------------------------------------------------------------------------
> petevang's Profile:
> http://www.excelforum.com/member.php...o&userid=25034
> View this thread: http://www.excelforum.com/showthread...hreadid=530786
>

[Biff]

Hi!

Try this:

Assume the numbers in column A are in the range A1:A50. The numbers in
column B are in the range B1:B40.

Select the range, A1:A50.
Goto Format>Conditional Formatting
Formula is: =COUNTIF(B$1:B$40,A1)=0
Click the Format button
Select the style(s) desired
OK out

Biff

"petevang" <[email protected]> wrote in
message news:[email protected]...
>
> I have two columns of data (A and B) whos cells consist of only numbers
> in order from 1 to 50. Columns A's ranges from 1 to 50, and every
> whole number is present. Column B is missing a few numbers. I want to
> see what numbers are not in column B but are in column A. Is their a
> formula that could highlight the missing cells?
>
>
> --
> petevang
> ------------------------------------------------------------------------
> petevang's Profile:
> http://www.excelforum.com/member.php...o&userid=25034
> View this thread: http://www.excelforum.com/showthread...hreadid=530786
>