Excel: Converting between data layouts

Question

I have the situation as show below. There are groups of rows. Above each group is a small "header" describing a data field that all the rows in the group have in common. I want to create a pivot table of everything and include that field (GroupID), hence I want the second version of the table. Is there a way of doing this short of recording a macro? Or is there perhaps a way of creating the pivot table directly?

I have:

GroupID
4

Name      Email       Likes   Dislikes
herpina   something   Cake    Excel
derpina   something   Cake    Excel

GroupID
5

Name      Email       Likes   Dislikes
roflmao   something   Cake    Hmm
roflomg   something   Baking  test
testtttt  something   Sleep   22

GroupID
1

Name      Email       Likes   Dislikes
OAG       something   OAB     N/A
OAB       something   N/A     N/A

Desired:

Name      Email       Likes   Dislikes  GroupID
herpina   something   Cake    Excel     4
derpina   something   Cake    Excel     4
roflmao   something   Cake    Hmm       5
roflomg   something   Baking  test      5
testtttt  something   Sleep   22        5
OAG       something   OAB     N/A       1
OAB       something   N/A     N/A       1

This sample input is also located at https://dl.dropbox.com/u/736090/Book1.xlsx

Answer 1

Here is the macro as short as possible

• open Excel and press ALT+F11
• insert the code below into sheet1 or where ever your data is

• close VBA editor and press ALT+F8 and ececute the macro

  Sub deletelines()    
    Range("A4:D4").Copy
    Range("A1").Insert Shift:=xlToRight
    For i = 2 To Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
      c = Cells(i - counter, 1)
      if IsNumeric(c) And c <> "" Then groupID = c
      If c = "" Or c = "Name" Or c = "GroupID" Or IsNumeric(c) Then
        Rows(i - counter).Delete
        counter = counter + 1
      Else
        Cells(i - counter, 5) = groupID
      End If
    Next i   
  End Sub
  

---

Since this is a Q&A site, I describe the code. That way it may be helpful for others who can easily adapt and customize the code.

• Line 1+14: starts and ends a macro (subroutine)
• Line 2+3: copy the range [A4:D4] and insert it before [A1]. This forms your header row
• Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row determines the last used cell. In your eample its 22
• Line 4: is a loop which starts at 2 and ends at 22. The i is used as row index
• Line 5: saves first cell of current line. General syntax is cells(rownumber,columnnumber)
  Attention: Since we delete rows, we have to subtract how many lines we already have deleted.
• Line 6: Look if c is a number and save it as our currect groupID until we find another number
• Line 7: Is a condition which checks if the current cell value is "Name", "GroupID", blank or a Number.
• Line 8: If the condition is true, this deletes the whole line
• Line 9: If the condition is true, this counts up our howmanydeletedlines counter
• Line 10-12: If the condition is false, this has to be a good data row we want to preserve
• Line 13: loops to the next i which stands for our current line