Joining Tables Based on Common ID

[bensamton]

Question: Each Patient ID represents one patient. There are multiple visit dates and prescription dates for each patient, in separate tables. I want to populate the “closest prescription date” column in Table 1 with the prescription date for that patient that is closest to each date. I also want to be able to do 2nd closest, 3rd closest, etc.
Table 1
Patient ID Visit Date Closest Prescription Fill Date 2nd closest prescription fill date 3rd closest prescription fill date
1 1/1/2015
1 9/15/2015
2 1/5/2015
3 3/6/2015
3 11/16/2015
4 9/5/2015
4 9/4/2015
5 11/7/2015
5 7/4/2015

Table 2
Patient ID Prescription Fill Date
1 1/6/2015
1 12/8/2015
2 11/8/2015
2 8/18/2015
2 9/9/2015
3 6/17/2015
3 9/8/2015
4 3/5/2015

[kersplash]

Can you attach a sample workbook showing your mocked-up results for that sample.

Go Advanced -> Manage Attachments -> Upload