Forum Discussion

rando230's avatar
rando230
Copper Contributor
Apr 18, 2024

Formula brainstorming

Hi all,

 

I have a set of data that requires me to set up personnel that is active/inactive.

 

The dataset is about a set of professional that took exam on certain timeframe.

 

The dataset sample are shown as follow

Exam SessionName
Dec-23A
Jun-23A
Dec-22A
Jun-23B
Dec-22B
Dec-22C
Jun-23D
Jun-22E

 

The expected output are attached as image below.

 

As you can see, Candidate C and E are consider as inactive because they are not taking exam on dec 23 and jun 23. However, since this excel is going to be update constantly, the date of dec 23 will move to jun 24 when the next result comes in. hence there will be a new col and the condition will change according to that.

 

With the following condition, how should I create my formula for it in excel and even better, for power query instead?

excel
Power BI
  • djclements's avatar
    djclements
    Bronze Contributor
    Apr 18, 2024

    rando230 If you have Excel for MS365, one possible dynamic array solution could be:

     

    =LET(
    data, A2:B1000,
    col_1, INDEX(data,, 1),
    col_2, INDEX(data,, 2),
    dates, TOROW(SORT(UNIQUE(col_1),, -1 ), 1),
    names, TOCOL(SORT(UNIQUE(col_2)), 1),
    results, COUNTIFS(col_1, dates, col_2, names),
    VSTACK(
        HSTACK("Name", dates, "Status"),
        HSTACK(names, IF(results, "x", ""), BYROW(TAKE(results,, 2), LAMBDA(r, IF(OR(r), "Active", "Inactive"))))
    )
)

     

    Sample Results

     

    Please see attached workbook, if needed...

Resources