Forum Discussion
Comparing and retrieving locations NOT visited using two excel docs
I have two excel docs; one is a schedule of locations to visit on certain days. The other is an exported report that shows visits made by day. I need to be able to not just compare the report against the schedule, but see which locations were NOT visited on the day they were supposed to (Outlined within the schedule excel doc)
The schedule basically lists locations as rows, the column headers are days of the week, and the values under those are who is supposed to go to said location on that day.
The exported report provides me with locations, who was seen there, and what day it was.
20 Replies
That could be done by Power Query - query schedule, unpivot (since dates are column headers), query report, merge first with second on dates, expand location and filter on nulls.
Above is only idea, concreteness depends on how your data is structured.
Thanks SergeiBaklan ! Unfortunately, here at work we are on Excel 2010 without Power Query add on...Unfortunately I am thinking this may have to be some formula work or perhaps reorganizing the exported reports. I could be wrong though..
Perhaps that could be formulas, Power Query is just more natural way these days. To suggest something concrete with formulas it will be better if you submit small sample file(s) without any sensitive information.
