All you wana know about computers: 2013

I was working on a project where I encountered a challenge. There were over 14,000 rows of data, and many rows were repeated based on values in Column A. To address this, I wrote a macro to move duplicate entries into rows, aligning them horizontally. I’m sharing this macro with you—feel free to use it and leave feedback!

Managing data in Excel can become tricky when you have duplicate values in one column and want to organize related data into rows for easier analysis. This guide walks you through a simple VBA macro that transposes such data efficiently.

Example

Name	Subject
John	Math
John	Science
John	History
Alice	Math
Alice	English

Goal

Name	Subject 1	Subject 2	Subject 3
John	Math	Science	History
Alice	Math	English

When to Use This

This technique is useful when:

You're dealing with survey data or logs with repeat entries.
You want to summarize values per entity (e.g., customer, student, product).

You want a flat structure for reporting or exporting data.

How to Use the VBA Macro

1. Open the Excel File

Make sure your data starts in Column A (Names) and Column B (Values) without any blank rows.

2. Open the VBA Editor

Press `ALT + F11` to open the Visual Basic for Applications editor.

Go to `Insert > Module`.

VBA Macro: Rearrange Duplicate Columns into Rows

Sub rearrange()

Dim a, n As Long, i As Long, u()
Dim g, m As Integer, k As Integer
a = Range("A1").CurrentRegion
n = UBound(a, 1)
ReDim u(1 To n, 1 To 5)
For i = 1 To n
If a(i, 1) <> g Then
k = k + 1
g = a(i, 1)
m = 2
u(k, 1) = a(i, 1)
u(k, 2) = a(i, 2)
Else
m = m + 1
If m > 5 Then ReDim Preserve u(1 To n, 1 To m)
u(k, m) = a(i, 2)
End If
Next i
u(1, 1) = "Macro output"
Range("E1").Resize(k, UBound(u, 2)) = u
End Sub

Run the Macro

Close the editor.
Press ALT + F8, select TransposeData, and click Run.

Your transposed output will appear starting from Column E.

How It Works

It creates a dictionary with names as keys.
For each duplicate name, it stores values in an array.
Then it writes each name and its values horizontally.

Tips and Customization

You can change the input columns if your data isn't in A & B.
Add sorting or filtering before running the macro for cleaner output.
You can adapt this script for three or more columns as needed.

Transpose duplicate data from columns to rows in excel.