Find Most Recent Date corresponding to Unique ID from Multiple Workbooks

NATANI

New Member
Joined
Jun 2, 2018
Messages
1
I have three workbooks one we will call, ‘Clients’ and the other two ‘Visits’ pertaining to visits to clients for the second half of 2017 and the first half of 2018. Each visit also has a corresponding job number.

Within the Clients workbook I wish to:

• populate Column O in Clients workbook with the most recent date of a visit to a client from within the Visits workbooks (which will obviously correspond to their Unique ID in both workbooks).
• Where a client exists in the client workbook, but has no corresponding records in either visits workbook, Populate with Text result “NO VISIT” or similar.

To date I’ve developed the following, which appears to work. Where the client ID is identified in both records, it appears to draw the most recent date from the 2018 records. Conversely, where it is not found in the 2018 records, the latest visit from 2017 is outputted. Unfortunately, I have not been able to output the relevant text result where the Client is not found in either Visit workbook. In this case the result output is ‘#VALUE!’. I’ve provided Approximate Structure of workbooks below. Any assistance to trouble shoot or simplify the following formula would be much appreciated.


=IF(IFERROR(VLOOKUP(D2,'[Visit Maxi - Jan to June 2018.xlsx]Visit Jan - Jun 2018'!$E$2:$E$5000,1,FALSE),IFERROR(VLOOKUP(D2,'[Visit Maxi - July to Dec 2017 Test.xlsx]Visits 1 July - 31 Dec 2017'!$E$2:$E$5000,1,FALSE),"NOT PREVIOUSLY VISITED")),MAX(IF('[Visit Maxi - Jan to June 2018.xlsx]Visit Jan - Jun 2018'!$E$2:$E$5000=D2,'[Visit Maxi - Jan to June 2018.xlsx]Visit Jan - Jun 2018'!$D$2:$D$5000,MAX(IF('[Visit Maxi - July to Dec 2017 Test.xlsx]Visits 1 July - 31 Dec 2017'!$E$2:$E$5000=D2,'[Visit Maxi - July to Dec 2017 Test.xlsx]Visits 1 July - 31 Dec 2017'!$D$2:$D$5000)))))

I also want to output the Job number corresponding to the latest date to an adjacent column in the Client workbook (P). To do so I am using the following formula. Again, It will need to draw off both the 2017 and 2018 Visit records. At present I have the following formula which only draws on 2018 records and needs some tweaking.

=IF(ISERROR(VLOOKUP(D2, '[Visit Maxi - Jan to June 2018.xlsx]Visit Jan - Jun 2018'!$E$2:$E$5000,1,FALSE)),"NOT PREVIOUSLY TASKED",(INDEX([Visit Maxi - Jan to June 2018.xlsx]Visit Jan - Jun 2018'!$A$2:$A$5000,MATCH(MAX(‘[Visit Maxi - Jan to June 2018.xlsx]Visit Jan - Jun 2018'!$D$2:$D$5000*([Visit Maxi - Jan to June 2018.xlsx]Visit Jan - Jun 2018'!$E$2:$E$5000=D2)),([Visit Maxi - Jan to June 2018.xlsx]Visit Jan - Jun 2018'!$D$2:$D$5000*('[Visit Maxi - Jan to June 2018.xlsx]Visit Jan - Jun 2018'!$E$2:$E$5000=D2)),0))))

VISITS WORKBOOK
B(date of Visit) C(Job#) D(ID)
DD/MM/YYYY | JOB123 123456
DD/MM/YYYY | JOB124 124555
DD/MM/YYYY | JOB125 123456
DD/MM/YYYY | JOB126 124555

CLIENT WORKBOOK
D(ID) O(LAST VISIT)
123456 | DD/MM/YYYY
124555 | DD/MM/YYYY



VISITS WORKBOOK
B(date of Visit) C(Job#) D(ID)
DD/MM/YYYY | JOB123 123456
DD/MM/YYYY | JOB124 124555
DD/MM/YYYY | JOB125 123456
DD/MM/YYYY | JOB126 124555

CLIENT WORKBOOK
D(ID) O(LAST VISIT)
123456 | DD/MM/YYYY
124555 | DD/MM/YYYY
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Forum statistics

Threads
1,224,825
Messages
6,181,190
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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