Forum Discussion

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jun 30, 2020

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
    Jul 01, 2020

    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
      Jul 01, 2020

      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)) )

       

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jul 01, 2020

        PeterBartholomew1 

        Even without LET() your code will be very similar, only with global names.

        My variant is

        =UNIQUE(
    INDEX(Range,
       MOD(SEQUENCE((COLUMNS(Range)-1)*(ROWS(Range)-1))-1,14)+2,
       INT( SEQUENCE((COLUMNS(Range)-1)*(ROWS(Range)-1),,(ROWS(Range)-1)*2)/
               (ROWS(Range)-1))
   )
)

        More interesting is spill for counts, here LET could be more useful. Without it

        =TRANSPOSE(
    MMULT(
       SEQUENCE(1,(COLUMNS(Range)-1)*(ROWS(Range)-1),,0),
       (
          INDEX(Range,
                MOD(SEQUENCE((COLUMNS(Range)-1)*(ROWS(Range)-1))-1,14)+2,
                INT( SEQUENCE((COLUMNS(Range)-1)*(ROWS(Range)-1),,(ROWS(Range)-1)*2)/
               (ROWS(Range)-1))
      )=TRANSPOSE(K6#))*
       (INDEX(Range, MOD(SEQUENCE((COLUMNS(Range)-1)*(ROWS(Range)-1))-1,14)+2, 1 )>=$L$3)*
       (INDEX(Range, MOD(SEQUENCE((COLUMNS(Range)-1)*(ROWS(Range)-1))-1,14)+2, 1 )<=$N$3)
   )
)

        Not sure how to combine both spills together.

         

Resources