VBA Count add to Sheet2

A

anthonyexcel

Active Member
Joined
Jun 10, 2011
Messages
258
Office Version
  1. 365
Platform
  1. Windows
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.
<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Grade[/TH]
[TH]Test Level[/TH]
[TH]#[/TH]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]


So that the data looks like this:


[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Grade[/TH]
[TH]Test Level[/TH]
[TH]#[/TH]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]1[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
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.
 
Upvote 0
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.

[table="class:thin_grid"]
[tr][td]v[/td]
[td="bgcolor:#ECF0F0, align:center"]A[/td]
[td="bgcolor:#ECF0F0, align:center"]B[/td]
[td="bgcolor:#ECF0F0, align:center"]C[/td]
[td="bgcolor:#ECF0F0, align:center"]D[/td]
[td="bgcolor:#ECF0F0, align:center"]E[/td]
[td="bgcolor:#ECF0F0, align:center"]F[/td]
[td="bgcolor:#ECF0F0, align:center"]G[/td]
[/tr][tr][td="bgcolor:#ECF0F0, align:center"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Grade[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Test Level[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]#[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]17[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]10[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Grade[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Test Level[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Sum of #[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]6[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]5[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]20[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]33[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]6[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]8[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]20[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]7[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]11[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]8[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]5[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]14[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]9[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]5[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]9[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]35[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]10[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]5[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]16[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]25[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]11[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]6[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]16[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]7[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]12[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]6[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]11[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]5[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]13[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]6[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]6[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]14[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]14[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]7[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]18[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]9[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]15[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]7[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]11[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]16[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]16[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]7[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]5[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]6[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]17[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]8[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]18[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]16[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]18[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]8[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]13[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]11[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]19[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]8[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]6[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]20[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]16[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]7[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]21[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]10[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]18[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]22[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]5[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]11[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]23[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]15[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]5[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]24[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]17[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]8[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]25[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]18[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]26[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]13[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]27[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]3[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]28[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[/table]
 
Upvote 0
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:
Upvote 0
Thank you both!!! Amazing!! Did exactly what I needed it to do! Thanks again!
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Transposing Data - Evaluating formula to an array error.
Replies
1
Views
42
Skyybot
Skyybot
M
  • Question Question
Index the value with the criteria and get the lowest with in the range
Replies
2
Views
186
mart_mrexcel
M
D
  • Question Question
Compile error: Wrong number of arguments or invalid property assignment
Replies
2
Views
71
daveyc18
D
G
  • Solved
If Else Loop macro running slow, anyway to speed it up?
Replies
9
Views
291
Alex Blakenburg
A
GMC The Macro Man
  • Question Question
Count with Multiple Criteria - Follow up
Replies
2
Views
202
myall_blues
myall_blues

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top