vba Collection/Dictionary Summarise Data

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
983
Office Version
  1. 2010
Platform
  1. Windows
Hi Team,

I have below data, Player Name in Column A and their total Scores Every Year.
and Criteria List is in Column F. as per Criteria List it has to show output in Column H & I.

I am looking for answer in collection or Dictionary you can take help of class module if needed. I am trying to learn collection/dictionary how to use it in below Scenario.

Name201720182019Criteria ListExpected Output
Sachin Tendulkar10007001500Sachin TendulkarPlayer NameTotal Scores
Rohit Sharma8005001400DhoniSachin Tendulkar3200
Virat Kohli9006001600Ricky PontingDhoni3000
Jason Roy7001400500Davin WarnerRicky Ponting2500
Buttler4009001100JaysuryaDavin Warner4000
Jaysurya800600400ButtlerJaysurya1800
Ricky Ponting1200900400Buttler2400
Dhoni50015001000
Davin Warner20001500500
 

Attachments

  • Input file with expected result.png
    Input file with expected result.png
    18.4 KB · Views: 9

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Try this macro:
VBA Code:
Sub Mallesh()
    Application.ScreenUpdating = False
    Dim bottomF As Long, rName As Range, fnd As Range
    bottomF = Range("F" & Rows.Count).End(xlUp).Row
    For Each rName In Range("F2:F" & bottomF)
        Set fnd = Range("A:A").Find(rName, LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            Cells(Rows.Count, "H").End(xlUp).Offset(1) = rName
            Cells(Rows.Count, "I").End(xlUp).Offset(1) = WorksheetFunction.Sum(fnd.Offset(, 1).Resize(, 3))
        End If
    Next rName
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Hi Mumps,

Superb!!! Thank you so much , it worked as expected !!! ?


Thanks
mg
 
Upvote 0
You could also do that quite simply with a formula

+Fluff.xlsm
ABCDEFG
1Name201720182019Criteria List
2Sachin Tendulkar10007001500Sachin Tendulkar3200
3Rohit Sharma8005001400Dhoni3000
4Virat Kohli9006001600Ricky Ponting2500
5Jason Roy7001400500Davin Warner4000
6Buttler4009001100Jaysurya1800
7Jaysurya800600400Buttler2400
8Ricky Ponting1200900400
9Dhoni50015001000
10Davin Warner20001500500
11
Main
Cell Formulas
RangeFormula
G2:G7G2=SUM(INDEX($A$2:$D$10,MATCH(F2,$A$2:$A$10,0),0))
 
Upvote 0
Hi Fluff,
Thanks for adding one more solution !!! ?


Regards,
mg
 
Upvote 0

Forum statistics

Threads
1,223,999
Messages
6,175,882
Members
452,679
Latest member
darryl47nopra

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