VBA Count add to Sheet2
Results 1 to 5 of 5

Thread: VBA Count add to Sheet2
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2011
    Posts
    204
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Count add to Sheet2

    I have about 40,000 rows of data on sheet1 like below. What I need to do is to give a summary of grade level on sheet2. Please see below data.
    Grade Test Level #
    3
    1 17
    2 10
    3 6
    4
    1 20
    2 8
    3 3
    5
    1 14
    2 9
    3 16
    6
    1 16
    2 11
    3 6
    7
    1 18
    2 11
    3 5
    8
    1 18
    2 13
    3 3
    3
    1 16
    2 10
    3 5
    4
    1 15
    2 17
    3 4
    So that the data looks like this:
    Grade Test Level #
    3 1 33
    2 20
    3 11
    4 1 35
    2 25
    3 7
    5 1 14
    2 9
    3 16
    6 1 16
    2 11
    3 6
    7 1 18
    2 11
    3 5
    8 1 18
    2 13
    3 3

  2. #2
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,375
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Count add to Sheet2

    You can transform your original table with Power Query,

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Fill = Table.FillDown(Source,{"Grade"}),
        Filter = Table.SelectRows(Fill, each ([Test Level] <> null)),
        Group = Table.Group(Filter, {"Grade", "Test Level"}, {{"Total", each List.Sum([#"#"]), type number}})
    in
        Group
    This doesn't have the blank rows below the Grade values like in your example, it goes 1,1,1,2,2,2,etc. But after you've done the PQ transform, you adjust the visuals by turning the result into a pivot table.
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  3. #3
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,862
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Count add to Sheet2

    I used Power Query to get the data in a form that I could then create a pivot table.

    Here is the Mcode for the data rearranging

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Grade", Int64.Type}, {"Test Level", Int64.Type}, {"#", Int64.Type}}),
        #"Filled Down" = Table.FillDown(#"Changed Type",{"Grade"}),
        #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Test Level] <> null))
    in
        #"Filtered Rows"
    Here is what the data looks like and then pivotted.

    v A B C D E F G
    1 Grade Test Level #
    2 3 1 17
    3 3 2 10 Grade Test Level Sum of #
    4 3 3 6 3
    5 4 1 20 1 33
    6 4 2 8 2 20
    7 4 3 3 3 11
    8 5 1 14 4
    9 5 2 9 1 35
    10 5 3 16 2 25
    11 6 1 16 3 7
    12 6 2 11 5
    13 6 3 6 1 14
    14 7 1 18 2 9
    15 7 2 11 3 16
    16 7 3 5 6
    17 8 1 18 1 16
    18 8 2 13 2 11
    19 8 3 3 3 6
    20 3 1 16 7
    21 3 2 10 1 18
    22 3 3 5 2 11
    23 4 1 15 3 5
    24 4 2 17 8
    25 4 3 4 1 18
    26 2 13
    27 3 3
    28
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  4. #4
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,375
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Count add to Sheet2

    Here's a VBA solution as well. This will output the results beginning in column J. You can adjust that bit of code to put the results where you want them.

    Code:
    Sub MXL201907202()
    Dim AR() As Variant: AR = Range("A1:C" & Range("A" & Rows.Count).End(xlUp).Row).Value
    Dim SD As Object: Set SD = CreateObject("Scripting.Dictionary")
    Dim AL As Object: Set AL = CreateObject("System.Collections.ArrayList")
    Dim tmp As String, grade As String
    Dim Last As Long: Last = 0
    
    For a = LBound(AR) + 1 To UBound(AR)
        If Not IsEmpty(AR(a, 1)) Then AL.Add a
    Next a
    
    For i = 0 To AL.Count - 1
        grade = AR(AL(i), 1)
        If i < AL.Count - 1 Then Last = AL(i + 1) - 1 Else Last = UBound(AR)
        For j = AL(i) + 1 To Last
            tmp = grade & "-" & AR(j, 2)
            SD(tmp) = SD(tmp) + AR(j, 3)
        Next j
    Next i
    
    Range("J1:K1") = Array("Grade-Level", "Total")
    
    With Range("J2").Resize(SD.Count, 1)
        .Value = Application.Transpose(SD.keys)
        .Offset(, 1).Value = Application.Transpose(SD.items)
    End With
    
    With Range("J1").CurrentRegion
        .Sort Key1:=.Cells(1, 1), Order1:=xlAscending, Header:=xlYes
    End With
    
    End Sub
    Last edited by lrobbo314; Jul 20th, 2019 at 08:33 PM.
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  5. #5
    Board Regular
    Join Date
    Jun 2011
    Posts
    204
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Count add to Sheet2

    Thank you both!!! Amazing!! Did exactly what I needed it to do! Thanks again!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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