Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help with V lookups | Excel Worksheet Functions | |||
Lookups | Excel Worksheet Functions | |||
Lookups | Excel Worksheet Functions | |||
Lookups | Excel Discussion (Misc queries) | |||
LOOKUPS - Creating LOOKUPs where two different values must BOTH be satisfied. | Excel Worksheet Functions |