Forum Discussion

bbsin's avatar
bbsin
Iron Contributor
Aug 08, 2024
Solved

how to find if the person attend the session how many time?

Hi I like to find the unique number of person participated in the activities throughout the year as 1 person will attend the same activity for many days/months.

the date col is running through the whole date by mm/dd/yy for the activities

 

Can it show by month and also by whole year to display the activities, staff type , and unique person attended in number?

 

DateActivitiesStaff Typeemail
 Afull Time 
 BContract 
 CPart Time 
 D  
 E  

 

 

Thank you

Formulas and Functions
  • bbsin's avatar
    bbsin
    Aug 14, 2024

    Thanks both, I will try

    I also need show the email based on event & month, how many time in the month and whole year he/she attended to find the unique # attended. how to get it? Thank you


  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Aug 14, 2024

    bbsin 

    I might well have got the wrong end of the stick here and calculated how many distinct email addresses appear in each category rather than how many occurrences are there of any given email address.  Nevertheless, interesting things are possible using the latest versions of Excel 365.  For example

    was generated by the formula

    = LET(
    ToListĪ»,  LAMBDA(sep, opt, LAMBDA(arr, TEXTJOIN(sep, opt, arr))),
    distinct, UNIQUE(HSTACK(TEXT(date, "mmm"), event, email)),
    m,        CHOOSECOLS(distinct, 1),
    v,        CHOOSECOLS(distinct, 2),
    e,        CHOOSECOLS(distinct, 3),
    PIVOTBY(v, m, e, ToListĪ»(CHAR(10), 0), , 0, , 0)
  )
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Aug 12, 2024

    bbsin 

    Currently, this approach is only possible with Excel 365 insider beta channel.

    = LET(
    distinct, UNIQUE(HSTACK(MONTH(date), event, email)),
    m, CHOOSECOLS(distinct, 1),
    e, CHOOSECOLS(distinct, 2),
    PIVOTBY(e, m, m, COUNT)
  )

    First it determines the unique combinations of month, event and email present within the list.  The row  count for each combination of event and month is determined by the new PIVOTBY function.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 08, 2024

    bbsin 

    Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

Resources