Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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
share|improve this answer
    
It's a start, but not quite there. Please have a look at the sample input at dl.dropbox.com/u/736090/Book1.xlsx - and do let me know if you need it in another format. –  Christian Jonassen Jan 6 '13 at 1:44
    
I edited my answer. I tested it with your book1.xlsx and it worked for me. –  nixda Jan 6 '13 at 15:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.