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 Ive 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!. Ive 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
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 Ive 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!. Ive 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
