Results 1 to 5 of 5

How do I find data from one spreadsheet in another, and compare values

This is a discussion on How do I find data from one spreadsheet in another, and compare values within the Excel Questions forums, part of the Question Forums category; First, you guys are awesome. You solved my last problem perfectly. Of course, since no good deed goes unpunished, I ...

  1. #1
    New Member
    Join Date
    Apr 2012
    Posts
    8

    Default How do I find data from one spreadsheet in another, and compare values

    First, you guys are awesome. You solved my last problem perfectly. Of course, since no good deed goes unpunished, I have another question.

    Spreadsheet 1, "NPI.xls" contains (among other things) the following fields
    A1 - NPI (it is a unique ID for a doctor)
    B1 - Last Name
    C1 - First Name
    D1 thru H1 - Address information (addr1, addr2, city, state, zip)
    (There are around 13,000 rows)

    Spreadsheet 2, "PECOS.xls" contains
    A1 - NPI
    B1 - Last Name
    C1 - First Name
    (around 60,000 rows)

    What I want to do is go to spreadsheet 2 and select the first NPI, find it in spreadsheet 1. If found, then compare to determine if last name and first names are identical. There is no certainty that the find will work. If the find fails or succeeds, fine, move on to the next row.

    Thanks!
    Pete

  2. #2
    Board Regular Darren Bartrup's Avatar
    Join Date
    Mar 2006
    Location
    Nottingham (UK)
    Posts
    1,036

    Default Re: How do I find data from one spreadsheet in another, and compare values

    This formula should do the trick:

    Code:
    =B2 & C2 =VLOOKUP(A2,Sheet1!$A$2:$C$500,2,FALSE) & VLOOKUP(A2,Sheet1!$A$2:$C$500,3,FALSE)
    Will compare the first and last names and return true or false.
    or
    Does B2 & C2 equal column B & C on whichever row the the NPI number appears between rows 2 & 500.

    Edit: Will return an #N/A error if the NPI doesn't appear on both sheets.
    Using Office 2003 & 2010,

    I'm 1 of the 10 people that don't understand binary. Guess that means the other 1001 do.

    No answer to your post?
    Get someone to read it - does it make sense or does it sound like gibberish?

  3. #3
    New Member
    Join Date
    Apr 2012
    Posts
    8

    Default Re: How do I find data from one spreadsheet in another, and compare values

    Boy, I've spent an hour trying to make this work.

    I am sure that I am completely wrong, but it looks like the formula is self-referring. In my example, I have two separate spreadsheets, NPI, and PECOS. I am doing the work in PECOS, using NPI as having the NPI value and Last/first that I want to compare.

    When I implement your solution, I pasted it into column D2 of PECOS. The way that it seems to read to me, it is looking into the PECOS spreadsheet, not the NPI spreadsheet.

    Guessing that perhaps you were expecting me to copy the NPI spreadsheet into Sheet1 of the PECOS spreadsheet, (since your formula refers to Sheet1 multiple times) I gave that a try. Now when I try, I only get a result of #NA.

    Clearly we are on to something, but I just don't have the experience to kick it to the next level.

    Any help would be appreciated!

    Thanks,
    Pete

  4. #4
    Board Regular
    Join Date
    Feb 2007
    Posts
    79

    Default Re: How do I find data from one spreadsheet in another, and compare values

    You are correct "NPI.xls" goes on Sheet 1 and "PECOS.xls" sheet 2. The formula goes to "PECOS" D2 then copied down. #N/A appears when the NPI cannot be found and or the surname & given name differ. If the NPI & surname & given name are the same then TRUE returns

    If all you get is #N/A I suggest you check for spaces or some other anomaly in the ID, surname and given name fields.

    you say there are 60,000 odd lines to check, you'll need to amend the $C$500 to the total line items
    Teach a man to fish, and you feed him for life

  5. #5
    New Member
    Join Date
    Apr 2012
    Posts
    8

    Default Re: How do I find data from one spreadsheet in another, and compare values

    Outstanding sir! I'll be augmenting this extensively to include comparing addresses and the like, so this is very very helpful, thanks.

    Ok, now a slightly different take. I am finding that popping back and forth between the sheets to be time consuming. Is there a way to use the same formula to 'drag over' the LName and FName to Sheet2, into columns e and f?

    This way if Sheet 2 is SMITH, BILL
    And Sheet 1 is SM1TH, BILL (note the "1" instead of the "I")

    Then on Sheet 2, we'll have

    A2 = SMITH
    B2 = BILL
    D2 =
    E2 = SM1TH
    F2= BILL

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com