VBA & Macro Problems

Normalize Repeating Excel Columns into Rows Using Access VBA

Algirdas JasaitisAlgirdas Jasaitis Oct 13, 2026 868 views

Question details

The user needs to convert a wide Excel table with repeating, numbered fields (e.g., Diagnosis, Procedure, Modifier, HCPC numbered 1 to 25) into a normalized, single-row-per-record format in Microsoft Access.

Normalize Repeating Excel Columns into Rows Using Access VBA
Product
Microsoft Excel / Microsoft Access
Device & OS
not provided
Scenario
Restructuring wide, unnormalized spreadsheet data into a relational database format to make querying, reporting, and maintenance easier.
Observed behavior
Data is currently spread horizontally across numbered columns, making filtering and querying highly inefficient compared to a normalized row structure.
Before you start

Make a backup copy of your original Excel spreadsheet and ensure that Microsoft Access is installed with macro security settings configured to allow VBA code execution.

Solution 1Recommended

Use a VBA Loop in Access to Normalize the Data

This method uses a VBA script to iterate through numbered columns dynamically, inserting a new row in your destination table whenever data is present.

By looping through the numbered fields (e.g., 1 to 25), VBA can read the data horizontally from the imported Excel table and insert a new vertical row into a destination table for every non-null diagnosis.

This is highly efficient for datasets with many repeating column blocks because you do not have to manually map dozens of individual fields.

1
Import Excel Data

Open Microsoft Access, go to the 'External Data' tab, select 'New Data Source' > 'From File' > 'Excel', and import your wide spreadsheet as a new temporary table.

2
Create Destination Table

Create a new table in Access with the normalized structure: PatientID, Diagnosis, Procedure, Modifier1, Modifier2, and HCPC.

3
Write the VBA Loop

Press Alt + F11 to open the VBA Editor. Insert a new module and write a recordset loop that iterates through fields 1 to 25 of your temporary table.

4
Add Insertion Logic

Inside the loop, use an `If Not IsNull(Diagnosis)` condition to ensure a row is only appended to the destination table when actual data is present in that numbered column set.

5
Execute the Macro

Run the VBA code. Check your destination table to verify that the horizontal fields have been successfully translated into vertical rows.

Use a VBA Loop in Access to Normalize the Data
Normalization Complete: Your wide Excel data is now fully normalized into relational database rows, allowing for efficient SQL querying.
Free Microsoft Office alternative

Manage Complex Spreadsheets Easily with WPS Office

While Microsoft Access handles relational database architecture, WPS Office provides a free, lightweight, and robust alternative for managing complex Excel spreadsheets. You can seamlessly open, pivot, and analyze large datasets without an expensive software subscription.

  1. 1. Download the Suite: Visit the official WPS Office website and download the free software for your operating system.
  2. 2. Install and Launch: Run the installer. Once installed, launch WPS Spreadsheet to begin managing your data.
  3. 3. Open Your Excel Files: Click 'Open' and select your existing .xlsx file. You can now use built-in data tools to filter, pivot, and analyze your wide datasets natively.
Fully compatible with Microsoft Excel (.xlsx, .xls) formats and structures.Robust PivotTable features to restructure and summarize wide data formats.Lightweight performance ensures large, wide spreadsheets open quickly.Familiar ribbon interface makes transitioning from Microsoft Office seamless.
microsoft office alternative - wps office

Frequently Asked Questions

Can I normalize repeating columns directly in a spreadsheet instead of using Access?

Yes. Advanced spreadsheet functionalities, such as the Power Query (Get & Transform) 'Unpivot Columns' feature in Excel or data pivoting tools in WPS Spreadsheet, allow you to restructure wide columns into rows without needing a database application.

What happens if some records have fewer than 25 diagnoses?

The VBA loop and the append query criteria both use 'Is Not Null' logic. This ensures that blank cells are actively skipped, and rows are only generated in the destination table when actual diagnosis data is present.

Why is normalizing wide spreadsheet data necessary?

Normalizing converts horizontal, repeating columns into vertical, structured rows. This eliminates empty cells, reduces database bloat, and makes it possible to aggregate, filter, and run SQL queries efficiently across your entire dataset.