Aug
15
Written by:
Brad Holtz
8/15/2009
Brian Seitz and I developed this data reshaper to get our survey data ready for Tableau Software. It still could use an error rountine. Be sure to read the notes and assumptions. Comments and improvements are welcome.
Qualifier - THIS IS NOT FOR DATA THAT DOES NOT REFLECT THE COLUMN.
It takes Data like this:
| ID |
Truck |
Car |
Bike |
| 1234 |
Truck |
no |
no |
| 1235 |
Truck |
Car |
no |
| 1236 |
no |
no |
Bike |
and coverts it into Data like this:
| ID |
Vehicle |
| 1234 |
Truck |
| 1235 |
Truck |
| 1236 |
no |
| 1234 |
no |
| 1235 |
Car |
| 1236 |
no |
| 1234 |
no |
| 1235 |
no |
| 1236 |
Bike |
' ColumnReshape Macro
' Reshape Column Data
'
'creative commons 2009 (cc) Cyon Research Corporation
'
'This macro will take data that has multiple columns related to a single question,
'and reshape them into a single column, along with the record identifier.
'
'NOTE: BEFORE YOU BEGIN
' 1. Insert a blank column after Column A
' 2. Give the column a title in the header
' (typically it will be the name of the question and located in Cell "B1")
' 3. There is no undo - make sure you have a backup of the worksheet
'
'Assumptions:
' 1. Record identifier is located in Column "A"
' 2. An uncertian number rows of headers above data
' 3. Data in the cell for the column reflects the column header
' (for instance,
' if the question is "What type of vehicle do you drive" and the
' choices are shown in mulitple columns as Yes/No responses,
' where the Column "F" is "Truck" and Column "G" is "Car"
' rather than "Yes" and "No" as cell data,
' replace "Yes" responses with "Trucks" in Column "F" and
' replace "Yes" responses with "Cars" in Column "G"
' BEFORE YOU RUN THIS MACRO)
'
' Keyboard Shortcut: Ctrl+r
'
'Get Range to Copy
DataShaperForm.Show 'Show Range Input form
'Define form data
'Data in RowXxx is the number of the rows that contain data
RowStart = DataShaperForm.RowStart.Value 'Starting Range Cell for Row Calculation
RowEnd = DataShaperForm.RowEnd.Value 'Ending Range Cell for Row Calculation
'data in ColumnXxx is the letter of columns that need to be reshaped (excluding the ID column A)
ColumnStart = DataShaperForm.ColumnStart.Value 'Starting Range Cell for Column Calculation
ColumnEnd = DataShaperForm.ColumnEnd.Value 'Ending Range Cell for Column Calculation
'NEW VARIABLES
'data for columns to be copied
FirstDataCell = ColumnStart & RowStart
LastDataCellInColumn = ColumnStart & RowEnd
CellOfLastDataColumn = ColumnEnd & RowStart
FirstIDCell = "A" & RowStart
LastIDCell = "A" & RowEnd
'Loop counts
LoopCount = 1
NumOfRowsToCopy = RowEnd - RowStart + 1
Increment = NumOfRowsToCopy + 1
'Get ColumnCount
ColumnRange = FirstDataCell & ":" & CellOfLastDataColumn
NumOfColumnsToCopy = Range(ColumnRange).Columns.Count + 0
'Loop
For LoopCount = 1 To NumOfColumnsToCopy 'For each column to be copied...
'Reset Ranges to be Copied
IDRange = FirstIDCell & ":" & "A" & RowEnd
DataRange = FirstDataCell & ":" & LastDataCellInColumn
'Copy ID and Data from First of the columns to be shaped to the new location
Range(IDRange).Copy Range(IDRange).Offset(Increment, 0)
Range(DataRange).Copy Range(IDRange).Offset(Increment, 1)
'Reset the increment to the new end of column
Increment = Increment + NumOfRowsToCopy + 0
'Delete the Column just reshaped
DeleteThisRange = ColumnStart & "1:" & ColumnStart & RowEnd
Range(DeleteThisRange).Delete
Next LoopCount
Excel Data Reshaper by Cyon Research Corporation is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License.
Requests for permissions beyond the scope of this license may be addressed via email to info@cyonresearch.com.