How to Fill Group Titles Down a New Column in WPS Spreadsheets
Question details
The user needs to repeat group titles in a new column so that each staff or detail row under a group heading shows the correct group title after PDF import.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Data imported from a PDF contains group title rows followed by staff or detail rows, and the user wants to fill those group titles down into a new column.
- Observed behavior
- Group titles appear only once above their related rows, leaving blank cells under each title in the new column.
Open the imported PDF data in WPS Spreadsheets and identify the column that contains group titles and staff or detail rows. If you plan to test formulas, keep a duplicate of the original sheet so you can compare the filled results.
Fill group titles down with a lookup formula in WPS Spreadsheets
Use this when you want each staff or detail row to automatically show the most recent group title above it.
This formula checks whether the current row contains "Staff" and then retrieves the last cell above that contains "Group". Adjust the search words to match the exact labels in your imported PDF data.
Click the column header to the right of your imported data, then click Home > Insert > Insert Sheet Column and type a heading such as Group Title.
In the first output cell next to the first staff row, enter =IF(ISNUMBER(SEARCH("Staff",B2)),LOOKUP(2,1/(ISNUMBER(SEARCH("Group",$B$2:B2))),$B$2:B2),"") and press Enter.
Select the formula cell and drag the fill handle down through all staff or detail rows to repeat each group title automatically.
Repeat group titles manually for small or one-off datasets
Choose this when the dataset is short and you only need to fill the titles once.
Use Power Query for refreshable PDF imports in Microsoft Excel
Consider this if you repeatedly import PDF data and need a query that refreshes the grouped layout automatically.
Repeat group titles down a column with WPS Spreadsheets
Open your PDF-imported workbook in WPS Spreadsheets, add a helper column, and use a lookup formula to fill every staff or detail row with its group title. No additional add-ins are needed.
- 1. Open the imported data: Launch WPS Spreadsheets, click Menu > Open, and open the PDF-imported .xlsx file or use Data > Import to bring in the PDF data.
- 2. Add the fill column: Click the column header next to the imported data, then click Home > Insert > Insert Sheet Column and enter a heading such as Group Title.
- 3. Enter the lookup formula: In the first output cell, enter =IF(ISNUMBER(SEARCH("Staff",B2)),LOOKUP(2,1/(ISNUMBER(SEARCH("Group",$B$2:B2))),$B$2:B2),"") and press Enter.
- 4. Fill the remaining rows: Drag the fill handle down through all staff or detail rows to repeat each group title automatically.
- 5. Review and save: Check that each staff or detail row shows the correct group title, then click Menu > Save As and choose Excel Workbook (.xlsx).
Frequently Asked Questions
Why does the formula return blank cells for group title rows?
The formula deliberately shows a group title only on staff or detail rows. If you want the title on every row, remove the IF condition and use the LOOKUP part alone, or copy the group title down to all rows.
Can I use this method if my imported labels are not exactly "Group" and "Staff"?
Yes. Replace "Group" and "Staff" in the SEARCH function with the actual text from your PDF import. If the rows are identified by blank cells or different values, adjust the condition accordingly.
Does this work in WPS Spreadsheets or only in Excel 365?
The recommended LOOKUP/ISNUMBER/SEARCH formula works in WPS Spreadsheets and does not require Excel 365 dynamic arrays. If your version prompts for an array formula, press Ctrl+Shift+Enter.
What is the best way to update the filled column after importing a new PDF?
For a WPS Spreadsheets workflow, copy the formula down to cover the newly added rows. For frequent, refreshable imports, use Power Query in Microsoft Excel and refresh the query after each import.
