Power Query Problems

How to Identify Active and Inactive Candidates in Excel and Power Query

Adam DavisAdam Davis Nov 11, 2026 870 views

Question details

The user needs to create a dynamic solution to track candidate participation across exam sessions, labeling those in the latest sessions as 'Active' and others as 'Inactive'.

How to Identify Active and Inactive Candidates in Excel and Power Query
Product
Excel / Power Query
Device & OS
not provided
Scenario
Managing candidate exam records where new sessions are continuously added, requiring automatic status updates without manual recalculation.
Observed behavior
A system that automatically groups candidates, sorts by the newest sessions, and accurately outputs 'Active' or 'Inactive' status based on participation in the two most recent sessions.
Before you start

Ensure you have an Office version that supports Dynamic Array functions (such as Microsoft 365) or verify that your Power Query add-in is enabled and updated.

Solution 1Recommended

Use Excel Dynamic Array Functions

Utilize modern Excel dynamic array functions like LET, UNIQUE, and SORT to automatically calculate and spill candidate status.

This approach uses the LET function to define variables for your data range, extracting unique dates and names, and then checks attendance using COUNTIFS. It dynamically builds a results table with headers and calculates if a candidate attended either of the two most recent sessions.

1
Define your data range

Identify the columns containing your exam dates and candidate names. For example, assume your dates are in Column A and names in Column B (A2:B1000).

2
Apply the LET formula

Select a blank cell where you want the output table to begin and input the formula: =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"))))))

3
Verify the spilled results

Press Enter. The formula will automatically spill the data into neighboring cells, creating a matrix of candidates, their session attendance marked with 'x', and their calculated 'Active' or 'Inactive' status.

Use Excel Dynamic Array Functions
Version Compatibility: Functions like VSTACK, HSTACK, TOROW, and TOCOL require Excel for Microsoft 365 or newer versions that support dynamic arrays.
Advanced Data Analysis

Manage Candidate Records Dynamically with WPS Spreadsheet

WPS Spreadsheet offers powerful data processing capabilities, including full support for modern dynamic array functions and advanced data filtering. Easily calculate candidate statuses and automate your reporting without heavy manual work.

  1. 1. Open your dataset: Launch WPS Spreadsheet and open the .xlsx file containing your candidate and exam session data.
  2. 2. Apply dynamic formulas: Select the target cell for your new table and paste the provided LET formula to automatically generate the active/inactive matrix.
  3. 3. Format and highlight: Use WPS Spreadsheet's Conditional Formatting from the Home tab to automatically highlight 'Active' candidates in green for better visibility.
Full compatibility with Microsoft Excel formulas and file formats (.xlsx).Built-in support for dynamic array functions to simplify complex calculations.Lightweight and fast, even when handling thousands of candidate records.Free to use with an intuitive, tabbed interface for seamless workflow.
microsoft office alternative - wps office

Frequently Asked Questions

Why is my dynamic array formula returning a #NAME? error?

This usually happens if you are using an older version of Excel that does not support newer functions like LET, VSTACK, or HSTACK. You will need a compatible version like Excel for Microsoft 365 or WPS Office to use these specific formulas.

How can I change the formula to check the last 3 sessions instead of 2?

In the LET formula provided, locate the TAKE function segment: TAKE(results,, 2). Change the '2' to '3' to evaluate the candidate's participation across the three most recent exam sessions.

Does Power Query update automatically when new exam sessions are added?

Power Query does not update in real-time. Whenever you add new exam sessions to your source table, you must right-click the loaded Power Query output table and select 'Refresh', or go to the Data tab and click 'Refresh All' to calculate the new statuses.

Can I highlight active and inactive candidates with different colors?

Yes. Select the 'Status' column in your generated table, navigate to Home > Conditional Formatting > Highlight Cells Rules, and create a rule where cell value equal to 'Active' is formatted with green fill, and 'Inactive' with red fill.