Comparing Worksheet ranges

[ibeetb]

I would like to compare Range A and Range B and then be able to identify or
write out the items in A that re NOT in B.
Keep in mind,, that range B has unique items but Range A had several
instances of the same name....i.e. non-unique items. I have done this
successfiully via Array formulas in the wrksht, but it takes too long. I
would like to do in code. Any suggestions?

 

[Ron de Bruin]

Chip Pearson have a great website that get you started
http://www.cpearson.com/excel/duplicat.htm

 

[ibeetb]

wHAT i ACTUALLY NEED TO DO IS do this in code.....not worksheet array
formulas....in the wrksht takes too long and your link was useful, but it
works easiest for wrksht formulas in the wrkshtr

 

[Myrna Larson]

If speed is the issue, VBA code will probably be SLOWER than the array formulas, not faster.

You can identify missing items with a COUNTIF or MATCH formula. Assuming you've named the ranges
RangeA and RangeB. Let's say RangeA starts in A2. RangeB starts in F2. In B2

=IF(COUNTIF(RangeB,A2)=0,"Missing from B","")

or

=IF(ISERROR(MATCH(A2,RangeB,0)),"Missing from B","")

and copy down through the last entry in RangeA.