Function Problems

How to Assign Sequential Numbers to Repeated Company Names in Excel

Huma Ashraf Ch Oct 1, 2026 869 views

Question details

The user needs to assign a unique, repeating sequential number to consecutive rows that share the same company name or ticker in an Excel dataset.

How to Assign Sequential Numbers to Repeated Company Names in Excel

Product: Excel
Device & OS: not provided
Scenario: Organizing a sorted financial dataset by assigning unique numerical IDs or PivotTable ranks to grouped data (like company tickers) across multiple detailed quarterly rows.
Observed behavior: The dataset contains repeated company names across multiple rows, but lacks a unique identifier or rank corresponding to each unique company group in the detailed view.

Before you start

Ensure your dataset is sorted by the company name or ticker column so that all rows belonging to a single company are grouped together consecutively.

Solution 1Recommended

Use Remove Duplicates and VLOOKUP Function

This method extracts a unique list of companies, assigns numbers to them, and maps those numbers back to your main dataset.

This is the most reliable method for mapping unique IDs or ranks. It works flawlessly whether you are generating basic sequential numbers or pulling complex PivotTable rankings back into your detailed records.

Copy the ticker column

Select the entire column containing your company names or tickers, copy it, and paste it into a new, blank worksheet.

Remove duplicates

Navigate to the 'Data' tab on the ribbon and click 'Remove Duplicates'. Confirm your selection to leave only a list of unique company names.

Assign sequential numbers

In the column immediately to the right of your unique tickers, type 1, 2, 3, etc., to assign a unique sequential number or rank to each company.

Apply the VLOOKUP function

Return to your original dataset, insert a new column for the IDs, and use the VLOOKUP function (e.g., =VLOOKUP(A2, Sheet2!A:B, 2, FALSE)) to search for the company name and return the assigned number.

Use Remove Duplicates and VLOOKUP Function

Works for PivotTable Ranks: If you grouped your data in a PivotTable to calculate total profits and rank stocks, you can use this same VLOOKUP technique to pull each stock's rank from the PivotTable back to every quarterly row in your detailed data.

Solution 2

Use an IF Formula for Grouped Data

If your data is already perfectly sorted, you can use a simple IF formula to increment the number only when the company name changes.

Easily Manage Sequential Data with WPS Spreadsheet

WPS Spreadsheet offers powerful data processing tools, including advanced lookup functions and one-click deduplication, fully compatible with Excel formats to help you assign sequential numbers seamlessly.

1. Extract unique records: Copy your company column to a new sheet and use 'Data' > 'Remove Duplicates' in WPS Spreadsheet.
2. Create an index: Type sequential numbers or paste PivotTable rankings next to the unique company list.
3. Lookup the numbers: Use =VLOOKUP() in your main data sheet to pull the numbers back to your detailed rows.

100% compatible with Microsoft Excel formulas like VLOOKUP, XLOOKUP, and IF.Intuitive Data tab with a streamlined Remove Duplicates feature.Lightweight software with fast processing for handling large financial datasets.Free built-in templates for financial analysis and stock management.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use XLOOKUP instead of VLOOKUP for this task?

Yes, XLOOKUP is a modern and more robust alternative. You can use =XLOOKUP(lookup_value, lookup_array, return_array) to fetch the sequential number without worrying about counting column index numbers or the position of the lookup column.

What if my data is not sorted by company name?

If your data is not sorted, the VLOOKUP and XLOOKUP methods will still work perfectly because they search for the exact company name regardless of its position in the dataset. However, the IF formula method will fail and should not be used on unsorted data.

How can I assign ranks from a PivotTable back to the main data?

First, create your PivotTable and rank the items as needed. Then, treat the PivotTable area (or copy its values to a new range) as your lookup table. Finally, use the VLOOKUP function in your main data to search for the company name and return the corresponding rank.

Excel & Data Problems