How to Align Two Excel Name Columns by Inserting Blank Cells with VBA
Question details
The user needs to align two alphabetical columns of names. Column A contains the master list (with possible duplicates), and Column B needs blank cells inserted so matching names sit on the same row. Any names in Column B that are not found in Column A should be moved to Column C.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing and aligning two datasets where names must match row by row, requiring structural shifts like inserting blank cells for missing entries and extracting unmatched data.
- Observed behavior
- Standard formulas cannot dynamically shift data structures or physically insert blank cells into an existing column, necessitating a macro-based approach.
Before running the macro, ensure your dataset starts from row 2 (assuming row 1 contains headers) and that the Developer tab is enabled in your spreadsheet ribbon so you can access the VBA Editor.
Use a VBA Macro to Align Columns and Move Unmatched Data
Since worksheet formulas cannot physically insert rows or shift cells, deploying a VBA macro is the most effective way to automate the alignment process and organize non-matching data.
This macro works in two phases: First, it scans column B from the bottom up to identify names that do not exist in column A. It moves these unmatched names to column C and deletes them from column B. Second, it compares the remaining values in columns A and B from the top down, inserting blank cells in column B wherever the names are misaligned.
Press ALT + F11 in Excel to launch the Microsoft Visual Basic for Applications (VBA) editor.
Click on 'Insert' in the top menu bar, then select 'Module' to create a blank script window.
Paste your macro script into the module. Ensure the script includes a reverse loop (Step -1) using Application.CountIf to find non-matching names in column B, move them to column C, and delete them. Then, include a Do Loop to compare rows and insert blank cells (Shift:=xlShiftDown) in column B where the values do not match.
Close the VBA Editor to return to your worksheet. Press ALT + F8 to open the Macro dialog box, select your newly created macro, and click 'Run' to align the columns.

Align Columns and Run Macros Seamlessly with WPS Spreadsheet
WPS Spreadsheet offers powerful data manipulation tools, including full support for VBA macros in its advanced versions. You can easily align mismatched columns, automate complex structural shifts, and handle large datasets without experiencing lag.
- 1. Open Your Dataset in WPS: Download and launch WPS Office, then open your existing dataset or workbook in WPS Spreadsheet.
- 2. Access the Macro Tools: Navigate to the 'Developer' tab on the top ribbon and click on 'Macro' to open the script manager.
- 3. Run the Alignment Script: Paste the alignment VBA script into the editor and execute it to instantly organize your columns, extract unmatched data, and insert blank cells automatically.

Frequently Asked Questions
Why can't I use VLOOKUP or IF formulas to insert blank cells?
Formulas like VLOOKUP or IF are designed to return values to the specific cells where they are typed. They do not have the capability to alter the physical structure of the spreadsheet, such as inserting new blank cells, shifting surrounding rows, or deleting existing data.
How do I align columns without using VBA macros?
Without macros, you cannot physically insert blank cells dynamically into an existing column. Instead, you would need to create a helper column. You can use a formula like =IFERROR(VLOOKUP(A2, B:B, 1, FALSE), "") in a new column to pull matching data next to Column A, leaving a blank where no match is found.
What happens if both Column A and Column B contain duplicates?
A basic comparison macro evaluates cells row by row. If there are duplicates in both columns, the loop logic might misalign subsequent rows depending on how it processes identical consecutive values. It is highly recommended to remove duplicates or add unique identifiers before running the alignment.
Can I undo a VBA macro after running it to align my columns?
No, actions executed by a VBA macro generally clear the Excel Undo history. Because this script shifts cells and deletes unmatched entries, you should always save a backup copy of your workbook before running the macro.




