Two Excel Sheets - Merging Selective Info

[bizres]

I have an xls file comprising 2 excel sheets. Each sheet has a common column/field with the heading PID.

Both sheets have been exported from a medical patients record system. One sheet is a complete demographics of all patients in the database and comprises of more than 200K records (let's call this sheet Demographics), while the other sheet comprises of those patients who have visited the clinic in the past 5 yrs and has about 70K records (let's call this sheet Visits). Many records in the Visits sheet comprise of patients visiting the clinic more than once. So many of the PID's (Patient Identification) will be repetitive. While the Demographics sheet lists a PID only once and each PID is linked to only one person. For example, there are some patients who have visited the clinic 7 times in the past 5yrs, so the visits sheet will show 7 records for the same PID, while the demographics sheet will only show one record for this PID.

Since the Visits sheet does not show names and contact details of the patient associated with the unique PID, I need to somehow know by name and contact details (eg tel, email) who are the people who have been visiting the clinic in the past 5yrs.
So In Visits sheet I want to add the columns ie Telephone, email and name, next to the appropriate PID.

So I made 2 separate files: Demographics.xlsx and Visits.xlsx

I cleaned up all the unnecessary columns in Demographics.xlsx and am now left with the following column headings:

pid

fname

lname

phone_home

phone_biz

phone_contact

phone_cell

email

And then I have the Visits.xlsx file with the following columns:

pid

fname

lname

email

Now I went into the Visits.xlsx file which under the pid column has 79818 records (the other columns are empty) and now in the fname I did:

pid	fname	lname	email
93969	=VLOOKUP(pid,['Demographics']Demographics!$A$2:$H$2,8,FALSE)

and here (in the Visits.xlsx file) I would expect the fname for PID 93969 to be automatically filled with Salim which is the fname record in the Demographics.xls file for PID 93969 but instead, I'm presented with #NAME? error, "The formula contains unrecognized text"

So I'm not sure how I can bring in the records from the Demographics.xlsx file and get the appropriate record next to it's respective PID.

Would really appreciate some help on this, pls.

[mikeava]

Hi - can you post a small sample of your files?

[bizres]

As for the Demographics sheet, I've replaced actual data (first name, last name, contact numbers and emails) to dummy data and have kept only the first 3 lines available. The PID column remains unchanged in both sheets.

I managed to directly attach the Visits.xlsx to this message, but the Demographics.xlsx file exceeded the size limit and can be found here: bit[DOT]ly/3BfHjiR

[mikeava]

So I could not get your Demographics file so I added a tab in your file and made up a few lines. See attached sheet.

One thing I noticed in your formula you had =VLOOKUP(PID but I did not see PID in the Name Manager(in Formula tab). So you have to highlight Column A in Visits tab, click on Formula tab, under Defined Tabs section click on Define Name and then click OK. Otherwise it has nothing to reference that PID to. You would just have to use =VLOOKUP(A2 for example if you did not do all that before.

Also the last number in the formula, in your case 8 refers to the columns that you have to count where you want the info to come from. In that case that is the email column, not the fname.

Hope all this makes sense --- take a look at what I have done and let me know if I can explain anything better.

[bizres]

Worked beautifully, Thanks.