vlookup

5 pts.
Tags:
Excel 2003
Excel 2003 VLOOKUP function
Microsoft Excel 2003
VLOOKUP function
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
ASKED: November 24, 2011  11:09 PM
UPDATED: March 6, 2012  6:59 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Chippy088
    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.
    4,625 pointsBadges:
    report
  • DoneThat
    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
    830 pointsBadges:
    report
  • Chippy088
    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.
    4,625 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following