Normalize Repeating Excel Columns into Rows Using Access VBA
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.

- 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.
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.
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.
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.
Create a new table in Access with the normalized structure: PatientID, Diagnosis, Procedure, Modifier1, Modifier2, and HCPC.
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.
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.
Run the VBA code. Check your destination table to verify that the horizontal fields have been successfully translated into vertical rows.

Use Append Queries (No-Code Alternative)
If you are not comfortable writing VBA, you can use built-in Access Append Queries for each set of numbered columns.
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. Download the Suite: Visit the official WPS Office website and download the free software for your operating system.
- 2. Install and Launch: Run the installer. Once installed, launch WPS Spreadsheet to begin managing your data.
- 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.

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.




