How to Combine Multiple Customer Rows into One Row in WPS Office Excel with a PivotTable
Question details
The user needs to consolidate multiple rows for the same customer into one row in WPS Office Excel while preserving the date associated with each product topic.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A table records product contacts or dates on different days, creating multiple rows for each customer. The user wants a one-row-per-customer summary.
- Observed behavior
- The current table shows each customer repeated across many rows, so the product topics and their dates are split instead of appearing in separate columns.
Make sure the source table uses consistent headers for the customer, product topic, and date columns and does not contain merged cells. This helps the PivotTable group every record correctly.
Create a PivotTable to Group Customers and Display Product Dates
Use this method when you want a fast, repeatable summary without building complex formulas.
A PivotTable treats the customer column as row labels and the product topic column as column labels. Placing the date field in the Values area then returns one value for each customer and product combination.
Open your workbook in WPS Office Spreadsheets and click any cell inside the table that contains the customer, product topic, and date columns.
Go to the Insert tab and click PivotTable. Confirm the table range and choose New Worksheet, then click OK.
In the PivotTable Fields pane, drag Customer or Account to Rows, drag Product Topic to Columns, and drag Date to Values.
Click the date field in the Values area, select Value Field Settings, then choose Max or Min under Summarize Values By. This shows the related date instead of a count.
Click each generated column header and type a clear name, then apply date formatting to the date cells if needed.
Use an Array Formula to Return Product Dates by Customer
Use this only when you need a formula-based result that updates without a PivotTable and you are comfortable with array formulas.
Consolidate Customer Records in WPS Office Spreadsheets with a PivotTable
Create a PivotTable in WPS Office Spreadsheets to group customers by row and product topics by column. Each customer appears once while product dates remain visible, without using complex formulas.
- 1. Open your data: Launch WPS Office Spreadsheets and open the Excel workbook that contains multiple customer rows.
- 2. Insert a PivotTable: Click any cell in the table, go to Insert > PivotTable, and click OK.
- 3. Configure fields: Drag Customer to Rows, Product Topic to Columns, and Date to Values.
- 4. Show actual dates: Change the Value Field Settings for the date column to Max or Min so the associated date is displayed.
- 5. Review the one-row summary: Check the new worksheet for one customer row per record and product dates under each product column.
Frequently Asked Questions
Why does my PivotTable show a count of dates instead of the actual date?
Date fields are sometimes summarized as Count by default. Click the date field in the Values area, choose Value Field Settings, and change Summarize Values By to Max or Min to display the date value.
Can I use the PivotTable method when my product field contains text labels?
Yes. Product text labels work well in the Columns area. The PivotTable automatically creates one column for each product topic, such as 'Email' or 'Call'.
How can I display the latest product date if a customer has multiple entries for the same product?
Place the date field in the Values area and change the Value Field Settings to Max. The PivotTable then shows the most recent date for each customer and product topic.
Does WPS Office open the Excel file without changing the original data?
Yes. WPS Office Spreadsheets opens and saves .xlsx workbooks while preserving the existing data and layout, so you can create the PivotTable in a new worksheet without altering the source table.
