Excel

C

Chris

If I have a master list of 20 part numbers in one Excel column or worksheet
how can I compare each part numbers in the master column against actual
values/part numbers in another column or worksheet, and highlight in red the
missing parts in a third column? Is there a wizzard to help me or will I have
to write a complicated macro or loop?
 
B

Biff

Hi!

Column A1:A19 = secondary list
Column B1:B19 = master list

In C1 enter this formula with the key combo of
CTRL,SHIFT,ENTER:

=IF(ISERROR(SMALL(IF(COUNTIF(A$1:A$19,B$1:B$19)=0,ROW
($1:$19)),ROW(1:1))),"",INDEX(B$1:B$19,SMALL(IF(COUNTIF
(A$1:A$19,B$1:B$19)=0,ROW($1:$19)),ROW(1:1))))

Copy down until you get "blanks".

Will return:

C1 = 12A221332
C2 = 12A221333
C3 = blank

Biff
 
C

cpetta

Biff

I selected C1 and presses key combo of CTRL,SHIFT,ENTER, but was unable to
paste in the formula.
 
B

Biff

Hi!

Copy/Paste the formula into cell C1.

The formula will appear in the Formula Bar.

Goto the Formula Bar and place the cursor at the end of
the formula.

Hold down the CTRL key and the SHIFT key then hit ENTER.

If done properly Excel will enclose the formula in
squiggly braces { } which means that it is an array
formula. You must use the key combo of CTRL,SHIFT,ENTER
for it to work. You can not just simply type the braces in.

Biff
 
C

cpetta

Biff,

I get an error message "It says my formula is missing parenthesis. This is
the formula I copy/pasted into C1, then clicked at the end of the formula and
pressed Ctrl, Shift, Enter

=IF(ISERROR(SMALL(IF(COUNTIF(A$1:A$19,B$1:B$19)=0,ROW
($1:$19)),ROW(1:1))),"",INDEX(B$1:B$19,SMALL(IF(COUNTIF
(A$1:A$19,B$1:B$19)=0,ROW($1:$19)),ROW(1:1))))
 
B

Biff

Hi!

Should work, there's nothing wrong with the formula.

The formula is all on one line. Sometimes if you copy from
a certain source you can get messed up with line breaks or
word wrap.

If you want to send me a copy of the file I'll do it for
you. Post back with an email address and I'll contact you.

Biff
 
Top