How to Combine Multiple Training Rows into One Person Record in WPS Office Without Power Query
Question details
The user needs to consolidate multiple Power Query rows for each person and training module into a single row containing the required module, date, role, validator, and related information.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- Managing training records where each person has multiple module rows and the goal is to create one person record.
- Observed behavior
- Power Query returns multiple rows per person, and ribbon tools do not directly produce the desired one-row layout.
Identify the person identifier column and confirm the maximum number of training modules per person. Decide which module fields, such as date, role, validator, and related information, must appear in the final consolidated row.
Consolidate rows with COUNTIF, TEXTJOIN and IF formulas in WPS Spreadsheets
Use this method when each person may have a different number of training modules and you need a compact, one-row-per-person summary without Power Query.
Because Power Query is a Microsoft Excel feature, WPS Office can produce a similar one-row-per-person result using a helper column and lookup/formula combinations. This approach keeps the original data unchanged and is easy to refresh when new training rows are added.
In WPS Spreadsheets, insert a new column next to the person column and enter the formula =COUNTIF($A$2:A2,A2) in the first data row. Drag the fill handle down to number each training module per person.
Copy the person column to a new sheet or blank area, then click Data > Remove Duplicates to keep only one row per person.
In the first cell for module 1, use =IFERROR(INDEX(module_column,MATCH(1,($person_column=$person_cell)*(helper_column=1),0)),"") and press Ctrl+Shift+Enter if using an array formula. Adjust the helper number and column references for each module field.
For fields such as role or validator where several values may belong to one person, use a TEXTJOIN formula, for example =TEXTJOIN(", ",TRUE,IF($person_column=$person_cell,role_column,"")) and press Ctrl+Shift+Enter to create a delimited list.
Build a PivotTable summary by person and module in WPS Spreadsheets
Use this method when you want a visual, interactive summary and can place module names as separate column headers.
Handle Training Row Consolidation in WPS Office Without Power Query
If your current Power Query workflow lives in Microsoft Excel, WPS Office gives you a free, lightweight alternative for opening, cleaning, and summarizing the same training data. WPS Spreadsheets supports familiar formulas, PivotTables, and Excel-compatible workbook formats so you can consolidate rows without losing your original tables.
- 1. Open your Excel training workbook in WPS Office: Download and install WPS Office, then open the Excel workbook that contains the Power Query training data. WPS Spreadsheets preserves the original .xlsx structure and formatting.
- 2. Keep or copy the training data to a WPS sheet: Select the training rows and copy them into a clean WPS Spreadsheet sheet so you can apply formulas without affecting the source query.
- 3. Apply consolidation formulas or a PivotTable: Use the COUNTIF/INDEX/MATCH/TEXTJOIN formula method or Insert > PivotTable to group the data by person and module as described above.
- 4. Save and share the workbook: Click Menu > Save As and choose an Excel-compatible format such as .xlsx or WPS native format before sharing the consolidated training records.
Frequently Asked Questions
Does WPS Office include Power Query like Microsoft Excel?
No, WPS Office does not include the Microsoft Power Query Editor. You can use WPS Spreadsheets formulas, Remove Duplicates, PivotTable, and Text to Columns to clean and consolidate similar training data.
How do I create one row per person if the number of training modules varies?
Add a helper column that counts each person's module row with COUNTIF, create a unique person list, then use INDEX/MATCH with the helper column to pull each module value into a separate column. For multiple text values, use TEXTJOIN with IF as an array formula.
Can I open and edit my existing Excel Power Query workbook in WPS Office?
Yes, WPS Spreadsheets can open .xlsx workbooks and preserve most data, formulas, and formatting. However, Power Query queries themselves are not editable in WPS Office; you can work with the resulting data or rebuild the transformation with WPS tools.
What is the easiest way to create a one-person-per-row summary in WPS Office?
If your modules are limited and consistent, select the data and click Insert > PivotTable, then place the person field in Rows and the module field in Columns. For exact text values and variable modules, use the COUNTIF and INDEX/MATCH formula method.
Will WPS Office formulas be compatible with Excel if I share the file?
Yes, WPS Office supports commonly used Excel functions such as COUNTIF, INDEX, MATCH, IF, and TEXTJOIN. The generated .xlsx file can be opened and further edited in Microsoft Excel.
