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
jennjax1010's avatar
jennjax1010
Copper Contributor
Jun 12, 2019

SergeiBaklan  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!

 

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

    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

Resources