OnsiteComputerDoctor
asked on
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
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
ASKER
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.
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.
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
ASKER
Reg....that looks like a good output file. Thank you
ASKER
One more question....what is the best file format for you? Crystal can output to CSV or Excel....with or without headers?
Thanks
Thanks
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?
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?
ASKER
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!!!!!
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!!!!!
ASKER
anyone?
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
ASKER
no worries. of course work gets priority.
posting for attention?
posting for attention?
click the request attention at the bottom right of the original question.
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
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
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
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
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,"
Example:
1. Replace all " (#" with ","
2. Replace all ")" with ","
3. Replace "PatientName," with "PatientName,PatientID,"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
looks like office 2007 is not supported. I will test it out and then make the cust purchase 2013. :)
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
Zack
ASKER
this is great....thank you.
-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