Solved

Formula that finds a match for cells in Column J in Book1.xls anywhere in Column N in Book2.xls

Posted on 2011-02-16
8
332 Views
Last Modified: 2012-06-27
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.

Thanks,
John
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 FORMULA:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(N2," ","",1)," ","",1)," ","",1)

Open in new window

0
Comment
Question by:gabrielPennyback
  • 4
  • 2
  • 2
8 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 34910491
use

=MATCH(TRIM(A1),DATA,0)

where DATA is your lookup range
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34910497
check this sample...
LookupRange.xls
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 34910870
TRIM will remove all leading and trailing spaces and will convert multiple internal spaces to single ones - so using TRIM only won't give a match between J2 and J5 in your example above because 28DEF won't match - to compare regardless of spaces use SUBSTITUTE, e.g. to remove all spaces from A1

=SUBSTITUTE(A1," ","")

so to match without spaces being considered, in lookup value and match range

=MATCH(SUBSTITUTE(N2," ",""),SUBSTITUTE(J$2:J$10," ",""),0)

needs to be confirmed with CTRL+SHIFT+ENTER

regards, barry
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 1

Author Comment

by:gabrielPennyback
ID: 34911128
Thanks for the posts. "=MATCH(TRIM(A1),DATA,0)
" would work great except I need to get rid of all the spaces, not just the leading and trailing ones. I can create a helper column which gets rid of the zeros. ideally with formulas but if necessary with a function or macro

See attached workbook.

Thanks,
John
LookupRange-2-.xls
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34911192
try

=MATCH(replace(A1, " ", ""), DATA, 0)
0
 
LVL 51

Accepted Solution

by:
HainKurt earned 350 total points
ID: 34911241
oops, use SUBSTITUTE

=MATCH(SUBSTITUTE(A1, " ", ""), DATA, 0)
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 150 total points
ID: 34911323
That will work with those examples because Data range doesn't contain any spaces. If the target range has spces then you need to use substitute on that range too......as I suggested above, so using that same formula but revising for the ranges here try this formula

=MATCH(SUBSTITUTE(F4," ",""),SUBSTITUTE(DATA," ",""),0)

confirmed with CTRL+SHIFT+ENTER

regards, barry
0
 
LVL 1

Author Closing Comment

by:gabrielPennyback
ID: 34923034
Thanks, gentlemen. Hain's works perfectly for my current needs but I appreciate Barry's refinement which might come in handy later.

Thanks,
John
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question