How to Generate Unique IDs from Item Names and Frequencies in Excel
Question details
The user needs to expand a two-column table of item names and frequency counts into repeated rows and generate sequential unique identifiers such as Bob_01, Chris_01 through Chris_05, and John_01 through John_02.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user has a table containing item names and desired frequency counts and wants to create one row per required occurrence with unique IDs.
- Observed behavior
- The expected output is an expanded item list with repeated item names and unique identifiers based on the frequency column.
Store the source data in an Excel table with clear Item Name and Frequency columns. Check that item names are not blank and all frequency values are positive numbers before expanding the rows.
Expand Item Names by Frequency and Add Sequential IDs with Power Query
Use this when your workbook has Power Query and you want a refreshable, formula-free solution.
Power Query lets you create a list of sequence numbers for each frequency value, expand that list into rows, and then concatenate the item name with a zero-padded sequence.
Click any cell in the source table, go to the Data tab, and choose From Table/Range. Confirm the table range to open the Power Query Editor.
In Power Query, go to Add Column > Custom Column. Name it Sequence and enter = List.Numbers(1, [Frequency]) to create a list from 1 to the frequency value for each item.
Click the expand icon in the Sequence column header, choose Expand to New Rows, and each item will repeat according to its Frequency value.
Go to Add Column > Custom Column. Name it Unique ID and enter = [Name] & "_" & Text.PadStart(Text.From([Sequence]),2,"0") to return IDs such as Bob_01 and Chris_03.
On the Home tab, click Close & Load, choose Table, and select a new worksheet as the destination.
Use Microsoft 365 Dynamic Array Formulas to Expand Items and IDs
Use this when you have Microsoft 365 and want a single-formula result without refreshing a Power Query connection.
Need an Excel-Compatible Spreadsheet? Try WPS Office Free
Power Query is a Microsoft Excel feature, and WPS Office does not include the native Power Query editor. WPS Spreadsheet is a free, lightweight alternative that opens .xlsx workbooks, keeps a familiar ribbon interface, and lets you edit and save Excel data without losing formatting or formulas.
- 1. Download WPS Office Free: Visit the WPS Office download page and click Download WPS Office Free to install the suite on your device.
- 2. Open the Excel workbook: Launch WPS Spreadsheet, click Open, and select the .xlsx file that contains the item names and frequency table.
- 3. Review and save the data: Use the familiar ribbon tools to review or edit the expanded list, then save the file as .xlsx to preserve Excel compatibility.
Frequently Asked Questions
Can I generate two-digit IDs like Bob_01 instead of Bob_1 in Power Query?
Yes. In the custom column, use Text.PadStart on the sequence number, for example: [Name] & "_" & Text.PadStart(Text.From([Sequence]),2,"0").
What should I do if my frequency column contains zero or blank values?
Check the frequency column before expanding. Zero or blank values can create empty lists or unexpected rows; filter them out or replace them with 1 as needed.
Will the REDUCE, SEQUENCE, VSTACK, and HSTACK formulas work in WPS Spreadsheet?
These dynamic array functions are designed for Microsoft 365. WPS Spreadsheet can open and edit the workbook, but the Power Query method or a helper-column approach may be required outside Microsoft 365.
How do I refresh the generated IDs after changing the source table?
If you used Power Query, right-click the result table and choose Refresh, or open Power Query Editor and click Close & Load again after changing the source.
Can I place the expanded result in the same worksheet as the source table?
Yes. When closing and loading Power Query results, choose Table and select the same worksheet, but ensure the destination area does not overlap the original table.
