Lookups
 

Does anyone know if it is possible to do a lookup to find similar
numbers from one Excel worksheet to another, and also similar values?

So I am not looking for a number that is exactly the same in the first
instance but might be similar e.g.

10571234 is the number on worksheet one


Below might be numbers from worksheet two that I would want it to
return a match for:

10571453
1571234
1057123
10571234
10571243

Would all be numbers where part or all of the number is similar or the
same as the number from worksheet one

And for values, is there a lookup you can do to return anything within
a certain percentage range of the value?

E.g. if you wanted anything returned with a 10% tolerance e.g. If
£1.00 is on worksheet one it would return anything within 10% of that
from worksheet two?

Cheers very much for any help.

John

Lookups
 

I can see how you might justify 1057123 as being "similar" to
10571234, as the first 7 digits are the same (although by that logic
then 1 is similar), but how can you say that 1571234 is similar? Your
logic would dictate that 14, 134, 1234, 11234, 171234 are also
similar, and if digits can be missing from the second position
onwards, then why not from the third position, or fourth position etc?
There would then be a very large number of "similar" numbers to test
out.

Pete

On May 24, 9:58 pm, John wrote:
Does anyone know if it is possible to do a lookup to find similar
numbers from one Excel worksheet to another, and also similar values?

So I am not looking for a number that is exactly the same in the first
instance but might be similar e.g.

10571234 is the number on worksheet one

Below might be numbers from worksheet two that I would want it to
return a match for:

10571453
1571234
1057123
10571234
10571243

Would all be numbers where part or all of the number is similar or the
same as the number from worksheet one

And for values, is there a lookup you can do to return anything within
a certain percentage range of the value?

E.g. if you wanted anything returned with a 10% tolerance e.g. If
£1.00 is on worksheet one it would return anything within 10% of that
from worksheet two?

Cheers very much for any help.

John

Lookups
 

Hi John,

The Search function allows "*" (asterisk) as a wild card. In order to make
the numbers in your example, the value in B2 is the first 5 characters,
substituting 0 with "*":

B1: 10571234
B2: 1*571
B3:B7 =IF(ISERROR(SEARCH(B$2,A3))=TRUE,"","Fuzzy Match")
A3:10571453
A4:1571234
A5:1057123
A6:10571234
A7:10571243

Let me know if this works for you.

Thanks,
Peggy

"John" wrote:

Does anyone know if it is possible to do a lookup to find similar
numbers from one Excel worksheet to another, and also similar values?

So I am not looking for a number that is exactly the same in the first
instance but might be similar e.g.

10571234 is the number on worksheet one


Below might be numbers from worksheet two that I would want it to
return a match for:

10571453
1571234
1057123
10571234
10571243

Would all be numbers where part or all of the number is similar or the
same as the number from worksheet one

And for values, is there a lookup you can do to return anything within
a certain percentage range of the value?

E.g. if you wanted anything returned with a 10% tolerance e.g. If
£1.00 is on worksheet one it would return anything within 10% of that
from worksheet two?

Cheers very much for any help.

John

Lookups
 

On 24 May 2007 15:13:45 -0700, Pete_UK wrote:

I can see how you might justify 1057123 as being "similar" to
10571234, as the first 7 digits are the same (although by that logic
then 1 is similar), but how can you say that 1571234 is similar?

Hi Pete. Yes these were not currency values as in £ or $ but for
example might be order numbers etc that have been miskeyed. Like I
said its a number not a currency so in this instance as its not
sequential it would be similar.

Your
logic would dictate that 14, 134, 1234, 11234, 171234 are also
similar, and if digits can be missing from the second position
onwards, then why not from the third position, or fourth position etc?
There would then be a very large number of "similar" numbers to test
out.

Not with this. I would just be looking at common errors where one
number might be missed out in error but the majority are still in
sequence. Or it might be a case of dyslexia where someone has put in
43 instead of 34 etc. So you will still have perhaps the first 5
numbers correct or the last 5 correct. Maybe a number in the middle
would be miskeyed or missed out so it would be the first few and last
numbers I would be looking for that are the same.

Cheers

John

Lookups
 

On Fri, 25 May 2007 05:44:00 -0700, pshepard
wrote:

Hi John,

The Search function allows "*" (asterisk) as a wild card. In order to make
the numbers in your example, the value in B2 is the first 5 characters,
substituting 0 with "*":

B1: 10571234
B2: 1*571
B3:B7 =IF(ISERROR(SEARCH(B$2,A3))=TRUE,"","Fuzzy Match")
A3:10571453
A4:1571234
A5:1057123
A6:10571234
A7:10571243

Let me know if this works for you.

Thanks,
Peggy

Thanks. I'll give this a try.

John