Forum Discussion

jennjax1010's avatar
jennjax1010
Copper Contributor
Jun 07, 2019

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...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 07, 2019

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.

jennjax1010's avatar
jennjax1010
Copper Contributor
Jun 07, 2019

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

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jun 07, 2019

    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.

    • jennjax1010's avatar
      jennjax1010
      Copper Contributor
      Jun 07, 2019

      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.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jun 07, 2019

        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

        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.

Resources