How to Search, Filter, and Copy Qualitative Data into a Master Table in WPS Office Excel
Question details
The user needs to consolidate coded qualitative data from multiple transcript tables into a master table without filtering one code at a time.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A researcher or analyst is working with qualitative data stored across multiple transcript tables and needs to move coded segments into a single master table.
- Observed behavior
- Applying filters one code at a time across multiple transcript tables is repetitive, slow, and error-prone.
Before you begin, make sure each transcript table uses consistent column names such as Transcript, Code, Text, and Category, and that the data is formatted as an Excel Table or a named range for easier appending in Power Query.
Use Power Query to Append Transcript Tables and Filter All Required Codes at Once
Best for recurring consolidation: it combines all transcript tables into one query and filters multiple codes in a single step.
Power Query is the most reliable way to consolidate and filter qualitative data because it keeps the original tables intact and refreshes the master table when source data changes.
In WPS Office Spreadsheets, open the workbook containing the transcript tables. Click the Data tab, choose Get Data > From Table/Range or From Workbook, and select each transcript table to combine them with Append Queries.
In the Power Query Editor, select the Code column and use the filter dropdown. Choose Text Filters > Equals or use the filter search box to select all the required codes at once, then click OK so the query keeps only the matching rows.
Click Close & Load To, choose Table, select the master table worksheet or a new worksheet, and confirm to load the appended and filtered qualitative data into the master table.
Use Advanced Filter with an OR Criteria Range for a One-Time Copy
Useful for a quick, one-time consolidation when you do not want to build a reusable query.
Consolidate Coded Qualitative Data in WPS Office Spreadsheets
Use WPS Office Spreadsheets to append multiple transcript tables, filter all required codes in one workflow, and load the results into a master table for faster qualitative data analysis.
- 1. Open WPS Office Spreadsheets: Launch WPS Office and open the workbook that contains the transcript tables you want to consolidate.
- 2. Combine source tables with the Data tab: Select the Data tab, use Get Data or Import Data to load each transcript table into the query editor, and choose Append to combine them.
- 3. Filter all required codes at once: In the query editor, select the Code column and use the filter dropdown to choose the required codes together, keeping only matching rows.
- 4. Load results into the master table: Choose Close & Load To, select the master table location, and confirm to complete the consolidation.
Frequently Asked Questions
How do I combine multiple transcript tables in WPS Office Excel?
Open the Data tab in WPS Office Spreadsheets, use Get Data or Import Data to load each transcript table into a query editor, and then append the tables before applying filters.
Can I filter several qualitative codes at once instead of one by one?
Yes. In the Power Query Editor or Advanced Filter criteria range, select or list all required codes in the Code column, and the filter keeps every matching row in one operation.
Do my transcript tables need the same columns?
Not exactly, but consistent column names such as Transcript, Code, Text, and Category make appending and filtering much easier. Use Transform tools to rename or remove inconsistent columns before combining.
Will this work with Microsoft Excel .xlsx qualitative data?
Yes. WPS Office Spreadsheets opens and saves .xlsx files, and it can import Excel workbook data, so you can work with existing Microsoft Excel qualitative data without converting the file format.
Can I refresh the master table after adding new transcript data?
If you use Power Query to combine and filter source tables, you can refresh the query from the Data tab so the master table picks up new or changed transcript rows.
