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 11, 2019

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.

jennjax1010's avatar
jennjax1010
Copper Contributor
Jun 11, 2019

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

It worked!!!!

  • 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

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

     

Resources