Comparing two columns

Posted by Pat on January 16, 2002 7:53 AM

I have two columns of text (one master and one partial)and I want to get excel to compare one with the other and provide a list of what is mising from the master..

Glenn

Posted by Aladin Akyurek on January 16, 2002 8:03 AM

Taking A as "master" and B as partial, both starting with data in row 2,

in C2 enter: =IF(COUNTIF(A2:A100,B2),"",B2)

and copy this down.

Aladin

Posted by Mark W. on January 16, 2002 8:17 AM

If column A contains your "partial" list beginning
at cell A2 and your "master" list is in cells
C2:C10, enter =MATCH(A2,$C$2:$C$10,0) into B2 and
copy down the length of your partial list. Partial
list entries with a corresponding #N/A in column
B are missing from your Master.

Posted by Russell Hauf on January 16, 2002 2:48 PM

You could do a quick temporary array formula as well:

=SUM(IF(COUNTIF(MasterRange,PartialRange),0,1))

(example: =SUM(IF(COUNTIF($A$11:$A$16,B11:B16),0,1)) )

(remember to enter array formulas by holding down CTRL+SHIFT while hitting ENTER).

HTH,

Russell

Posted by Russell Hauf on January 17, 2002 9:35 AM

Oops - I was thinking you wanted a count - sorry (nt)