Results 1 to 8 of 8
  1. #1
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    OK - I'm trying to make Excel behave like a relational database, and not having much success. The attached file is a subset of the problem - the actual data is some 28,000 rows with about 5000 different people. Each person has a header record displayed in the left set of columns, and a set of one or more detail records displayed in the right-hand columns. What I would like to do is use the Subtotal function in Excel to automatically create the Outline, but when I do that, I only have math functions available to me. I'm sure it could be done with a set of VBA, but it seems to me I should be able to do this with the standard commands. The example row in bold shows what I would like to have.

    [attachment=87866:GroupProblem.xlsx]

    (The actual scenario is this data from a SQL Server database needs to be sent to people who roam across the country interviewing people and submitting the detail records, and don't have access the the current data. But they would like to have a static copy that is refreshed every few months so they can check the existing records for a given person if there is a question about it. Since they all have Excel....)
    Attached Files Attached Files
    Wendell

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Maybe you can use a pivot table for this goal?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Here is a VBA way to do it...

    Steve

    [codebox]Option Explicit
    Sub CreateGroups()
    Dim lRow As Long
    Range("A2").RemoveSubtotal
    Columns("A:A").Copy
    Range("A1").Insert Shift:=xlToRight
    Application.CutCopyMode = False
    Range("A2").Subtotal GroupBy:=1, _
    Function:=xlCount, TotalList:=Array(17), _
    Replace:=True, PageBreaks:=False, _
    SummaryBelowData:=False
    Range("A3").EntireRow.Delete
    lRow = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
    Range("A2").AutoFilter Field:=1, _
    Criteria1:="<>*Count"
    Range("A3:A" & lRow).ClearContents
    Range("A2").AutoFilter
    lRow = Cells(lRow, 1).End(xlUp).Row
    Do
    Range(Cells(lRow + 1, 2), _
    Cells(lRow + 1, 12)).Copy Cells(lRow, 2)
    Range(Cells(lRow, 2), _
    Cells(lRow, 12)).Font.Bold = True
    lRow = Cells(lRow, 1).End(xlUp).Row
    Loop While lRow >= 3
    Columns("A:A").Delete
    End Sub[/CODEBOX]

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Thanks Jan and Steve - sorry about the late response. I've come to the conclusion that you can't really do it with Pivot Tables or via the grouping functions, so I'll have a go at the VBA and see if I can make that work - it doesn't look as hairy as I expected it would.
    Wendell

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Quote Originally Posted by sdckapr View Post
    Here is a VBA way to do it...
    That seems to work like a charm. But one thing is puzzling me - how did you get the Count of entires in the Notes field? My guess is that it is the expression
    Code:
      lRow = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
    but I must confess I don't understand just what that does. Can you give a brief explanation? Thanks.
    Wendell

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I am not sure I understand your question. But here is an explanation of hte code (you may want to step through the code looking what each line does to the data...)

    The code first removes any subtotals (if any existed)
    It then creates a temp copy of the MbrNum column (which will be deleted later) to add the subtotal info
    The subtotals are created in the temp column putting the totals on TOP of the datarange
    The Grand total row is deleted
    The last filled row is obtained by going to the last cell of the worksheet and moving up.
    Autofilter is used to just show the rows that do not have "Count at the end" - the ones with count are the new subtotal rows (which will be used as "headers" for the section)
    The visible cells in Temp column A (which are the rows to be "expanded") are cleared
    The autofilter is removed [The temp column A now only contains items in what will be the Header rows]
    The loop starts at the last row of data and moves up thru the blank cells to the Last header row. It then copies the items from cols B-L (original A-K) in the row below to be the header and then bolds it
    It repeats going to each header section and copying and bolding the row underneath until it gets to row 3 (the end of the loop)
    It then deletes the temp column created at the beginning. The code does not "count" the items, it just uses the temp column to move up from header item to the next header item. I let the built-in excel features (subtotal and autofilter) do most of the "heavy lifting) so I did not have to make complicated code...

    Steve
    Last edited by WendellB; 2011-03-21 at 11:36.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Thanks very much Steve, I had deduced most of that from an examination of the code, and after further investigation, I decided the count of columns with an entry in the Notes column actually came from the Subtotals calculation. It turns out the count is actually useful to the people who want to use this data. And the performance actually isn't bad - it takes only 3 or 4 minutes to process the 28K rows. So Well Done and thanks again.
    Wendell

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You are welcome. It could be faster if you turned off screen-updating (application.screenupdating = true) during the code running and then turned it on at the end.

    You could add some statusbar to let the user know it has not hung (updating it in the loop) since it takes so long...

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •