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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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

P
  • Solved
Vlookup and match issue
Replies
7
Views
91
etaf
etaf
H
  • Question Question
Transposing Data - Evaluating formula to an array error.
Replies
1
Views
59
Skyybot
Skyybot
M
  • Question Question
Index the value with the criteria and get the lowest with in the range
Replies
2
Views
189
mart_mrexcel
M
I
  • Question Question
Formula to get recent payment date
Replies
4
Views
120
InvoicingNoob
I
D
  • Question Question
Compile error: Wrong number of arguments or invalid property assignment
Replies
2
Views
74
daveyc18
D

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

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