Quick Excel VBA Example - Convert wide form data into long form

Excel is a tool that allows to automate practically every task with formulas or quick VBA programming / macros. You should not be copy-pasting or moving things by hand for hours on end.

Let's take a quick look at how to change wide form data that looks like this (downloaded from e.g. Datastream):

Into long form data that can be imported into statistical analysis packages (like STATA or R) and looks like this:

It can be easily accomplished using a VBA snippet that looks like this:

The code

You should be able to figure out what is happening based on the pictures above, but let's break down the pieces of the code.

Option Explicit

This line ensures that we do not forget to define (dimension) our variables before using them.

Sub ToLongForm()

This is where the execution of the code starts, it is the beginning of the subroutine.

Dim i, j, count As Integer

Here we define our variables that will allow us to walk through the cells on the sheet in sequence.

Dim timeid, compid, dataval As Variant

These variables will hold the values that we want to move around.

Application.ScreenUpdating = False

We turn off Excel's screen updating to make things go smoother.

count = 2

Let's start placing values in the new location starting from row number 2.

For i = 2 To 5

The data in the wide form is in columns 2 to 5, so let's loop over those column numbers.

compid = Worksheets("Sheet1").Cells(1, i).value

Let's store the value from the worksheet named Sheet1 and the cell in row 1 and column i into a variable called compid.

For j = 2 To 7

The data in the wide form is also stored in rows 2 to 7, let's loop over those values.

timeid = Worksheets("Sheet1").Cells(j, 1).value

Store the timeid from row j and column 1.

dataval = Worksheets("Sheet1").Cells(j, i).value

Store the actual value of each cell on row j and column i in the variable dataval.

Worksheets("Sheet2").Cells(count, 1).value = compid

After storing all the values that we need, let's place them in the correct locations. First put the compid on the other worksheet called Sheet2 in the row count and column 1.

Worksheets("Sheet2").Cells(count, 2).value = timeid

Place the timeid in column 2.

Worksheets("Sheet2").Cells(count, 3).value = dataval

Put the dataval in column 3.

count = count + 1

Let's increment the row count to the next one by adding one.

Next j

Go to the next row and process it.

Next i

Change columns.

Application.ScreenUpdating = True

Turn back Excel's screen updating.

End Sub

Close the subroutine section so that Excel knows where to stop.

Example file for download

Download an example macro enabled workbook for Excel 2013

Printing to a text file

If you were so inclined, you could also print out the things that you read from a sheet into a separate text file.

Open ThisWorkbook.Path & "\fileout.csv" For Output As #1

Place this in the beginning to open a file to print in to.

Print #1, compid & ";" & timeid & ";" & dataval

This command prints variables to the file.

Close #1

This command closes the file when you are done.

Enabling the developer ribbon in Excel 2013

To enable the developer ribbon in Excel 2013, go to File-Options-Customize Ribbon and then enable (check) the Developer tab under Main Tabs:

If you did it correctly, your ribbon should now look like the following: