Book1 Column J has more values than Column N in Book2.xls, and they are in different order. Furthermore the entries in Book1 Column J often have spaces that the entries in Book2 Column N don't have (see example below). Therefore we need something that ignores spaces and just searchesto match all the characters excluding spaces. (I think this is possible, I don't know).
Also, if this is of any relevance, how do I get rid of all the spaces in a cell, since there might be 20 or 30 spaces in a given entry?
I hope this makes sense.
Book1 Column J:
28 DEF LIGHTS, 32DEF LIGHTS CANNOT GO OFF (has trailing spaces as well)
Book2 Column N:
28DEF LIGHTS, 32 DEF LIGHTS CANNOT GO OFF
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(N2," ","",1)," ","",1)," ","",1)