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
329 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
 
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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,…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now