melt / reshape in excel using VBA?

I've got two posts, with usable code and downloadable workbook, on doing this in Excel/VBA on my blog:

http://yoursumbuddy.com/data-normalizer

http://yoursumbuddy.com/data-normalizer-the-sql/

Here's the code:

'Arguments'List: The range to be normalized.'RepeatingColsCount: The number of columns, starting with the leftmost,'   whose headings remain the same.'NormalizedColHeader: The column header for the rolled-up category.'DataColHeader: The column header for the normalized data.'NewWorkbook: Put the sheet with the data in a new workbook?''NOTE: The data must be in a contiguous range and the'columns that will be repeated must be to the left,'with the columns to be normalized to the right.Sub NormalizeList(List As Excel.Range, RepeatingColsCount As Long, _    NormalizedColHeader As String, DataColHeader As String, _    Optional NewWorkbook As Boolean = False)Dim FirstNormalizingCol As Long, NormalizingColsCount As LongDim ColsToRepeat As Excel.Range, ColsToNormalize As Excel.RangeDim NormalizedRowsCount As LongDim RepeatingList() As StringDim NormalizedList() As VariantDim ListIndex As Long, i As Long, j As LongDim wbSource As Excel.Workbook, wbTarget As Excel.WorkbookDim wsTarget As Excel.WorksheetWith List    'If the normalized list won't fit, you must quit.   If .Rows.Count * (.Columns.Count - RepeatingColsCount) > .Parent.Rows.Count Then        MsgBox "The normalized list will be too many rows.", _               vbExclamation + vbOKOnly, "Sorry"        Exit Sub    End If    'You have the range to be normalized and the count of leftmost rows to be repeated.   'This section uses those arguments to set the two ranges to parse   'and the two corresponding arrays to fill   FirstNormalizingCol = RepeatingColsCount + 1    NormalizingColsCount = .Columns.Count - RepeatingColsCount    Set ColsToRepeat = .Cells(1).Resize(.Rows.Count, RepeatingColsCount)    Set ColsToNormalize = .Cells(1, FirstNormalizingCol).Resize(.Rows.Count, NormalizingColsCount)    NormalizedRowsCount = ColsToNormalize.Columns.Count * .Rows.Count    ReDim RepeatingList(1 To NormalizedRowsCount, 1 To RepeatingColsCount)    ReDim NormalizedList(1 To NormalizedRowsCount, 1 To 2)End With'Fill in every i elements of the repeating array with the repeating row labels.For i = 1 To NormalizedRowsCount Step NormalizingColsCount    ListIndex = ListIndex + 1    For j = 1 To RepeatingColsCount        RepeatingList(i, j) = List.Cells(ListIndex, j).Value2    Next jNext i'We stepped over most rows above, so fill in other repeating array elements.For i = 1 To NormalizedRowsCount    For j = 1 To RepeatingColsCount        If RepeatingList(i, j) = "" Then            RepeatingList(i, j) = RepeatingList(i - 1, j)        End If    Next jNext i'Fill in each element of the first dimension of the normalizing array'with the former column header (which is now another row label) and the data.With ColsToNormalize    For i = 1 To .Rows.Count        For j = 1 To .Columns.Count            NormalizedList(((i - 1) * NormalizingColsCount) + j, 1) = .Cells(1, j)            NormalizedList(((i - 1) * NormalizingColsCount) + j, 2) = .Cells(i, j)        Next j    Next iEnd With'Put the normal data in the same workbook, or a new one.If NewWorkbook Then    Set wbTarget = Workbooks.Add    Set wsTarget = wbTarget.Worksheets(1)Else    Set wbSource = List.Parent.Parent    With wbSource.Worksheets        Set wsTarget = .Add(after:=.Item(.Count))    End WithEnd IfWith wsTarget    'Put the data from the two arrays in the new worksheet.   .Range("A1").Resize(NormalizedRowsCount, RepeatingColsCount) = RepeatingList    .Cells(1, FirstNormalizingCol).Resize(NormalizedRowsCount, 2) = NormalizedList    'At this point there will be repeated header rows, so delete all but one.   .Range("1:" & NormalizingColsCount - 1).EntireRow.Delete    'Add the headers for the new label column and the data column.   .Cells(1, FirstNormalizingCol).Value = NormalizedColHeader    .Cells(1, FirstNormalizingCol + 1).Value = DataColHeaderEnd WithEnd Sub

You’d call it like this:

Sub TestIt()NormalizeList ActiveSheet.UsedRange, 4, "Variable", "Value", FalseEnd Sub

Microsoft recently came out with Power Query, an Excel Add-In which adds a lot of interesting functions and capabilities to data manipulation from within Excel, including what you're looking for.

The actual function within the Add-In is called "Unpivot Columns", which is explained in this article. Here's the gist of it:

1. Download and install the add-in
2. Open up your Excel/CSV file
3. Select the table/range you want to melt/reshape
4. In the "Power Query" tab, click on "From Table", which will open the "Query Editor"
5. Select the columns you want to melt/reshape (ctrl or shift-select, don't drag)
6. In the "Transform" tab click on "Unpivot Columns" (you can also apply other transformations here before returning to Excel)
7. In the "Home" tab click "Close & Load". This will create a new table/query object in Excel with the desired result.

For anyone looking for a visual way to normalize excel data, see this video tutorial:

http://www.youtube.com/watch?v=xmqTN0X-AgY