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

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" wrote in
message ...

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

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 FormatConditional Formatting
Formula is: =COUNTIF(B$1:B$40,A1)=0
Click the Format button
Select the style(s) desired
OK out

Biff

"petevang" wrote in
message ...

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

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.

--

petevang Wrote:
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
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=530786