Keep FILTER Results and User Comments Matched to the Correct Customer in WPS Spreadsheets
Question details
Prevent user comments and feedback from shifting to the wrong customer when Excel FILTER results change order or rows are removed.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user maintains a dynamic filtered customer list and needs comments to remain attached to the correct customer even when the FILTER output changes.
- Observed behavior
- Comments entered beside a dynamic FILTER result shift to the wrong customer when the source order changes or rows are removed.
Confirm that every customer has a unique CustomerCode and that both the source data and comments table include that code. Also make sure your WPS Spreadsheets version supports dynamic array functions such as FILTER and XLOOKUP.
Store Comments in a Separate Table and Display Them with XLOOKUP
Use this method when comments must stay with a customer even if the FILTER result reorders, hides, or removes rows.
The FILTER function returns a dynamic spilled array. When the source data changes, the spilled row positions change, but manually placed comments beside those rows do not move with the customer.
A separate comments table keyed by a unique CustomerCode lets WPS Spreadsheets look up the comment for each row instead of relying on row position.
In WPS Spreadsheets, click the + sheet tab to add a new sheet and name it Comments. Enter the column headers CustomerCode and Comment, then fill in the unique customer code and comment for each customer.
In the filtered workbook, click the first empty cell immediately to the right of the FILTER spill range. Type the header Comment, and in the cell below it enter =XLOOKUP(A2#,Comments!$A$2:$A$100,Comments!$B$2:$B$100,""). Change A2# to the CustomerCode column inside your FILTER spill range and adjust the Comments ranges to your data.
Change the FILTER criteria or edit the source data, then confirm that the customer codes and their comments remain attached and move together. If a customer is filtered out, the XLOOKUP result clears automatically.
Add the Comment Column to the Master Source Table Before Filtering
Use this alternative when you want comments stored directly with the master customer records and included in the same FILTER output.
Keep Customer Comments Correctly Matched in WPS Spreadsheets
Use a separate comments table and XLOOKUP in WPS Spreadsheets to attach feedback to a unique CustomerCode instead of a moving row position. The FILTER results and comments then stay synchronized even when customer order changes or rows are removed.
- 1. Open WPS Spreadsheets and your workbook: Launch WPS Office, click Spreadsheets, and open the .xlsx file that contains the FILTER results.
- 2. Create a separate Comments sheet: Click the + sheet tab, name it Comments, and add CustomerCode and Comment columns.
- 3. Enter the XLOOKUP comment formula: Click the cell immediately to the right of the FILTER spill, type =XLOOKUP(A2#,Comments!$A$2:$A$100,Comments!$B$2:$B$100,""), and adjust the CustomerCode column and ranges to match your workbook.
- 4. Confirm comments move with customers: Change a filter condition or add/remove rows from the source data. Verify that each comment still appears next to its correct customer.
Frequently Asked Questions
Why do comments typed next to FILTER results move to the wrong customer?
FILTER returns a dynamic spilled array, so row positions change when source data is reordered or filtered out. Text typed in adjacent cells does not move with the spill, which causes comments to misalign. Store comments by a unique CustomerCode and retrieve them with XLOOKUP instead.
Can I stop this without creating a separate comments sheet?
Yes. Add the Comment column to the master source table and include that column in the FILTER array argument. The comment then travels with each customer record as part of the filtered output.
Does WPS Spreadsheets support XLOOKUP and FILTER?
Yes. WPS Spreadsheets supports XLOOKUP and FILTER in current versions. Open the workbook, click the formula bar, and type the function; if your version does not recognize them, update WPS Office first.
What happens when a customer has no comment?
Use the if_not_found argument in XLOOKUP. For example =XLOOKUP(A2#,Comments!$A$2:$A$100,Comments!$B$2:$B$100,"") returns an empty string instead of an #N/A error.
How do I make the comments update automatically when the filter changes?
Use a dynamic spill reference such as A2# in the XLOOKUP formula. The formula then recalculates automatically for the entire FILTER result range.
