Create a Master Time List and Match Excel Data by Time in WPS Spreadsheets
Question details
The user needs to combine time values from multiple part columns into a sorted unique master time list, then return matching values from each part column while leaving unmatched rows blank.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- You have time-based data split across multiple part columns or Excel tables and need to consolidate and match records by time.
- Observed behavior
- A sorted unique master time list is required, with matching values retrieved for each part and unmatched time slots displayed as blank cells rather than errors or zeros.
Confirm that your time values are stored as actual time or date-time values rather than text. Convert each part range into a table named PartA, PartB, and so on, or prepare the exact worksheet ranges to use in the formulas.
Use SORT, UNIQUE, VSTACK, and XLOOKUP to create the master time list and match data
Best when you want a formula-driven report that updates automatically and leaves unmatched rows blank.
This method uses dynamic array functions in WPS Spreadsheets. If your data is not in Excel tables, replace PartA[Time] with A2:A100 and PartA[Value] with B2:B100, then adjust the references for each part column.
In WPS Spreadsheets, open the workbook that contains the part time columns, select a blank cell where the master list should start, and enter =SORT(UNIQUE(VSTACK(PartA[Time],PartB[Time]))). Press Enter to spill the sorted unique time values.
In the cell next to the first master time, such as B2 if the master list starts in A2, enter =XLOOKUP(A2,PartA[Time],PartA[Value],"") and press Enter. The empty-string fourth argument leaves the cell blank when no matching time exists.
Copy the XLOOKUP formula to the next columns or create similar formulas for PartB, PartC, and any other parts. Update the table or range references so each value column is matched against the master time in A2.
Create a Master Time List and Match Excel Data by Time in WPS Spreadsheets
WPS Spreadsheets gives you dynamic array tools to combine multiple time columns into one sorted unique list, then use XLOOKUP to pull matching part values while leaving unmatched rows blank.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open WPS Spreadsheets, and load the workbook that contains the part time and value columns.
- 2. Generate the master time list: Select a blank cell and enter =SORT(UNIQUE(VSTACK(PartA[Time],PartB[Time]))) to create a sorted unique list of all time values.
- 3. Match Part A values: In the adjacent cell, enter =XLOOKUP(A2,PartA[Time],PartA[Value],"") and press Enter to return the matching value for the first master time.
- 4. Complete the other part columns: Drag or copy the XLOOKUP formula across the remaining part columns, updating the table or range reference for each part.
Frequently Asked Questions
Can I use these formulas if my PartA and PartB data is not in an Excel table?
Yes. Replace PartA[Time] and PartA[Value] with worksheet ranges such as A2:A100 and B2:B100. Use the same range size for each part and adjust references when copying formulas.
Why does XLOOKUP return 0 instead of a blank cell?
The fourth argument must be an empty string, entered as "", to return a blank. If the fourth argument is omitted or set to 0, WPS Spreadsheets returns 0 for unmatched time values.
Do SORT, UNIQUE, VSTACK, and XLOOKUP work in WPS Spreadsheets?
Recent WPS Office versions support these dynamic array functions. If you see a #NAME? error, update WPS Office or check that the function names are spelled correctly.
What should I do if time values from different parts do not match exactly?
Make sure both columns use the same time or date-time format and that there are no hidden seconds or text entries. Use the TEXT or TIME function to standardize values before creating the master list.
How do I keep unmatched rows blank when the master list is sorted?
The master list is generated independently with SORT and UNIQUE. Each XLOOKUP uses "" as the if_not_found argument, so times that are missing from a part column appear blank rather than as errors.
