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 />

 

[Weaver]

I strongly suspect there's a non vb way to do this, but I don't know it.

If you're not averse to using vb, here's a stab at it. Right click the tab of the sheet with the data, choose 'view code' and paste the following in there.

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Column
        Case 2, 3
            Application.EnableEvents = False
            Range("F1").CurrentRegion.ClearContents
            With CreateObject("scripting.dictionary")
                lr = Cells(Rows.Count, "B").End(xlUp).Row
                For i = 2 To lr
                    v = Cells(i, "C").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
                Range("F2").Resize(.Count, 2).Value = WorksheetFunction.Transpose(Array(.keys, .items))
            End With
            Range("F1:G1").Value = Array("Name", "Total")
            Range("F1").CurrentRegion.Sort key1:=Range("G1"), order1:=xlDescending, Header:=xlYes
    End Select
safeExit:
    Application.EnableEvents = True
End Sub

It will check to see if anything has changed in columns B or C and redraw the sorted table as required. Note that it only triggers when a cell in the given columns actually changes its value.

HTH

 

[brjohnson]

Weaver-

Thank You! This looks perfect and works well. I know nothing about vb and am really trying to get at solving this problem - http://www.mrexcel.com/forum/showthread.php?t=581587 - if you are willing to take a look at that and advise as to any big changes that i'll have to make to your code I'd really appreciate it.

-Brian

 

[Biz]

I strongly suspect there's a non vb way to do this, but I don't know it.

If you're not averse to using vb, here's a stab at it. Right click the tab of the sheet with the data, choose 'view code' and paste the following in there.

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Column
        Case 2, 3
            Application.EnableEvents = False
            Range("F1").CurrentRegion.ClearContents
            With CreateObject("scripting.dictionary")
                lr = Cells(Rows.Count, "B").End(xlUp).Row
                For i = 2 To lr
                    v = Cells(i, "C").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
                Range("F2").Resize(.Count, 2).Value = WorksheetFunction.Transpose(Array(.keys, .items))
            End With
            Range("F1:G1").Value = Array("Name", "Total")
            Range("F1").CurrentRegion.Sort key1:=Range("G1"), order1:=xlDescending, Header:=xlYes
    End Select
safeExit:
    Application.EnableEvents = True
End Sub

It will check to see if anything has changed in columns B or C and redraw the sorted table as required. Note that it only triggers when a cell in the given columns actually changes its value.

HTH

Very nice script m8.

Biz

 

[brjohnson]

As a follow up - am I a dreamer for thinking something like the below is possible? I'm trying to research how to sort portions and then move on to the next blend and am just creating errors in the code



<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 /><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><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Have This Data</td><td style=";">Blend 1</td><td style=";">Blend 2</td><td style=";">Blend 3</td><td style=";">… Blend 18</td><td style="text-align: right;;"></td><td style=";">want this output</td><td style=";">People names</td><td style=";">people sums (per blend)</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;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Blend1</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;;">2</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Blend1</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;;">3</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Blend1</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;;">4</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Blend1</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;;">5</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Blend1</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;;">6</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Blend1</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;;">4</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Blend1</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;;">2</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Blend1</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;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Blend2</td><td style=";">Sally</td><td style="text-align: right;;">6</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;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Blend2</td><td style=";">Hubert</td><td style="text-align: right;;">5</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;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Blend2</td><td style=";">Ethan</td><td style="text-align: right;;">4</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;;">0</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Blend2</td><td style=";">Jane</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">14</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><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Blend2</td><td style=";">Brian</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">15</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><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Blend2</td><td style=";">Mark</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">16</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><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Blend2</td><td style=";">Johnny</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">17</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><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Blend3</td><td style=";">Robert</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">18</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><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">…Blend18</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">Sheet3</p><br /><br />

 

[Weaver]

I think the current code could be adapted to do that - I'll give it a go later.

Biz - thanks, mate. Maybe I should've paid more attention to the online poker game I was involved in at the time - went out to a very obvious 'all in with nothing on the river'.

 

[brjohnson]

Thanks Weaver. Appreciate the help.

-Brian

 

[Weaver]

If you have up to 18 blends where would your reconstructed table actually start? Would you need to leave space for more?

 

[brjohnson]

I have 18 right now but would love to be able to add more later. If it is possible to post the reconstructed table to a separate sheet that is fine. Otherwise it'd be easy to start far off to the right and I'll reference and display it on a separate worksheet.

So... adding more would be helpful and starting at say row column 100 or so - whatever that would correspond to alphabetically would be awesome.

 

[Weaver]

I have 18 right now but would love to be able to add more later. If it is possible to post the reconstructed table to a separate sheet that is fine. Otherwise it'd be easy to start far off to the right and I'll reference and display it on a separate worksheet.

So... adding more would be helpful and starting at say row column 100 or so - whatever that would correspond to alphabetically would be awesome.

To be fair, I think a separate sheet is much the better option. On the data sheet, would there be any other data below or to the right of the main table. Better that there isn't.