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 Session | Name |
| Dec-23 | A |
| Jun-23 | A |
| Dec-22 | A |
| Jun-23 | B |
| Dec-22 | B |
| Dec-22 | C |
| Jun-23 | D |
| Jun-22 | E |
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?
@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"))))
)
)
Please see attached workbook, if needed...