Comparing and retrieving locations NOT visited using two excel docs

Copper Contributor

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

@jennjax1010 ,

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 @Sergei Baklan ! 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..

@jennjax1010 

 

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.

@Sergei Baklan  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.

@jennjax1010 

 

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

image.png

If to list not visited sites and organize as

image.png

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 @Sergei Baklan !! 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.

@Sergei Baklan Will do, trying them out today! :)

@Sergei Baklan  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))),"")

@jennjax1010 

 

I adjusted first two sheets of the sample file and copy/paste your formula without any changes - it works. Please see in attached file.

 

It's assumed what Not Visited range starts from the cell B4

image.png

otherwise the formula is to be adjusted. Or, you may start from B4 and after that remove/insert columns and/or rows as required.

@Sergei Baklan Thank you for that, I am using B4 as well on my sheet...just not sure why it isnt returning any data at all. Thank you anyway! Will just keep plugging away at it..there are definite locations not visited as evident in the highlighted cells/conditional formatting example...just not sure why not returning them in this method. I dont think that the returning data being longer strings of text besides simple letter like in the example would matter either.

 

Appreciate all the help!

 

@jennjax1010 , if you may submit your file keeping only few first records and renaming in them actual names on something abstract (other words removing sensitive information) I could try to check what's wrong with the formula.

@Sergei Baklan You are amazing :) Attaching a sample with just a few rows of data...same formula..

Hi @jennjax1010 

 

In your first sample in Schedule where were numbers and here are texts. Thus the condition in the middle of the formula as value>0 shall be changed on len(value)>0

=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)/
  LEN(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))),
"")

Please check attached.

@Sergei Baklan Ahhh!! I wasnt aware, thank you so much! Making note of that :)

It worked!!!!

@Sergei Baklan  Thought you were rid of me :) Just out of curiosity...is it possible to have the Not Visited Tab list display vertically, with the days of the week as column headers instead of a horizontal chart? I attempted to do so myself within the formula changing the row/column info but while its generating data as a result its not generating it properly.

 

Current formula =IFERROR(INDEX(Schedule!$A$2:$A$210,AGGREGATE(15,6,1/((COUNTIFS('Export Report'!$G$2:$G$7529,$B4,'Export Report'!$B$2:$B$7529,Schedule!$A$2:$A$210)=0)/LEN(INDEX(Schedule!$B$2:$H$210,0,MATCH($B4,Schedule!$B$1:$H$1,0)))>0)*(ROW(Schedule!$A$2:$A$210)-ROW(Schedule!$A$1)),COLUMN()-COLUMN($B$4))),"")

 

Thanks in advance!

 

@jennjax1010 

 

You shall change the reference on day of the week as well, from $B4 on B$4

=IFERROR(
    INDEX(Schedule!$A$2:$A$208,
        AGGREGATE(15,6,
            1/((COUNTIFS('Export Report'!$G$2:$G$7529,B$4,'Export Report'!$B$2:$B$7529,Schedule!$A$2:$A$208)=0)/
                LEN(INDEX(Schedule!$B$2:$H$208,0,MATCH(B$4,Schedule!$B$1:$H$1,0)))>0)*
               (ROW(Schedule!$A$2:$A$208)-ROW(Schedule!$A$1)),ROW()-ROW($B$4)
       )
    ),
"")

Please check attached