How to group range of cells into date ranges of "Multiple times per day", Weekly, Monthly etc

user_3405776 0
Viewed 43

I have a set of data that is shown as such:

User Report Month Day
User 1 Report Name 1 2019 Aug 20/08/2019
User 2 Report Name 1 2019 Aug 20/08/2019
User 1 Report Name 2 2019 Aug 21/08/2019
User 2 Report Name 3 2019 Aug 23/08/2019
User 1 Report Name 1 2019 Aug 24/08/2019
User 3 Report Name 2 2019 Aug 30/08/2019
User 3 Report Name 4 2019 Aug 30/08/2020

I am trying to perform some research into how often certain reports are run and group them into the following ranges:

  • Used multiple times a day
  • Used once daily
  • Used Weekly
  • Used Monthly
  • Used Yearly
  • Seldom Used

I don't need to which users are using the reports, just the number. I've tried countifs but so far i've only been able to count the number of uses per day by creating a new table and de-duplicating the values like so:

Report Month Day
Report Name 1 2019 Aug 20/08/2019
Report Name 2 2019 Aug 21/08/2019
Report Name 3 2019 Aug 23/08/2019
Report Name 1 2019 Aug 24/08/2019
Report Name 2 2019 Aug 30/08/2019
Report Name 4 2019 Aug 30/08/2020

Then using Countif: =COUNTIFS(A3:A8,A3,C3:C8,C3)

This formula is only giving me how many uses of each report per date shown in column C. To know what is used daily I also need to eyeball column C and make a best guess that reports are being activated on consecutive days so it would be great if there was a way to factor in weekends and holidays into a formula.

Any help will be appreciated thanks!

excel-formula
1 Answers

Here is a rather flexible solution that allows you to select different time periods and thresholds and then lists the 'reports' fitting in those selected criteria:

enter image description here

With the data array (excl. headers) named 'DataArray' and the dates column range named 'DateRange' then given the example in the screenshot above, the formulas are as follows.

in I12:

=LET(FilterArray,FILTER(DataArray,(DateRange>(I$2-I$3))*(DateRange<=I$2)),ReportUnique,UNIQUE(INDEX(FilterArray,0,2),FALSE,FALSE),CountBins,INT(I$3/I$5),StartBins,I$2-SEQUENCE(CountBins,1,I$5,I$5),EndBins,I$2-SEQUENCE(CountBins,1,0,I$5),IFERROR(SORT(FILTER(ReportUnique,TRANSPOSE(MMULT(SEQUENCE(1,CountBins*COUNTA(ReportUnique),1,0),IFERROR((TRANSPOSE(ReportUnique)=INDEX(ReportUnique,INT(SEQUENCE(CountBins*COUNTA(ReportUnique),1,0)/CountBins)+1))*TRANSPOSE(MMULT(SEQUENCE(1,ROWS(FilterArray),1,0),(INDEX(IF((INDEX(FilterArray,0,4)<=TRANSPOSE(EndBins))*(INDEX(FilterArray,0,4)>TRANSPOSE(StartBins)),INDEX(FilterArray,0,2)),SEQUENCE(ROWS(FilterArray)),TRANSPOSE(MOD(SEQUENCE(CountBins*COUNTA(ReportUnique),1,0),CountBins)+1))=TRANSPOSE(INDEX(ReportUnique,INT(SEQUENCE(CountBins*COUNTA(ReportUnique),1,0)/CountBins)+1)))+0)>=I$6)+0,0)))=CountBins)),"None"))

in J12:

