Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Mar 2004
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with Excel Task (2002)

    OK, I hope I'm on the proper message board. I have a very specific task I want to perform in Excel 2002. Here's the details. I have 2 different spreadsheets, spreadsheet one with a list of phone numbers(Column A) and their corresponding names (Column C), and spreadsheet 2 with phone usage history that contains phone numbers but no corresponding names.

    I want to compare the second spreadsheet phone numbers to the known phone numbers on the first spreadsheet and write the corresponding names in a designated name column on the second spreadsheet. If the phone number on the second speadsheet has no match on the first spreadsheet I want to write a ? in the designated name column. In short, how do I get the NAMES on the SpreadSheet that doesn't have names? This sheet is very large and has multiple instances of the same phone number.

    HOW DO I DO THIS? Any help on where to start or what to do?

    Thanks for your assistance!!!

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Excel Task (2002)

    You didn't say how far down the names and numbers go on sheet1so let's say they are in A1:C2000. You also did not say which column on Sheet2 contains the phone numbers, so let's say they are in column A. You will need to modify the formula below if those are not correct. In row 1 of the column where you want the name on Sheet2 enter the formula below:

    <pre>=IF(ISERROR(VLOOKUP(A1,Sheet1!$A$1:$C$2000,3, FALSE)),"?",VLOOKUP(A1,Sheet1!$A$1:$C$2000,3,FALSE ))
    </pre>


    Then copy this formula as far down Sheet2 as the numbers go.
    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Mar 2004
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Excel Task (2002)

    Legare - I tried but the formula would only returned ? in the column for each phone number in sheet2. OK, sheet1 has the names (column A) and corresponding numbers (column C) has 56 entries ($C$56). In sheet2 the phone numbers are in Column K (approximately 600) and I want to place the matching name from sheet1 (column A) in sheet2 (column M) for each of the 600 numbers in Column K of sheet2.

    Plus, how do I mass copy of the formula?

    Hope this additional info helps. I think your on the right track, but I know so little about Excel.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Excel Task (2002)

    OK, that is quite different. Put the formula below in cell M1 on Sheet2 and copy it down as far as it needs to go:

    <pre>=IF(ISERROR(MATCH(K1,Sheet1!$C$1:$C$56,0)),"? ",OFFSET(Sheet1!A1,MATCH(K1,Sheet1!$C$1:$C$56, 0)-1,0,1,1))
    </pre>

    Legare Coleman

  5. #5
    New Lounger
    Join Date
    Mar 2004
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Excel Task (2002)

    Legare - Still not working. It's got to be something simple. Can I send the Excel file to you? It might make more sense if I do.

    Thanks

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with Excel Task (2002)

    You can attach a copy of the workbook to a post. It must be below 100 KB; you can zip it if necessary. Remove sensitive information or replace it with dummy data.

  7. #7
    New Lounger
    Join Date
    Mar 2004
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Excel Task (2002)

    OK, here's a stripped down verion of the file. If a formula will work with this it'll work for the real thing. I've removed all impotant info, I think. The Numbers sheet has stripped down phone numbers and the names have been change to uniqe letters. Several columns were removed from the Info sheet and the phone numbers again stripped down, but all columns should be the same as my original, just alot less numbers to sift thru on the Info sheet.

    Thanks for your assistance!!

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Help with Excel Task (2002)

    Try this:
    =IF(ISERROR(MATCH(K1,Numbers!$A$31:$A$60,0)),"??", VLOOKUP(K1,Numbers!$A$34:$C$60,3,FALSE))

    Steve

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Excel Task (2002)

    You said that your sheets were Sheet1 and Sheet2, not Numbers and Info. Steve's formula should work.
    Legare Coleman

  10. #10
    New Lounger
    Join Date
    Mar 2004
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Excel Task (2002)

    GREAT JOB GUYS!! It's working, BUT how do I get the K cell "lookup_value" to change automatically for the corresponding row? I have to change it manually to make it work, and the actual spreadsheets of data will have thousands of phone numbers to compare. Sorry I know so little about Excel. Any suggestions?

    Thanks

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Excel Task (2002)

    Enter that formula into the first row. Then select the cell with the formula. There will be a small square in the lower right corner of the cell border. Put the cursor over that square and it should turn from a large cross to as smaller thin cross. left click and drag down to copy the formula to the other cells. The lookup value cell reference should automatically update to the corresponding row.
    Legare Coleman

  12. #12
    New Lounger
    Join Date
    Mar 2004
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Excel Task (2002)

    Thanks for all your help Legare. That works great! And I've started to learn some essential information about Excel that I can use in the future.

    You're the GREATEST!!

  13. #13
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Help with Excel Task (2002)

    Steve,

    Since I am doing a class where I need to show VLOOKUP, I took interest in your solution to the example. While I know how to use VLOOKUP, having a meaningful example that I don't have to develop <img src=/S/smile.gif border=0 alt=smile width=15 height=15> is always useful.

    However, I am very confused. Why would the MATCH start on $A$31 and the VLOOKUP on the false result start on $A$34?

    I downloaded the sample spreadsheet. First time I went to use it, Excel complained about a broken spreadsheet (in addition to saying there were macros) and repaired it. Being suspicious, I downloaded it again and it was ok the 2nd time (but still with macros).

    I inserted your formula in col M of the Info sheet and filled down. With your formula the way it was, I got "??" as the name for 281-373-5284, which isn't even close. When I changed the formula to start in row 1 of the Numbers sheet for both the MATCH and the VLOOKUP, I got C for 281-373-5284 which looks correct.

    Fred

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Excel Task (2002)

    I just noticed an error in Steve's formula. Please change it to:

    <pre>=IF(ISERROR(MATCH(K1,Numbers!$A$1:$A$60,0))," ??",VLOOKUP(K1,Numbers!$A$1:$C$60,3,FALSE))
    </pre>

    Legare Coleman

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Help with Excel Task (2002)

    <img src=/S/doh.gif border=0 alt=doh width=15 height=15> <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    I apologize for any confusion.

    Legare, thanks for correcting my error.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •