Forum Discussion
Comparing and retrieving locations NOT visited using two excel docs
SergeiBaklan Attaching a quick example (no real data) of what we have...you will see one tab lists, for this example, sites to be visited. The column headers are the days of the weeks, and the values are the "units" responsible to go and what days they are to go.
The second tab is an example loosely based on the export report. This tells me which "units" were seen where. In my full report, I have a column with a formula extracting the day of the week as its not in the exported report, and the column entitled "unit" on this tab is also a formula on mine pulling out the Unit number based on a device number.
Hope this helps..I do a ton in Excel often..but for some reason hitting a wall when needing to find where we DIDNT go based on the schedule tab.
Thank you for the sample. I'm not sure in which form you'd like to have the result.
You may apply conditional formatting to Schedule to highlight sits not visited. The rule could be with formula
=(COUNTIFS('Export Report'!$B$9:$B$98,$A4,'Export Report'!$G$9:$G$98,B$3)=0)*(B4>0)
it looks like
If to list not visited sites and organize as
in C3 it could be
=IFERROR(
INDEX(Schedule!$A$4:$A$28,
AGGREGATE(15,6,
1/((COUNTIFS('Export Report'!$G$9:$G$98,$B4,'Export Report'!$B$9:$B$98,Schedule!$A$4:$A$28)=0)/
INDEX(Schedule!$B$4:$H$28,0,MATCH($B4,Schedule!$B$3:$H$3,0)) >0)*
(ROW(Schedule!$A$4:$A$28)-ROW(Schedule!$A$3)),
COLUMN()-COLUMN($B$4))),
"")
and drag it down and to the right till first empty cells appear.
I used here static ranges, you may expand them or better to use Excel tables.
Thank you SergeiBaklan !! I cannot wait to dig in and try this out ! As I really want to have a clear understanding of how and why this works..I may reply back :) I want to be sure I can apply this knowledge from you in the future should I need it! :)
jennjax1010 , I may explain each formula in details, but first let agree if that's what you are looking for.
SergeiBaklan Thanks again for the info! The highlighted cells methodology is working great, however for some reason I cannot seem to get the "not visited" table view to work. I am not getting an error from the formula....just blanks, which we know from the highlighted portion cannot be possible...any ideas as to why would return blank info? Or another option to just extract the highlighted cells into a list? Heres the full formula I am using based off of yours...
=IFERROR(INDEX(Schedule!$A$2:$A$208,AGGREGATE(15,6,1/((COUNTIFS('Export Report'!$G$2:$G$7529,$B4,'Export Report'!$B$2:$B$7529,Schedule!$A$2:$A$208)=0)/INDEX(Schedule!$B$2:$H$208,0,MATCH($B4,Schedule!$B$1:$H$1,0))>0)*(ROW(Schedule!$A$2:$A$208)-ROW(Schedule!$A$1)),COLUMN()-COLUMN($B$4))),"")