=LET(FilterArray,FILTER(DataArray,(DateRange>(J$2-J$3))*(DateRange<=J$2)),ReportUnique,UNIQUE(INDEX(FilterArray,0,2),FALSE,FALSE),CountBins,INT(J$3/J$5),StartBins,I$2-SEQUENCE(CountBins,1,I$5,I$5),EndBins,I$2-SEQUENCE(CountBins,1,0,I$5),IFERROR(SORT(FILTER(ReportUnique,TRANSPOSE(MMULT(SEQUENCE(1,CountBins*COUNTA(ReportUnique),1,0),IFERROR((TRANSPOSE(ReportUnique)=INDEX(ReportUnique,INT(SEQUENCE(CountBins*COUNTA(ReportUnique),1,0)/CountBins)+1))*TRANSPOSE(MMULT(SEQUENCE(1,ROWS(FilterArray),1,0),(INDEX(IF((INDEX(FilterArray,0,4)<=TRANSPOSE(EndBins))*(INDEX(FilterArray,0,4)>TRANSPOSE(StartBins)),INDEX(FilterArray,0,2)),SEQUENCE(ROWS(FilterArray)),TRANSPOSE(MOD(SEQUENCE(CountBins*COUNTA(ReportUnique),1,0),CountBins)+1))=TRANSPOSE(INDEX(ReportUnique,INT(SEQUENCE(CountBins*COUNTA(ReportUnique),1,0)/CountBins)+1)))+0)<J$6)+0,0)))=CountBins)),"None"))

in K12:

=LET(FilterArray,FILTER(DataArray,(DateRange>(K$2-K$3))*(DateRange<=K$2)),ReportUnique,UNIQUE(INDEX(FilterArray,0,2),FALSE,FALSE),CountBins,INT(K$3/K$5),StartBins,I$2-SEQUENCE(CountBins,1,I$5,I$5),EndBins,I$2-SEQUENCE(CountBins,1,0,I$5),IFERROR(SORT(FILTER(ReportUnique,TRANSPOSE(MMULT(SEQUENCE(1,CountBins*COUNTA(ReportUnique),1,0),IFERROR((TRANSPOSE(ReportUnique)=INDEX(ReportUnique,INT(SEQUENCE(CountBins*COUNTA(ReportUnique),1,0)/CountBins)+1))*TRANSPOSE(MMULT(SEQUENCE(1,ROWS(FilterArray),1,0),(INDEX(IF((INDEX(FilterArray,0,4)<=TRANSPOSE(EndBins))*(INDEX(FilterArray,0,4)>TRANSPOSE(StartBins)),INDEX(FilterArray,0,2)),SEQUENCE(ROWS(FilterArray)),TRANSPOSE(MOD(SEQUENCE(CountBins*COUNTA(ReportUnique),1,0),CountBins)+1))=TRANSPOSE(INDEX(ReportUnique,INT(SEQUENCE(CountBins*COUNTA(ReportUnique),1,0)/CountBins)+1)))+0))+0,0)))>=K$6)),"None"))

and in L12:

=LET(FilterArray,FILTER(DataArray,(DateRange>(L$2-L$3))*(DateRange<=L$2)),ReportUnique,UNIQUE(INDEX(FilterArray,0,2),FALSE,FALSE),CountBins,INT(L$3/L$5),StartBins,I$2-SEQUENCE(CountBins,1,I$5,I$5),EndBins,I$2-SEQUENCE(CountBins,1,0,I$5),IFERROR(SORT(FILTER(ReportUnique,TRANSPOSE(MMULT(SEQUENCE(1,CountBins*COUNTA(ReportUnique),1,0),IFERROR((TRANSPOSE(ReportUnique)=INDEX(ReportUnique,INT(SEQUENCE(CountBins*COUNTA(ReportUnique),1,0)/CountBins)+1))*TRANSPOSE(MMULT(SEQUENCE(1,ROWS(FilterArray),1,0),(INDEX(IF((INDEX(FilterArray,0,4)<=TRANSPOSE(EndBins))*(INDEX(FilterArray,0,4)>TRANSPOSE(StartBins)),INDEX(FilterArray,0,2)),SEQUENCE(ROWS(FilterArray)),TRANSPOSE(MOD(SEQUENCE(CountBins*COUNTA(ReportUnique),1,0),CountBins)+1))=TRANSPOSE(INDEX(ReportUnique,INT(SEQUENCE(CountBins*COUNTA(ReportUnique),1,0)/CountBins)+1)))+0))+0,0)))<L$6)),"None"))

Here is another example screnshot with different selections in the input cells: Link

Note that there is no special recognition of weekends or holidays in here yet. I would suggest either reverting to VBA or an extra sheet just for those calculations to include weekend and holiday adjustments.

user_171587030
Related