MATCH function - 2 columns w/ SIMILAR, not EXACT data

colu. 1 contains:

1001
vendor 43980
1001/1180/90 CAT 8
1001/1180/90/8

col. 2 contains:
1001
SD3 vendor number 43980
PO 45609
1001 1180 90 8

I need to match these 2 columns IF the values are the same, regardless of
text.

Any suggestions?

Given your data, the following formulas will generate a match:

=a1=b1
=right(a2,5)=right(b2,5)
no match in row 3
=substitute(a4,"/"," ")=b4

However, if other date have different similarities, you may find if more
efficient to write a macro to toss out the extraneous stuff, so you end up
with exact matches.

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"Jane" <Jane@discussions.microsoft.com> wrote in message
news:EC1F1A94-F3C8-4EBD-9364-F1DAB4B3BDB8@microsoft.com...
> colu. 1 contains:
>
> 1001
> vendor 43980
> 1001/1180/90 CAT 8
> 1001/1180/90/8
>
> col. 2 contains:
> 1001
> SD3 vendor number 43980
> PO 45609
> 1001 1180 90 8
>
> I need to match these 2 columns IF the values are the same, regardless of
> text.
>
> Any suggestions?
>

Hi Fred,
I'm not very familiar with the Match function (altho' I use Vlookup
frequently)... woul dyou mind giving me an example of the formula written out
that captures what you have suggested? It would be most appreciated.
thank you, Jane

"Fred Smith" wrote:

> Given your data, the following formulas will generate a match:
>
> =a1=b1
> =right(a2,5)=right(b2,5)
> no match in row 3
> =substitute(a4,"/"," ")=b4
>
> However, if other date have different similarities, you may find if more
> efficient to write a macro to toss out the extraneous stuff, so you end up
> with exact matches.
>
> --
> Regards,
> Fred
> Please reply to newsgroup, not e-mail
>
>
> "Jane" <Jane@discussions.microsoft.com> wrote in message
> news:EC1F1A94-F3C8-4EBD-9364-F1DAB4B3BDB8@microsoft.com...
> > colu. 1 contains:
> >
> > 1001
> > vendor 43980
> > 1001/1180/90 CAT 8
> > 1001/1180/90/8
> >
> > col. 2 contains:
> > 1001
> > SD3 vendor number 43980
> > PO 45609
> > 1001 1180 90 8
> >
> > I need to match these 2 columns IF the values are the same, regardless of
> > text.
> >
> > Any suggestions?
> >
>
>
>

The formulas I gave you will evaluate to TRUE or FALSE. I didn't provide any
information on the Match function, because I didn't see an application for
it.

As you are interested, Match works somewhat like Vlookup, but rather that
returning a cell value, it returns the position in the list (eg, the 5th
entry in the list).

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"Jane" <Jane@discussions.microsoft.com> wrote in message
news:FA4C42D6-7AA1-4736-A89B-2A25645F928B@microsoft.com...
> Hi Fred,
> I'm not very familiar with the Match function (altho' I use Vlookup
> frequently)... woul dyou mind giving me an example of the formula written
> out
> that captures what you have suggested? It would be most appreciated.
> thank you, Jane
>
> "Fred Smith" wrote:
>
>> Given your data, the following formulas will generate a match:
>>
>> =a1=b1
>> =right(a2,5)=right(b2,5)
>> no match in row 3
>> =substitute(a4,"/"," ")=b4
>>
>> However, if other date have different similarities, you may find if more
>> efficient to write a macro to toss out the extraneous stuff, so you end
>> up
>> with exact matches.
>>
>> --
>> Regards,
>> Fred
>> Please reply to newsgroup, not e-mail
>>
>>
>> "Jane" <Jane@discussions.microsoft.com> wrote in message
>> news:EC1F1A94-F3C8-4EBD-9364-F1DAB4B3BDB8@microsoft.com...
>> > colu. 1 contains:
>> >
>> > 1001
>> > vendor 43980
>> > 1001/1180/90 CAT 8
>> > 1001/1180/90/8
>> >
>> > col. 2 contains:
>> > 1001
>> > SD3 vendor number 43980
>> > PO 45609
>> > 1001 1180 90 8
>> >
>> > I need to match these 2 columns IF the values are the same, regardless
>> > of
>> > text.
>> >
>> > Any suggestions?
>> >
>>
>>
>>