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
330 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
copy the content of word to outlook 3 46
What is format f12.8 for a CSV file 6 37
ADD New Entries 7 15
Excel 2016 formulas 5 22
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
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;…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

932 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

8 Experts available now in Live!

Get 1:1 Help Now