title: Convert row with columns of data into column with multiple rows in Excel
body: <p>I hv rows of data:-</p>
  
  <pre><code>TAG   SKU   SIZE   GRADE   LOCATION
  A001  123    12      A       X1
  A002  789    13      B       X3
  A003  456    15      C       X5
  </code></pre>
  
  <p>I need to convert it into:-</p>
  
  <pre><code>A001   123  SIZE 12
  A001   123  GRADE A
  A001   123  LOCATION X1
  A002   789  SIZE 13
  A002   789  GRADE B
  A002   789  LOCATION X3
  A003   456  SIZE 15
  A003   456  GRADE C
  A003   456  LOCATION X5
  </code></pre>
  
  <p>I used the below (based on Ben McCormack's suggestion posted on Nov 23 '09) but it doesn't produce the above result :-</p>
  
  <pre><code>Sub NormalizeSheet()
  Dim wsOriginal As Worksheet
  Dim wsNormalized As Worksheet
  Dim strKey As String
  Dim clnHeader As Collection
  Dim lngColumnCounter As Long
  Dim lngRowCounterOriginal As Long
  Dim lngRowCounterNormalized As Long
  Dim rngCurrent As Range
  Dim varColumn As Variant
  
  Set wsOriginal = ThisWorkbook.Worksheets("Original")     'This is the name of your original worksheet'
  Set wsNormalized = ThisWorkbook.Worksheets("Normalized") 'This is the name of the new worksheet'
  Set clnHeader = New Collection
  
  wsNormalized.Cells.ClearContents        'This deletes the contents of the destination worksheet'
  
  lngColumnCounter = 2
  lngRowCounterOriginal = 1
  Set rngCurrent = wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter)
  
  ' We'll loop through just the headers to get a collection of header names'
  Do Until IsEmpty(rngCurrent.Value)
      clnHeader.Add rngCurrent.Value, CStr(lngColumnCounter)
      lngColumnCounter = lngColumnCounter + 1
      Set rngCurrent = wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter)
  Loop
  
  'Here we'll reset our Row Counter and loop through the entire data set'
  lngRowCounterOriginal = 2
  lngRowCounterNormalized = 1
  lngColumnCounter = 1
  
  Do While Not IsEmpty(wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter))
  
      Set rngCurrent = wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter)
      strKey = rngCurrent.Value ' Get the key value from the current cell'
      lngColumnCounter = 2
  
      'This next loop parses the denormalized values for each row'
      Do While Not IsEmpty(wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter))
          Set rngCurrent = wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter)
  
          'We're going to check to see if the current value'
          'is equal to NULL. If it is, we won't add it to'
          'the Normalized Table.'
          If rngCurrent.Value = "NULL" Then
              'Skip it'
          Else
              'Add this item to the normalized sheet'
              wsNormalized.Range("A" &amp; lngRowCounterNormalized).Value = strKey
              wsNormalized.Range("B" &amp; lngRowCounterNormalized).Value = clnHeader(CStr(lngColumnCounter))
              wsNormalized.Range("C" &amp; lngRowCounterNormalized).Value = rngCurrent.Value
              lngRowCounterNormalized = lngRowCounterNormalized + 1
          End If
  
          lngColumnCounter = lngColumnCounter + 1
      Loop
      lngRowCounterOriginal = lngRowCounterOriginal + 1
      lngColumnCounter = 1    'We reset the column counter here because we're on a new row'
  Loop
  
  
  
  End Sub
  </code></pre>