Power Query Problems

How to Create a Consolidated Excel List of Unique Employee IDs

Maira Mehtab Oct 1, 2026 868 views

Question details

Consolidate multiple monthly attendance tables to create a dynamically expanding master sheet that lists unique employee IDs and retrieves their corresponding names using XLOOKUP.

How to Create a Consolidated Excel List of Unique Employee IDs

Product: Excel
Device & OS: not provided
Scenario: Merging attendance data across separate monthly sheets (e.g., January through April 2024) to extract a distinct list of employees and their details.
Observed behavior: The user requires a centralized, automatically updating consolidated list where duplicate IDs are removed and employee names are accurately matched.

Before you start

Ensure your monthly attendance data ranges are formatted as Excel Tables (Ctrl+T) and assign them clear, distinct names such as Jan2024, Feb2024, Mar2024, and Apr2024 via the Table Design tab.

Solution 1Recommended

Use Power Query to Append Tables and Extract Unique IDs

Power Query is the most robust and scalable method for consolidating multiple tables and removing duplicates, allowing for easy updates when new data is added.

Power Query allows you to stack (append) multiple tables on top of each other. Once appended, you can easily remove duplicate entries and output a clean, distinct list of IDs.

You can either merge the employee names directly within Power Query by linking a lookup table, or load the distinct list back to Excel and use an XLOOKUP formula.

Load Tables into Power Query

Navigate to the Data tab, click 'Get Data', choose 'From Other Sources', and select 'Blank Query'. Alternatively, select each source table and click 'From Table/Range' to load them into the Power Query Editor.

Append the Queries

In the Power Query Editor, go to the Home tab and click 'Append Queries as New'. Select 'Three or more tables' and add Jan2024, Feb2024, Mar2024, and Apr2024 to the 'Tables to append' box, then click OK.

Remove Duplicate IDs

Select the column containing the Employee IDs (Emp_ID) in the appended query. Right-click the column header and choose 'Remove Duplicates' to ensure each employee only appears once.

Load the Master List

Click 'Close & Load' on the Home tab to output the consolidated unique list into a new worksheet. In the adjacent column, you can now write your =XLOOKUP() formula to fetch employee names.

Use Power Query to Append Tables and Extract Unique IDs

Updating the Data: Power Query does not automatically recalculate like standard formulas. Whenever you add new data to the monthly tables, right-click the consolidated table and select 'Refresh'.

Solution 2

Use Dynamic Array Formulas (VSTACK and UNIQUE)

If you are using a newer version of Excel, combining VSTACK and UNIQUE functions provides a fully automated, formula-driven solution.

Efficient Data Tools

Easily Consolidate Multiple Tables with WPS Spreadsheet

WPS Office Spreadsheet offers full support for advanced dynamic array functions like UNIQUE and VSTACK, as well as built-in data consolidation tools, making it incredibly simple to generate unique employee lists from multiple monthly records.

1. Open Your Source Workbook: Launch WPS Spreadsheet and open the workbook containing your monthly attendance tables.
2. Apply the UNIQUE and VSTACK Formulas: In your summary sheet, simply type =UNIQUE(VSTACK(Jan2024[Emp_ID], Feb2024[Emp_ID])) to instantly extract a consolidated, distinct list of IDs.
3. Retrieve Employee Information: Use the built-in XLOOKUP function referencing your master lookup table to automatically populate employee names next to the dynamically generated IDs.

Fully compatible with Microsoft Excel file formats (.xlsx)Supports dynamic arrays and advanced lookup functions like XLOOKUPBuilt-in robust Data Consolidation utility for multi-sheet reportsLightweight, fast, and completely free to use

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my consolidated Power Query table updating automatically?

Power Query tables do not refresh in real-time as formulas do. To see updates after modifying your monthly source tables, you must right-click anywhere inside the consolidated table and select 'Refresh', or go to the Data tab and click 'Refresh All'.

Can I merge the employee names directly within Power Query instead of using XLOOKUP?

Yes. You can load your master Employee Names lookup table into Power Query as well. After appending the monthly tables and removing duplicates, use the 'Merge Queries' feature on the Home tab to join the appended table with the names table using the Emp_ID column.

What happens in Power Query if my monthly tables have different column layouts?

When you append queries, Power Query matches columns based on their exact header names. If a column exists in one table but not another, Power Query will still append the tables but will insert 'null' values for the missing data in the respective rows.

Why does my VSTACK formula return a #NAME? error?

The #NAME? error typically occurs if your version of Excel does not support dynamic array functions like VSTACK or UNIQUE, or if you have misspelled a table name inside the formula.

Excel & Data Problems