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 samp...
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