How to Find Part Numbers with Different Codes in Excel Using WPS Office PivotTables
Question details
The user needs to identify part numbers that are associated with multiple six-digit codes in a large Excel dataset.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- The user is working with a large dataset containing part numbers and six-digit codes and needs a maintainable way to spot part numbers with inconsistent codes.
- Observed behavior
- Some part numbers appear with multiple six-digit codes, making it necessary to isolate those mismatched combinations efficiently.
Make sure your data uses separate columns for Part Number and Code, and remove leading or trailing spaces so matching values are not hidden by formatting differences.
Use a PivotTable to Group Part Numbers by Code
Best for large datasets when you need a visual, low-formula way to spot part numbers that expand to multiple six-digit codes.
A PivotTable groups matching values together without changing the source data. When a part number appears more than once under different codes, the group expansion immediately shows the mismatch.
Click any cell inside the source data, then go to the Insert tab and click PivotTable.
In the Create PivotTable dialog, confirm the table or range and choose New Worksheet, then click OK.
In the PivotTable Fields panel, drag Part Number and Code into the Rows area with Part Number above Code.
Expand each part number. If multiple six-digit codes are listed underneath it, that part number has different codes.
Use a COUNTIFS Helper Column for a Formula-Based Check
Use this when you prefer a formula-based flag that works in most spreadsheet versions and is easy to filter.
Identify Mismatched Part Numbers with WPS Office PivotTables
Use WPS Office Spreadsheet to create a PivotTable that groups part numbers by six-digit code, making it easy to find part numbers with multiple different codes without complex formulas.
- 1. Open the workbook in WPS Office: Download and install WPS Office, then open the Excel workbook that contains the part number and code data.
- 2. Insert a PivotTable: Click any cell in the data range, go to the Insert tab, and select PivotTable.
- 3. Confirm the data range: In the PivotTable dialog, verify the range and choose New Worksheet, then click OK.
- 4. Arrange the PivotTable fields: In the PivotTable Fields panel, drag Part Number and Code into the Rows area with Part Number listed first.
- 5. Review multi-code part numbers: Expand each part number group to see whether it contains multiple six-digit codes.
Frequently Asked Questions
How do I know which part numbers have more than one six-digit code?
In the PivotTable Rows area, put Part Number above Code. Then expand each part number. If more than one six-digit code appears under the same part number, that part number has different codes.
Why is my PivotTable not showing Code as a child row under Part Number?
Check the PivotTable Fields panel. Part Number must be above Code in the Rows area. If Code is in the Columns or Values area, move it back to Rows below Part Number.
Does this method work for very large Excel datasets?
Yes. PivotTables are suitable for large datasets. To avoid missing new rows, convert your source data to an Excel Table before creating the PivotTable.
Can I filter only the part numbers that have mismatched codes?
After creating the PivotTable, expand the part number groups and use filters or sorting. You can also copy the PivotTable result and highlight part numbers that contain more than one code.
