ISNA match function help
 

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

ISNA match function help
 

Can you give examples of items where the formula fails?

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

Regards,

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


"Adam" wrote in message
...
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

ISNA match function help
 

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.

"Adam" wrote:

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

ISNA match function help
 

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)


"Adam" wrote in message
...
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.

"Adam" wrote:

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

ISNA match function help
 

Adam,

Have you thought of using DataFilterAdvanced?

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

"Adam" wrote:

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

ISNA match function help
 

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

On Mar 7, 4:09*pm, Adam wrote:
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.



"Adam" wrote:
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- Hide quoted text -

- Show quoted text -

ISNA match function help
 

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?

"Ron Coderre" wrote:

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)


"Adam" wrote in message
...
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.

"Adam" wrote:

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

ISNA match function help
 

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

On Mar 7, 6:26*pm, Adam wrote:
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?



"Ron Coderre" wrote:
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)

"Adam" wrote in message
...
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.

"Adam" wrote:

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- Hide quoted text -

- Show quoted text -

ISNA match function help
 

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)

"Adam" wrote in message
...
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?

"Ron Coderre" wrote:

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)


"Adam" wrote in message
...
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.

"Adam" wrote:

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

ISNA match function help
 

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


"Ron Coderre" wrote in message
...
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)

"Adam" wrote in message
...
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?

"Ron Coderre" wrote:

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)


"Adam" wrote in message
...
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.

"Adam" wrote:

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