VBA & Macro Problems

How to Assign Group Numbers to Duplicate Excel Cell Values Using VBA

Maira Mehtab Oct 9, 2026 871 views

Question details

The user needs to identify exact matches among duplicate Excel cells containing more than 256 characters and assign group numbers based on their first appearance.

How to Assign Group Numbers to Duplicate Excel Cell Values Using VBA

Product: Microsoft Excel 2013
Device & OS: not provided
Scenario: Comparing long text strings in Excel cells to find duplicates and grouping them by assigning a unique group identifier.
Observed behavior: Standard Excel formulas fail to reliably compare duplicate cells containing more than 256 characters, necessitating a VBA-based solution.

Before you start

Ensure you have enabled the Developer tab in your spreadsheet program and saved your workbook as a Macro-Enabled Workbook (.xlsm) to allow VBA code execution.

Solution 1Recommended

Use a VBA Dictionary to Group Duplicate Cells

This method uses a Scripting.Dictionary object to track unique values and assign a distinct group ID to each set of matching long text strings.

Standard Excel formulas often truncate or fail to accurately compare text strings exceeding 256 characters. Using a VBA dictionary allows you to handle long strings efficiently by mapping each distinct cell value to a unique group number when it first appears.

Open the VBA Editor

Press Alt + F11 on your keyboard to launch the Visual Basic for Applications Editor.

Insert a New Module

In the top menu, click on 'Insert' and select 'Module' to create a blank script window.

Write the Dictionary Script

Create a Sub procedure that initializes a 'Scripting.Dictionary'. Use a For Each loop to iterate through your target range (e.g., AS9:AS1245). Check if the cell value exists as a key in the dictionary; if not, assign it a new incrementing group number.

Output the Group Numbers

Within the loop, use the 'Offset' property to write the dictionary's assigned group number into an adjacent column for each corresponding cell.

Run the Macro

Press F5 or click the 'Run' button in the toolbar to execute the script and populate your worksheet with the correct group numbers.

Use a VBA Dictionary to Group Duplicate Cells

Customizing the Logic: If you only want to assign group '1' to values that have no duplicates, you will need an initial loop to count occurrences first, before assigning distinct IDs to actual duplicates.

Group Duplicate Data Easily with WPS Office

WPS Spreadsheet provides robust macro support that is fully compatible with Excel VBA, allowing you to run complex dictionary scripts for long text strings effortlessly. It also features powerful built-in tools for standard duplicate management.

1. Open WPS Spreadsheet: Launch WPS Office and open your data workbook in WPS Spreadsheet.
2. Access the Developer Tools: Navigate to the Developer tab on the ribbon and click on the 'Visual Basic' or 'Macro' icon.
3. Paste Your VBA Code: Insert a new Module and paste your VBA dictionary script designed to group duplicates.
4. Execute the Script: Run the macro to instantly assign group numbers to your duplicate text strings.

Fully compatible with Microsoft Excel VBA macros and Scripting.DictionaryEasily processes large datasets and cell values over 256 charactersIntuitive Developer tab for writing and running scriptsFree to use as a lightweight alternative to Microsoft Office

microsoft office alternative - wps office

Frequently Asked Questions

Why do standard Excel formulas fail to compare duplicates over 256 characters?

Certain built-in functions, like COUNTIF or VLOOKUP, have historical limitations and cannot evaluate text strings longer than 255 characters, often returning incorrect results, truncations, or #VALUE! errors.

Can I group all unique (non-duplicated) values together as group 1?

Yes, you can modify your VBA logic to count occurrences first. Then, iterate through the list again to assign group '1' to any value with a total occurrence count of exactly 1, and unique incremented identifiers for the rest.

How do I enable the Developer tab to run VBA?

Right-click anywhere on the main ribbon, select 'Customize the Ribbon', and check the box next to 'Developer' in the right-hand pane to make it visible.

Excel & Data Problems