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:

 

 and coverts it into Data like this:

' 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.