Joining two Excel\CSV files based on Patient ID

Good Day-
I am exporting two files from an EHR application via their crystal reports plugin with no option to modify the reports.
EETestPatients (attached) contains Email Address which I need added to the corresponding patient ID in EEAged-AR.CSV.

The intention here is to perform a list merge to email that will show the patient's outstanding balance from Aged-AR and give them a convenient link to enable them to pay online.

Please note that I created test data with matching Patient ID in both files.  The names do not match which is insignificant to me.

I would greatly appreciate some code in VBA or Script that would make it easy for an end user to add Email Address to their Aged AR report and then perform a list merge to email (outlook).

Many Thanks,
Craig



(Unfortunately, the EHR application does not offer the ability to email billing statements.)
EEAged-AR.csv
EEtestpatients.xlsx
LVL 1
OnsiteComputerDoctorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

regmigrantCommented:
I've modified the aged-ar to add two columns (eetestpatients2.xlsx) Column R extracts the patient ID from the name and column S grabs the email address from the second file (eepatientemail.xlsx).  if you use this second file as data file for a Word mail merge you can generate the letters you need

 -if I've interpreted the requirement correctly we can look at defining a process that makes it easier to apply going forward.
eetestpatients2.xlsx
EEtestpatientemail.xlsx
OnsiteComputerDoctorAuthor Commented:
Forgive me for being unclear.  I have an application which is unable to generate an AgedAR report that includes Email address.

I can, however export a patient list that includes the Patient ID in a field along with the patient's full name and email address.  I need to join the two attached files based on Patient ID and then perform a query which will output records that include the AR record along with Email address.

Many thanks.
regmigrantCommented:
That's what I've done in the example files I returned. The eetestpatients2 file contains the aged-ar sample with two new columns showing patient Id and email address
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

OnsiteComputerDoctorAuthor Commented:
Reg....that looks like a good output file.  Thank you
OnsiteComputerDoctorAuthor Commented:
One more question....what is the best file format for you? Crystal can output to CSV or Excel....with or without headers?

Thanks
regmigrantCommented:
for simplicity we would use excel but as we will be manipulating the sheets anyway it doesn't really matter.

Some points to consider:-
1. is this something the end user will be doing on a regular basis or will you be providing the information for them - ie: who generates the Crystal outputs?
2. do you need something that is self contained or is the user happy to follow defined steps within excel?
3. do you need the steps to include the mailmerge stage or is this something they are already comfortable with?
OnsiteComputerDoctorAuthor Commented:
1. is this something the end user will be doing on a regular basis or will you be providing the information for them - ie: who generates the Crystal outputs?

The End User will generate the output from Crystal. on a regular  basis.


2. do you need something that is self contained or is the user happy to follow defined steps within excel?

Self Contained would be very helpful.

3. do you need the steps to include the mailmerge stage or is this something they are already comfortable with?

Mailmerge would be most helpful if you are OK with coding.  I am most humbled by your help!!!!!
OnsiteComputerDoctorAuthor Commented:
anyone?
regmigrantCommented:
apologies I've been too busy to look at this as it requires a larger amount of time than I can devote at the moment. I will try and get around to it soon but in the meantime it might be worth posting for attention as others will be assuming its dealt with - the moderators can ask additional experts to take a look
OnsiteComputerDoctorAuthor Commented:
no worries. of course work gets priority.
posting for attention?
regmigrantCommented:
click the request attention at the bottom right of the original question.
Zack BarresseCEOCommented:
What version of Excel are you using? I'd use Power Query for this, which is a free add-in, unless you're using 2016 in which case it's natively available.

If using Power Query, you can write the query to grab the files where they sit, or even grab all files in a folder, and as long as the structure is known (even somewhat not depending on how dynamic your query is), once setup, you would only have to refresh your query.

Regards,
Zack Barresse
aikimarkCommented:
In the attached workbook, I did the following:
1. Inserted new column after column G
2. In column G, replace all " (" with ""
3. In column G, replace all ")" with ""
4. In G2:G6 (the last non-empty cell in column G), do a text-to-columns operation with a "#" delimiter
5. Added PatientID column header in H1
EEAged-AR.csv
aikimarkCommented:
Depending on the actual (production) data, you might be able to issue these changes with a text editor.
Example:
1. Replace all " (#" with ","
2. Replace all ")" with ","
3. Replace "PatientName," with "PatientName,PatientID,"
Zack BarresseCEOCommented:
If you want to do this with Power Query, it can look like this (if you want the M code)...

For your table from 'EEtestpatientemail.xlsx', assuming you tabled the data and it's titled "Table1"
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PatientID", Int64.Type}, {"Lname", type text}, {"Fname", type text}, {"Address", type text}, {"Address 2", type any}, {"City", type text}, {"State", type text}, {"Zip", Int64.Type}, {"Phone1", type any}, {"Phone2", type text}, {"Email", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"PatientID", "Lname", "Fname"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "PatientName", each [Lname]&", "&[Fname]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Lname", "Fname"})
in
    #"Removed Columns"

Open in new window


For your table from 'eetestpatients2.xlsx', assuming you tabled the data and it's titled "Table2"
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Region", type any}, {"Location", type any}, {"Provider", type any}, {"FeeSlipNumber", Int64.Type}, {"AgingDate", type any}, {"InsuranceName", type any}, {"PatientName", type text}, {"Comments", type any}, {"ProductName", type text}, {"BalanceType", type text}, {"Balance", type number}, {"Current", Int64.Type}, {"Bucket1", Int64.Type}, {"Bucket2", Int64.Type}, {"Bucket3", Int64.Type}, {"Bucket4", type number}, {"UnApplied", Int64.Type}, {"PatiendId", Int64.Type}, {"email", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"PatientName", "PatiendId", "Balance"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns","PatientName",Splitter.SplitTextByEachDelimiter({" ("}, null, false),{"PatientName.1", "PatientName.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"PatientName.1", type text}, {"PatientName.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"PatientName.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"PatientName.1", "PatientName"}})
in
    #"Renamed Columns"

Open in new window


And to merge them:
let
    Source = Table.NestedJoin(Table1,{"PatientID"},Table2,{"PatiendId"},"NewColumn",JoinKind.Inner),
    #"Expanded NewColumn" = Table.ExpandTableColumn(Source, "NewColumn", {"Balance"}, {"NewColumn.Balance"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded NewColumn",{{"NewColumn.Balance", "Balance"}})
in
    #"Renamed Columns"

Open in new window


I'd recommend a connection-only query for the first two, then load the second to the worksheet. After that you only need to refresh the final table results.

Please note you can also have Power Query grab the data from a closed file or folder, as I mentioned before.

HTH
Zack

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
OnsiteComputerDoctorAuthor Commented:
power query sounds like a good direction to go.

They are office 2007.  hopefully the add-in works.

I don't mind making the end user go thru a structured process to accomplish this.  their current manual process dwarfs any query process.

no problem na mind the files.

I will take a look
OnsiteComputerDoctorAuthor Commented:
looks like office 2007 is not supported.  I will test it out and then make the cust purchase 2013. :)
Zack BarresseCEOCommented:
No, it's not supported. Earliest version it is supported in is 2010. It's native in Excel 2016, but an add-in for 2010 and 2013. It's absolutely awesome. :)

Zack
OnsiteComputerDoctorAuthor Commented:
this is great....thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.