How to Find and Count Duplicate Item Combinations in Excel
Question details
The user needs to extract unique combinations of items associated with specific order numbers, identify duplicates, and count the occurrences of each distinct combination within a large dataset.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Analyzing order data where multiple items are mapped to the same order number, aiming to summarize identical item sets and calculate their frequency.
- Observed behavior
- The user seeks a formula-based method to automatically group items by order ID, output unique item combinations, and tally how many times each combination appears across the dataset.
Verify that your version of Excel supports dynamic array functions (Microsoft 365 or Excel 2021 and later). Ensure your dataset is cleanly organized in columns, typically with order numbers in column A and item codes in column B.
Use Dynamic Array Functions to Group and Count Combinations
Leverage modern Excel functions like UNIQUE, FILTER, TEXTJOIN, and COUNTIF to sequentially consolidate items by order number and find duplicates.
This formula-driven approach breaks the task into three logical steps: extracting a list of distinct order numbers, retrieving and merging the items associated with each order, and finally tallying the duplicates.
Assuming your order numbers are in Column A, select an empty cell (for example, C2) and enter the formula =UNIQUE(A:A). This will automatically spill a list of distinct order numbers without duplicates.
In the adjacent cell (e.g., D2), input the formula =TEXTJOIN("", TRUE, FILTER(B:B, A:A=C2)) to pull and concatenate all items from Column B that belong to the specific order number in C2. Drag this formula down the column to apply it to all generated orders.
In the next column (e.g., E2), use the formula =COUNTIF(D:D, D2) to calculate how many times that specific concatenated item string appears across your newly created combination list.
Easily Group and Count Duplicate Combinations in WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like UNIQUE, FILTER, and TEXTJOIN, allowing you to seamlessly process complex order combinations and calculate duplicate frequencies exactly as you would in Microsoft Excel.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the order numbers and corresponding items.
- 2. Apply the UNIQUE function: Select an empty cell and input =UNIQUE(A:A) to generate a distinct list of your order numbers.
- 3. Merge items with TEXTJOIN and FILTER: In the adjacent column, enter =TEXTJOIN(", ", TRUE, FILTER(B:B, A:A=C2)) to consolidate all items associated with each specific order.
- 4. Calculate combination frequencies: Use the =COUNTIF() function targeting your new combinations column to tally exactly how many times each item set occurs in the document.
Frequently Asked Questions
What if my version of Excel doesn't support UNIQUE or FILTER?
If you are using an older version of Excel (prior to Microsoft 365 or Excel 2021), these dynamic array functions will not work. You can achieve similar results by using Power Query to group by order number and extract items, or by writing a custom VBA macro.
Why is the COUNTIF function returning 0 for my grouped combinations?
This usually happens if the range referenced in your COUNTIF formula does not match the column where your combined TEXTJOIN results are located, or if there are hidden trailing spaces in your original data. Use the TRIM() function on your source data to clean up invisible spaces.
Is there a way to highlight the duplicate combinations visually?
Yes. Select the entire column containing your joined item strings, go to the Home tab, click Conditional Formatting, choose Highlight Cells Rules, and select Duplicate Values. This will apply a color highlight to all combinations that appear more than once.
Can I ignore blank cells when combining items?
Yes. The second argument in the TEXTJOIN function handles empty cells. By setting it to TRUE (e.g., =TEXTJOIN(", ", TRUE, ...)), the formula will automatically ignore any blank cells within the filtered range.
