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

Resources