Forum Discussion

14 Replies

  • sandeeptikait 

    This is another modern dynamic array solution.  One form that I like is to use the beta-release function LET to generate a self-documenting formula

     

     

    = LET(
      firstRow, XLOOKUP(Start, Date, NameRange, ,1),
      finalRow, XLOOKUP(End, Date, NameRange, ,-1),
      filteredRange, firstRow:finalRow,
      COUNTIFS(filteredRange, Name) )

     

     

    It uses the fact that XLOOKUP returns a range to build the filtered range to search.  Without LET, one would have

     

    = COUNTIFS(XLOOKUP(Start,Date,NameRange, ,1):XLOOKUP(End,Date,NameRange, ,-1), Name)

     

    • sandeeptikait's avatar
      sandeeptikait
      Copper Contributor

      PeterBartholomew1 Thanks for your response. You solution for counting the number of occurrence is fine. But how would I get the unique records of names, if there are hundreds of names in the list.

      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor

        sandeeptikait 

        The UNIQUE function requires the table to be unpivoted and that is a somewhat messy process.

        = LET(
          m, ROWS(NameTable),
          n, COLUMNS(NameTable)-1,
          k, SEQUENCE(m*n),
          rowNum, 1+QUOTIENT(k-1,n),
          colNum, 1+MOD(k-1,n),
          list, INDEX(NameTable, rowNum, 1+colNum),
          SORT(UNIQUE(list)) )

        Without the LET function, the process is worse.  One would either have to implement each line as a separate named formula using Name Manager or, worse still, write it as a nested formula.

         

        There are (paid) function libraries that can help.  Charles Williams's FastExcel includes an UNPIVOT function which reduces the process of determining a sorted list of distinct names to

        = LET(
          unpivoted, UNPIVOT(NameTable,,1,,,0),
          list, INDEX(unpivoted, ,3),
          SORT(UNIQUE(list)) )

         

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    sandeeptikait Offering a PowerQuery approach, using three Named Ranges (names, Start and End). Although I still consider myself a relative newbie at the subject, I'm rather pleased with the outcome :), but at the same time convinced that it probably can be done better.

     

    Results are shown in the small table in N5:O11. Change the Start and/or End dates. Select the Data ribbon and press"Refresh All" for an updated result.

     

     

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor

      Riny_van_Eekelen 

      Looks good to me. About the only point where I would differ is in that I would have converted the named range to a Table before starting with PQ. That both makes the input dynamic and saves the promote headers step in the M code.

  • mtarler's avatar
    mtarler
    Silver Contributor

    sandeeptikait  Here is a formula that works:

    =SUMPRODUCT(--($B$2:$B$16>=$L$3)*($B$2:$B$16<=$N$3),(--($C$2:$C$16=$K6)+($D$2:$D$16=$K6)+($E$2:$E$16=$K6)+($F$2:$F$16=$K6)+($G$2:$G$16=$K6)+($H$2:$H$16=$K6)))

    I wonder if SergeiBaklan  or someone else might chime in with a 'better' answer.

    I'm pretty sure some of those new array functions like FILTER could help out here.

    Maybe I'll get access to them some day...

     

    • sandeeptikait's avatar
      sandeeptikait
      Copper Contributor

      mtarler  Thanks for your response. You solution for counting the number of occurrence is fine. But how would I get the unique records of names, if there are hundreds of names in the list.

      • mtarler's avatar
        mtarler
        Silver Contributor

        sandeeptikait  Here is a formula for finding unique names without the new functions (I hope it is ok that it isn't alphabetical):

        =IFERROR(OFFSET($A$1,MOD(AGGREGATE(15,7, (ROW($C$3:$H$16)+100000*COLUMN($C$3:$H$16))/(--(COUNTIF(J$5:J10,$C$3:$H$16)=0)), 1),100000)-1,INT(AGGREGATE(15,7, (ROW($C$3:$H$16)+100000*COLUMN($C$3:$H$16))/(--(COUNTIF(J$5:J10,$C$3:$H$16)=0)), 1)/100000)-1),"")

         

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor

      mtarler , IMHO - "best solution" is quite individual and depends on criterion. From my point of view that's any one which works and which concrete person understands better. Thus using such pattern potentially the person could save a lot of time on deployment and maintenance. That in particular means that for the same person today one solution could be considered as best, and in a while another one, assuming both do exactly the same job. Sorry for some philosophy, with your experience that could be not new.

      sandeeptikait , if play with modern Excel functionality, formula could be

      =SUM(--(
        INDEX(Range,
           SEQUENCE( COUNTIFS(INDEX(Range,0,1),">="&$L$3,INDEX(Range,0,1),"<="&$N$3),1,2),
           SEQUENCE(1,COLUMNS(Range)-1,2,1)
        )=K6)
      )

      where the "Range" is named range for entire source data. If extract unique names and return counts as spill it'll be more complicated.

       

      Most probably other formula (without Power Query) solutions exist.

Resources