Jun 07 2019 07:03 AM
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.
Jun 07 2019 07:34 AM
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.
Jun 07 2019 07:59 AM
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..
Jun 07 2019 08:20 AM
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.
Jun 07 2019 08:56 AM
@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.
Jun 07 2019 10:18 AM
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.
Jun 07 2019 10:37 AM
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! :)
Jun 07 2019 10:46 AM
@jennjax1010 , I may explain each formula in details, but first let agree if that's what you are looking for.
Jun 10 2019 06:13 AM
@Sergei Baklan Will do, trying them out today! :)
Jun 10 2019 11:55 AM
@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))),"")
Jun 10 2019 01:41 PM
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
otherwise the formula is to be adjusted. Or, you may start from B4 and after that remove/insert columns and/or rows as required.
Jun 11 2019 05:26 AM - edited Jun 11 2019 05:31 AM
@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!
Jun 11 2019 05:44 AM
@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.
Jun 11 2019 06:24 AM
@Sergei Baklan You are amazing :) Attaching a sample with just a few rows of data...same formula..
Jun 11 2019 08:29 AM
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.
Jun 11 2019 08:32 AM - edited Jun 11 2019 08:33 AM
@Sergei Baklan Ahhh!! I wasnt aware, thank you so much! Making note of that :)
It worked!!!!
Jun 11 2019 08:41 AM
Great
Jun 12 2019 12:21 PM
@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!
Jun 14 2019 09:10 AM
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
Jun 17 2019 10:31 AM
@Sergei Baklan Thank you will give it a go :)