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

phaustin

New Member
Joined
Apr 17, 2012
Messages
8
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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 = <FORMULA>
E2 = SM1TH
F2= BILL
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top