Function Problems

How to Create Unique Product and Size SKUs in Excel

Nimra Malik Oct 12, 2026 870 views

Question details

The user needs to dynamically generate a deduplicated list of product-and-size SKU combinations from a matrix of product codes, sizes, and quantities.

How to Create Unique Product and Size SKUs in Excel

Product: Excel
Device & OS: not provided
Scenario: Consolidating a 2D grid of inventory data (products in rows, sizes in columns) into a clean, vertical list of active SKUs.
Observed behavior: The raw data is spread across multiple columns and rows, and manual combination creates duplicates when the same product-size combination appears multiple times.

Before you start

Ensure you are using a modern version of Excel (Microsoft 365 or Excel 2021) that supports dynamic array functions like TOCOL, FILTER, and UNIQUE.

Solution 1Recommended

Use Dynamic Array Functions to Generate SKUs

Leverage modern Excel functions to dynamically combine, filter, and deduplicate your product and size data into a single SKU column.

By nesting IF, TOCOL, FILTER, and UNIQUE functions, you can create a formula that instantly transforms a two-dimensional grid into a single, deduplicated column of SKUs. This approach ensures that combinations with zero quantities are ignored.

Select the target cell

Click on the cell where you want the new list of SKUs to begin spilling (for example, cell O2).

Enter the formula string

Input the following formula into the formula bar: =UNIQUE(FILTER(TOCOL(IF(B3:L4>0,A3:A4&"-"&B2:L2,"")),TOCOL(IF(B3:L4>0,A3:A4&"-"&B2:L2,""))<>"")). Adjust the ranges B3:L4 (quantities), A3:A4 (product codes), and B2:L2 (sizes) to match your specific spreadsheet layout.

Generate the SKU array

Press the Enter key. The formula will automatically combine the product codes and sizes with a hyphen, remove empty results, filter out duplicates, and spill the final SKU list down the column.

Calculate total quantities (Optional)

In the adjacent column (e.g., column P), use the SUMIFS function referencing your original quantity ranges and the new spilled SKU list to calculate the total stock for each unique item.

Use Dynamic Array Functions to Generate SKUs

Dynamic Updates: As you update or add new quantities in your data grid, the SKU list generated by this formula will automatically update and expand without requiring any manual adjustments.

Advanced Spreadsheets Made Easy

Create Dynamic SKUs in WPS Spreadsheet

WPS Office provides robust support for modern dynamic array functions, allowing you to seamlessly process complex inventory matrices and generate precise SKU lists.

1. Open your inventory workbook: Launch WPS Spreadsheet and open the file containing your product and size grid.
2. Select the output cell: Click the cell in the column where you wish to generate your SKU list.
3. Input the dynamic formula: Type the formula combining UNIQUE, FILTER, TOCOL, and IF into the formula bar, referencing your product and size columns.
4. Apply and calculate: Press Enter to execute the formula and instantly generate a deduplicated, dynamic list of SKUs.

Fully compatible with complex Microsoft Excel array formulasSupports modern functions including UNIQUE, FILTER, and TOCOLFree, lightweight, and highly optimized for large datasetsClean, familiar user interface for seamless workflow migration

microsoft office alternative - wps office

Frequently Asked Questions

How do I calculate the total quantity for each generated SKU?

You can use the SUMIFS function next to your spilled SKU array. Reference the original grid to sum the corresponding quantities based on the newly created product-size SKU strings.

Why does my TOCOL function return a #NAME? error?

The #NAME? error occurs if your version of Excel does not support the newly introduced TOCOL dynamic array function. You must use Microsoft 365, Excel 2021, or an updated version of WPS Office.

Can I extract just the product code from the generated SKU?

Yes. If your SKU format is 'PRODUCT-SIZE', you can use the TEXTBEFORE function or a combination of LEFT and FIND to extract the text appearing before the hyphen delimiter.

Excel & Data Problems