Creating a new list - removing entries with 0 totals and sorting

[brjohnson]

Hi,

I have an ever changing list of names and want to be able to create, automatically, a summary of names and corresponding totals as you see in the attached chart. Per that chart, I know how to do the sumif to add the totals but I don't know how to get the list of names with non-0 totals to populate column F automatically.

Secondarily, I'd like to be able to sort F and G by largest to smallest in column G.

Any ideas?

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Have This Data</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Want to create this list of names</td><td style=";">and then use sum if to total</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Andrew</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Mark</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Mark</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Johnny</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Brian</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Brian</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Ethan</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Hubert</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Hubert</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Ethan</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Sally</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Sally</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Jane</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Andrew</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Johnny</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Jane</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Mark</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Johnny</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Kevin</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Robert</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />

 

[brjohnson]

The main table can easily be completely free of all other data.

 

[Weaver]

Note, you'll either need to create a tab called 'results' or change the assignment in the code to a preexisting tab.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim src As Worksheet, dst As Worksheet
    On Error GoTo safeExit
    Set src = ActiveSheet
    Set dst = Sheets("results")
    Debug.Print Intersect(Target.EntireColumn, src.UsedRange).Address
    If Not Intersect(Target.EntireColumn, ActiveSheet.UsedRange) Is Nothing Then
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
        dst.Columns("A:C").ClearContents
        lc = Cells(1, Columns.Count).End(xlToLeft).Column
        lr = Cells(Rows.Count, "B").End(xlUp).Row
        For j = 3 To lc
            With CreateObject("scripting.dictionary")
                For i = 2 To lr
                    v = Cells(i, j).Value
                    If v <> 0 Then
                        n = Cells(i, "B").Value
                        If .exists(n) Then
                            .Item(n) = .Item(n) + v
                        Else
                            .Add n, v
                        End If
                    End If
                Next i
                c = .Count
                If c <> 0 Then
                    Set np = dst.Cells(Rows.Count, 1).End(xlUp).Offset(1)
                    Set nr = np.Offset(, 1).Resize(c, 2)
                    nr.Value = WorksheetFunction.Transpose(Array(.keys, .items))
                    nr.Sort key1:=nr.Cells(1, 2), order1:=xlDescending, Header:=xlNo
                    np.Resize(c).Value = src.Cells(1, j).Value
                End If
            End With
        Next j
        With dst
            With .Range("A1:C1")
                .Value = Array("want this output", "People names", "people sums (per blend)")
                .EntireColumn.AutoFit
            End With
        End With
    End If
safeExit:
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

 

[brjohnson]

Weaver-

THANK YOU. This is amazing.

Is it easy to switch the output column order so that the sum is displayed in column 2 and the name in column 3?

 

[brjohnson]

weaver -

no need to change the column order. All is well. Thanks again. Your help is/was very much appreciated.

-Brian

 

[Weaver]

No problem, thanks for the feedback.