vlookup
how can I convert then match two lists of numbers? One list has thousands of 7 digit numbers. The other list is even longer, but contains all of the numbers in the first list, looking at the first 7 digits of each. The difference is, the numbers have an additional digit on the end.
Software/Hardware used:
Excel 2003
Software/Hardware used:
Excel 2003
ASKED:
November 24, 2011 11:09 PM
UPDATED:
March 6, 2012 6:59 PM
Answer Wiki:
Last Wiki Answer Submitted:
Be the first to answer this question.
To see all answers submitted to the Answer Wiki: View Answer History.
What do you mean by “how can I convert, then match two lists of numbers”
You say they are 7 digits in 1 table, and 8 digits in the other. What conversion do you want to make, and why?
What is the purpose of the comparrison? Do you want to match the first 7 digits of each field, or are you looking for duplicatres?
Can you give more information, and we can give a better answer.
Yes more info would be helpful.
To answer part of your question: to convert the 8 digit number to the 7 digit number you need for your lookup…add 100,000,000 (8 zeroes) to the number, convert it to a string, substring out digits 2 thru 8, convert back to numeric for your lookup.
You have to do it this way due to numbers under 10,000,000, which will not substring correctly due to leading zeroes.
EG: start with 1234569
The number you want to lookup with is 123456
Add 100,000,000 = 101,234,569
Convert to char will now yield the complete string you need: 101234569.
Substring 2 thru 8 = 0123456
Back to numeric = 123456
Gary
If you are just wanting to compare the first 7 digits of the 8 digit number against the 7 digit number, you could do it this way.
Divide the 8 digit number by 10, which will give a 7digit integer, if you drop the remainder and you can then do the comparison.