@Doug Glancy 2012-06-06 21:11:02

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



Here's the code:

'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 Long
Dim ColsToRepeat As Excel.Range, ColsToNormalize As Excel.Range
Dim NormalizedRowsCount As Long
Dim RepeatingList() As String
Dim NormalizedList() As Variant
Dim ListIndex As Long, i As Long, j As Long
Dim wbSource As Excel.Workbook, wbTarget As Excel.Workbook
Dim wsTarget As Excel.Worksheet

With 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 j
Next 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 j
Next 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 i
End 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)
    Set wbSource = List.Parent.Parent
    With wbSource.Worksheets
        Set wsTarget = .Add(after:=.Item(.Count))
    End With
End If

With 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 = DataColHeader
End With
End Sub

You’d call it like this:

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

@baha-kev 2012-06-06 23:25:25

This is great; however I'm having trouble selecting the valid range. When I highlight the cells I'm trying to stack, it pulls in extra blank columns (probably from your baseball example). How do I delineate the relevant range before running the macro?

@Doug Glancy 2012-06-06 23:48:49

You'd change the calling line to:NormalizeList Selection, 4, "Variable", "Value", False or NormalizeList ActiveSheet.Range("A1:D100"), 4, "Variable", "Value", False or whatever the range should be.

@user3971056 2014-08-26 16:27:37

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.

@Raphael Lee 2016-03-30 00:33:25

First create a Userform and name it Unpivot_Form with two RefEdit fields - rng_id and value_id and a submit/go button. I am also an R user and rng_id is the range that contains the id while value_id contains the value; both range inclusive of header.

Do two macro:

Sub unpivot()
End Sub

Another macro is within the submit/go button of the field:

Private Sub submit_Click()
'Code to unpivot (convert wide to long for excel)

Dim rng_id, rng_id_header, val_id As Range
Dim colvar, emptyrow, col As Integer
Dim new_sheet As Worksheet

'Put val_id range into a range object
Set val_id = Range(value_id.Value)

'Determine the parameter for the value id range
'This is used for the looping later on
numrows = val_id.Rows.Count
numcols = val_id.Columns.Count

'Resize changes the "block" to the size defined by the row and column
'Offset moves the "block"
Set rng_id_header = Range(range_id.Value).Resize(1)
Set rng_id = Range(range_id.Value).Offset(1, 0).Resize(numrows - 1)

Set new_sheet = Worksheets.Add

'Set up the first column and first batch of id vars
rng_id_header.Copy ActiveCell
colvar = Range("XFD1").End(xlToLeft).Column + 1
Range("XFD1").End(xlToLeft).Offset(, 1).Value = "Variable"
Range("XFD1").End(xlToLeft).Offset(, 1).Value = "Value"

'Start populating the value ids
For col = 1 To numcols

  'populate var_id
  'determine last row
   emptyrow = Range("A65535").End(xlUp).Row + 1
   'no need to activate to source to copy
   rng_id.Copy new_sheet.Cells(emptyrow, 1)
  'copy the variable
  val_id.Offset(, col - 1).Resize(1, 1).Copy new_sheet.Range(Cells(emptyrow, colvar), Cells(emptyrow + numrows - 2, colvar))
  'copy the value
  val_id.Offset(1, col - 1).Resize(numrows - 1, 1).Copy new_sheet.Range(Cells(emptyrow, colvar + 1), Cells(emptyrow + numrows - 2, colvar + 1))


Unload Me

End Sub


@snb 2015-01-14 17:45:07

or use:

Sub M_snb_000()
  With sheet1.Cells(1).CurrentRegion
    sn = .Resize(, .Columns.Count + 1)
  End With

  For j = 4 To UBound(sn, 2) - 1
    With Sheet2.Cells(2 + (UBound(sn) - 1) * (j - 4), 1)
       .Resize(UBound(sn) - 1, 5) = Application.Index(sn, Evaluate("row(2:" 
             & UBound(sn) & ")"), Array(1, 2, 3,UBound(sn, 2), j))
       .Resize(UBound(sn) - 1, 1).Offset(, 3) = sn(1, j)
    End With
End Sub

@KHeaney 2015-01-14 18:20:13

This does not seem like a correct answer as it does not accept a variable for the number of id columns.

@Excel Hero 2020-05-04 05:48:36

@KHeaney Actually, this answer hits Excel savant level. Did you try it? One minor change for this particular question... change the For j = 4... line to For j = 5... It works perfectly. This is spectacular mastery of Excel-VBA nuance. Excellent work, @snb.

@KHeaney 2020-05-27 19:31:35

@ExcelHero I did not test it, just quickly assessed it in the Triage queue.Honestly it probably would work well as it seems like it would properly iterate over the range needed. As I said in my comment though, I do not believe it accepts an input and I do not know the use case for the user so I went with what their question stated which was that it required an input from the user.

@Tom McMahon 2012-08-16 05:57:47

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


Related Questions

Sponsored Content

15 Answered Questions

[SOLVED] How to avoid using Select in Excel VBA

  • 2012-05-23 05:57:58
  • 280930 View
  • 537 Score
  • 15 Answer
  • Tags:   excel vba

22 Answered Questions

[SOLVED] Is there a way to crack the password on an Excel VBA Project?

  • 2009-06-22 10:37:13
  • Jonathan Sayce
  • 1164456 View
  • 485 Score
  • 22 Answer
  • Tags:   excel vba passwords

1 Answered Questions

[SOLVED] equivalent of melt+reshape that splits on column names

1 Answered Questions

[SOLVED] Reshape time-series data into panel-data with multiple subjects and variables in r

  • 2018-09-23 23:24:26
  • Scalable_Solutions
  • 382 View
  • 0 Score
  • 1 Answer
  • Tags:   r panel-data

3 Answered Questions

[SOLVED] Reshaping data by aggregating columns in a row

  • 2016-11-02 12:43:25
  • pacomet
  • 152 View
  • 1 Score
  • 3 Answer
  • Tags:   r reshape2

2 Answered Questions

[SOLVED] Reshape data based on difference of the two columns years

  • 2016-03-01 19:43:58
  • user3570187
  • 106 View
  • 0 Score
  • 2 Answer
  • Tags:   r reshape reshape2

1 Answered Questions

[SOLVED] Reshape DF from long to wide in R using Reshape2 without an aggregation function

  • 2016-01-25 19:07:15
  • Derek Darves
  • 435 View
  • 1 Score
  • 1 Answer
  • Tags:   r reshape2

2 Answered Questions

[SOLVED] Reshape data in R change a long table into a wide table

1 Answered Questions

[SOLVED] Panel Data from Long to wide reshape or cast

Sponsored Content