Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Nov 2008
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    combine two worksheets (Excel 2003)

    HI! Does anyone know how to do this:

    I have two worksheets. One has about 200 addresses on it and the other worksheet has around 5000 addresses along corresponding PID numbers for each address. I want the second worksheet to figure out which PID numbers go with their corresponding addresses on the first sheet and have them automatically populate by a PID number. Is there a formula I should use?

    Thank you for any help you can give!

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

    Re: combine two worksheets (Excel 2003)

    Welcome to Woody's Lounge!

    Let's say that the addresses on the first sheet are in A2:A201 (with a column header in A1), and that the addresses on the second sheet are in A2:A5001, with the corresponding PIDs in B2:B5001. I'll call the second sheet Sheet2.

    Enter the following formula in B2 on the first sheet:
    <code>
    =VLOOKUP(A2,'Sheet2'!$A$2:$B$5001,2,FALSE)
    </code>
    Fill down this formula as far as needed (to B201 in this example). If there are addresses on the first sheet without a match on the second sheet, the above formula will return #N/A. If you want to suppress this, you can use
    <code>
    =IF(ISNA(VLOOKUP(A2,'Sheet2'!$A$2:$B$5001,2,FALSE) ),"",VLOOKUP(A2,'Sheet2'!$A$2:$B$5001,2,FALSE))
    </code>
    Note: the single quotes around the sheet name Sheet2 aren't strictly necessary here, but they are essential if the sheet name contains spaces.

  3. #3
    New Lounger
    Join Date
    Nov 2008
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combine two worksheets (Excel 2003)

    Hans,

    Thank you for your help. I cannot seem to get the formula to work for me. Addresses are in two cells. For example: 121 Lake Street would be located in A2:B2. A2 would have the house number 121 and B2 would have the street name. The full addresses in 'Whole City' are in A2 and B2 just like above, but now the PID numbers are in C2.

    This is the formula that I have created: =VLOOKUP(A2:B2 'Whole City' A2:C4199,2,FALSE)

    Any suggestions for me?

    Thank very much,

    LaTisha

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

    Re: combine two worksheets (Excel 2003)

    VLOOKUP can only look up a value in a single column, you cannot use it to look up two values in two columns.

    You can use the following array formula in C2; you must confirm it with Ctrl+Shift+Enter:
    <code>
    =INDEX('Whole City'!$C$2:$C$4199,MATCH(A2&" " &B2,'Whole City'!$A$2:$A$4199&" "&'Whole City'!$B$2:$B$4199,0))
    </code>
    You can fill this down as far as needed.

  5. #5
    New Lounger
    Join Date
    Nov 2008
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combine two worksheets (Excel 2003)

    Hans,

    Thank you for the formula. I had to change the commas ( , ) to bars ( | ) to make it work. The formula is reading N/A in C2. This should be able to give me a PID number since all addresses in "Oct Billing" would already be in "Whole City" but I am not sure why it would not be showing up.

    =INDEX('Whole City'!$C$2:$C$4199|MATCH(A2&B2|'Whole City'!$A$2:$A$4199&" "&'Whole City'!$B$2:$B$4199|0)) - this is exactly what I have. I tried to drag it down as well and the A2&B2 changes to A3&B3.

    Any thoughts?

    Thank you again!
    LaTisha

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

    Re: combine two worksheets (Excel 2003)

    The separator in the formula is whatever your system uses as list separator,

  7. #7
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    Re: combine two worksheets (Excel 2003)

    Make sure the addresses in both systems Match.
    Using the Exact Function in Excel compare known matches from both lists. It Exact return TRUE your OK, if it returns FALSE, you have a data problem.
    What can happen is that one system can have extra spaces at the end or extra space or spaces between words in the address.
    In other words Spaces matters to Excel in the Data.
    If you have a problem you may want to consider the TRIM function in Excel.

    Regards,

    Tom Duthie

Posting Permissions

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