Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Subtotal Rows & Summary Report Of Dynamic Table

  1. #1
    Join Date
    25th November 2008
    Posts
    14

    Subtotal Rows & Summary Report Of Dynamic Table

    Hello

    I am new to Excel VBA and I am having a tough time writing a VBA to convert a set of data in sheet 1 to one in sheet 2(I am enclosing that as a Excel document"Test -Original").I have described what needs to be done (step by step ) below.

    Could you please help ?Any input will be greatly appreciated

    Thanks

    Raja

    Develop a macro
    1. I have a report from SAP BW, the original format of which is in sheet 1.I need to develop a macro using VBA and need the report with the format in sheet 2.

    2. I need a “Results “row after every Bill to Party in column A as below(screen shot 1.doc):



    P.S: The number of customers is dynamic i.e.it keeps changing every month

    4. Nothing needs to be done to column.SAP BW will not overwrite the format and the data in column G.Hence leave it as it is.

    5. Calculate the number of Sales document numbers for each customer and put the value of 1 for every value. If it is blank it should not be counted and put the value of 0 for those rows. (Shown below) Display the sum of the number of sales document numbers in the results row for column H

    6. In the column I, put the value of 1 if the difference column (column G) is 0 and put the value of 0 if the value in the difference column (column G) is any value apart from 0.now sum the value in the results row for each customer and display the summation value in the results row under column I
    7. Compute the percentage which is the values in (column I/Column J)*100 .This should be done only for the results row
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    5,218

    Re: For Inserting A Blank Row And Performing Calculations

    Pivot Table seems like a good option...

    Check out our new reputation system. Click on the "star" under the post!
    _______________________________________________

    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

  3. #3
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: For Inserting A Blank Row And Performing Calculations

    Hi,

    Try,

    VB:
    Sub kTest() 
        Dim a, i As Long, n As Long, w(), CntA As Long, BP, c As Long 
        Dim SumSD As Long, cDiff As Long, Per As Single 
         
        CntA = Application.WorksheetFunction.CountA(Range("a13:a" & _ 
        Range("c" & Rows.Count).End(xlUp).Row)) 
        a = Range("a13:f" & Range("c" & Rows.Count).End(xlUp).Row) 
         
        Redim w(1 To UBound(a, 1) + CntA, 1 To 10) 
         
        For i = 1 To UBound(a, 1) 
            n = n + 1 
            If i = 1 Then 
                BP = a(i, 1) 
                For c = 1 To 6: w(n, c) = a(i, c): Next 
                    If a(i, 5) = "#" Then w(n, 7) = 0 Else: w(n, 7) = CDate(a(i, 6)) - CDate(a(i, 5)) 
                    If Trim(a(i, 3)) <> "" Then w(n, 8) = 1 Else: w(n, 8) = 0 
                    If w(n, 7) = 0 Then w(n, 9) = 1 Else: w(n, 9) = 0 
                    SumSD = SumSD + w(n, 8): cDiff = cDiff + w(n, 9) 
                ElseIf IsEmpty(a(i, 1)) Then 
                    For c = 1 To 6: w(n, c) = a(i, c): Next 
                        If a(i, 5) = "#" Then w(n, 7) = 0 Else: w(n, 7) = CDate(a(i, 6)) - CDate(a(i, 5)) 
                        If Trim(a(i, 3)) <> "" Then w(n, 8) = 1 Else: w(n, 8) = 0 
                        If w(n, 7) = 0 Then w(n, 9) = 1 Else: w(n, 9) = 0 
                        SumSD = SumSD + w(n, 8): cDiff = cDiff + w(n, 9) 
                    ElseIf Not IsEmpty(a(i, 1)) Then 
                        w(n, 2) = "Results" 
                        w(n, 8) = SumSD: w(n, 9) = cDiff: w(n, 10) = (cDiff / SumSD) * 100 
                        SumSD = 0: cDiff = 0 
                        BP = a(i, 1): n = n + 1 
                        For c = 1 To 6: w(n, c) = a(i, c): Next 
                            If a(i, 5) = "#" Then w(n, 7) = 0 Else: w(n, 7) = CDate(a(i, 6)) - CDate(a(i, 5)) 
                            If Trim(a(i, 3)) <> "" Then w(n, 8) = 1 Else: w(n, 8) = 0 
                            If w(n, 7) = 0 Then w(n, 9) = 1 Else: w(n, 9) = 0 
                            SumSD = SumSD + w(n, 8): cDiff = cDiff + w(n, 9) 
                        End If 
                    Next 
                    n = n + 1 
                    w(n, 2) = "Results" 
                    w(n, 8) = SumSD: w(n, 9) = cDiff: w(n, 10) = (cDiff / SumSD) * 100 
                    With Range("a12") 
                        .Offset(, 6).Resize(, 4).Value = Array("Difference", "Count(Sales Doc)", "Count(Diff)", "Pecentage(%)") 
                        .Offset(1).Resize(n, 10).Value = w 
                    End With 
                End Sub 
    
    
    HTH

  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

    Re: For Inserting A Blank Row And Performing Calculations

    Quote Originally Posted by Ger Plante
    Pivot Table seems like a good option...
    Agree. K.I.S.S

  5. #5
    Join Date
    25th November 2008
    Posts
    14

    Re: Subtotal Rows & Summary Report Of Dynamic Table

    Hi Kris,

    Thanks a lot for the help.The VBA code really works!!!You are a Star.Thanks once again.

    Just a quick question.The new columns need to have the same format as the old ones i.e.as we use format painter in Excel to copy the format from the old columns and rows.Is that possible?What I did was to record a macro to do this for one cell.The code that is gave me was below:
    VB:
    Range("A12").Select 
    Selection.Copy 
    Range("C32:F32").Select 
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ 
    SkipBlanks:=False, Transpose:=False 
    Application.CutCopyMode = False 
    Selection.Copy 
    
    

    But I need the formatting to be for dynamic rows and columns.Could you please help?.

    I am really sorry for bothering you on this.

    Thanks

    Raja
    Last edited by Krishnakumar; December 3rd, 2008 at 01:02.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Subtotal Rows & Summary Report Of Dynamic Table

    Hi,

    Use code tags while posting codes.

    change the last part

    VB:
    With Range("a12") 
        .Offset(, 6).Resize(, 4).Value = Array("Difference", "Count(Sales Doc)", "Count(Diff)", "Pecentage(%)") 
        .Offset(1).Resize(n, 10).Value = w 
        .Offset(1).Rows.Copy 
        .Offset(1).Resize(n, 10).PasteSpecial xlPasteFormats 
        .Offset(1, 4).Resize(n, 2).NumberFormat = "dd/mm/yyyy" 
        Application.CutCopyMode = 0 
    End With 
    
    
    HTH

  7. #7
    Join Date
    25th November 2008
    Posts
    14

    Re: Subtotal Rows & Summary Report Of Dynamic Table

    Hi Kris,

    Thanks a lot for the help.Sorry to be a pain once again.

    The following headings do not appear to have the same format as the rest of the data.

    Difference Count(Sales Doc) Count(Diff) Pecentage(%)


    Could you please help?

    Thanks


    Raja

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Subtotal Rows & Summary Report Of Dynamic Table

    Hi,

    Change

    VB:
    .Offset(1).Resize(n, 10).PasteSpecial xlPasteFormats 
    
    
    to

    VB:
    .Resize(n + 1, 10).PasteSpecial xlPasteFormats 
    
    

  9. #9
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

    Re: Subtotal Rows & Summary Report Of Dynamic Table

    You SHOULD had taken the advice of the other 2 in this Thread and K.I.S.S

  10. #10
    Join Date
    25th November 2008
    Posts
    14

    Re: Subtotal Rows & Summary Report Of Dynamic Table

    Hi Kris,

    Thanks a lot for the help and sorry for the delay.Apologies....


    Thanks

    Raja

    Dave:The SAP BW that I am using and for which I need the macros, does not recognise the Pivot table hence could not take the advice from K.I.S.S.Thanks anyways.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Summary Report Of Dynamic Table Data
    By tsorvillo in forum Excel General
    Replies: 10
    Last Post: November 11th, 2008, 13:46
  2. Criteria Sum Report On Dynamic Table
    By TaiChi56 in forum Excel General
    Replies: 3
    Last Post: May 2nd, 2008, 10:41
  3. Summary Report Sheet From Data Table
    By Another_VBA_Newbie in forum Excel General
    Replies: 4
    Last Post: May 1st, 2008, 21:32
  4. Create Summary Report Of Table
    By Prakash1731 in forum Excel General
    Replies: 2
    Last Post: September 14th, 2007, 21:12
  5. Dynamic Rows For Pivot Table
    By Frank_ACN in forum Excel General
    Replies: 2
    Last Post: November 16th, 2006, 17:21

Bookmarks

Posting Permissions

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