vba Collection/Dictionary Summarise Data

[Mallesh23]

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.

Name	2017	2018	2019		Criteria List		Expected Output
Sachin Tendulkar	1000	700	1500		Sachin Tendulkar		Player Name	Total Scores
Rohit Sharma	800	500	1400		Dhoni		Sachin Tendulkar	3200
Virat Kohli	900	600	1600		Ricky Ponting		Dhoni	3000
Jason Roy	700	1400	500		Davin Warner		Ricky Ponting	2500
Buttler	400	900	1100		Jaysurya		Davin Warner	4000
Jaysurya	800	600	400		Buttler		Jaysurya	1800
Ricky Ponting	1200	900	400				Buttler	2400
Dhoni	500	1500	1000
Davin Warner	2000	1500	500

 

[mumps]

Try this macro:

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

 

[Mallesh23]

Hi Mumps,

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


Thanks
mg

 

[Fluff]

You could also do that quite simply with a formula

+Fluff.xlsm
	A	B	C	D	E	F	G
1	Name	2017	2018	2019		Criteria List
2	Sachin Tendulkar	1000	700	1500		Sachin Tendulkar	3200
3	Rohit Sharma	800	500	1400		Dhoni	3000
4	Virat Kohli	900	600	1600		Ricky Ponting	2500
5	Jason Roy	700	1400	500		Davin Warner	4000
6	Buttler	400	900	1100		Jaysurya	1800
7	Jaysurya	800	600	400		Buttler	2400
8	Ricky Ponting	1200	900	400
9	Dhoni	500	1500	1000
10	Davin Warner	2000	1500	500
11
Main

Cell Formulas
Range	Formula
G2:G7	G2	=SUM(INDEX($A$2:$D$10,MATCH(F2,$A$2:$A$10,0),0))

 

[Mallesh23]

Hi Fluff,
Thanks for adding one more solution !!! ?


Regards,
mg

 

[Fluff]

You're welcome

 

[mumps]

My pleasure.