ISNA match function help

[Adam]

Ok, i need some help.

I have two columns of data. For one of the columns, i need to find out if
its contained somewhere, anywhere in the other column. So how was going to
do it was useing an ISNA, Match function and create a new column called
Missing?, and then drag the code down, if its not contained then it will
output True, if it is there, then it will output False. This usually works
great. The only problem im having is that my column contents are kinda
wacky. Here are some examples

M4758375
12-67-8
056-76-23
08-22-456

So both columns have data like this. And for some reason, my ISNA code isnt
working.

Any ideas?

Thanks,

-Adam

 

[Ron Coderre]

Can you give examples of items where the formula fails?

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Adam]

The formula Fails on those types of Numbers i posted above

M4758375
12-67-8
056-76-23
08-22-456

So what i mean is that these types of numbers are found in both columns, but
the code still comes out as true. I have tested the code for very simple
inputs such as 1, and 2, and it works fine.

 

[Ron Coderre]

I can't get those values to fail.....Perhaps in one of the columns there are
leading or trailing spaces, where the other column does not have them.

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Jim]

Adam,

Have you thought of using Data>Filter>Advanced?

One column would be your data range and the other column would be your
criteria range. Just make sure they both have the same heading. This will
find any exact matches.

HTH
Jim

 

[Pete_UK]

These are all text values, and so you might have space(s) at the end
of them and not know it - if you are looking for an exact match then
you will not get it if one value has an extra space. Apply the TRIM
function to both sets of data to see if this improves things.

Hope this helps.

Pete

 

[Adam]

Ron,

Your right. heres 10 spaces at the end of these types of Data. Is there
anyone, via a macro or code, to remove these 10 spaces from group of rows?

 

[Pete_UK]

If your data is in A1, put this formula in a helper column:

=TRIM(A1)

Copy the formula across if you also have data in column B, C, D etc,
and copy down as necessary.

Then highlight all the cells with this formula in, click <copy>, then
click Edit | Paste Special | Values (check) | OK then <Esc>. This will
have converted all the formulae to values, and any leading / trailing
or multiple spaces will have been removed. You can then copy these
values to overwrite the values that were in coumns A, B, C etc, and
then you can delete the helper columns.

Hope this helps.

Pete

 

[Ron Coderre]

If every cell in the list has exactly 10 spaces after the text....
try this:

Select the list, then...

From the Excel Main Menu:
<edit><replace>
Find what: (enter 10 spaces here)
Replace with: (leave this blank)
Click [Replace All]

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Rick Rothstein \(MVP - VB\)]

If the data is all like shown in the OP's examples, namely, with no leading
or internal spaces, then he can put a single space in the "Find what:" field
(and then follow the rest of your instructions) and eliminate all the spaces
(no matter what their number).

Rick

If every cell in the list has exactly 10 spaces after the text....
try this:

Select the list, then...

From the Excel Main Menu:
<edit><replace>
Find what: (enter 10 spaces here)
Replace with: (leave this blank)
Click [Replace All]

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

Ron,

Your right. heres 10 spaces at the end of these types of Data. Is there
anyone, via a macro or code, to remove these 10 spaces from group of
rows?