chriscorpion786
Board Regular
- Joined
- Apr 3, 2011
- Messages
- 112
- Office Version
- 365
- Platform
- Windows
Hi Everbody,
I have done the summary using Dictionary in VBA, but i have many loops in my code. Is there a shorter method that I can use or nest the loops one inside the other.
Secondly , I am looking for a solution to summarize the data by name and by Location, 2 criterias using the same code with Dictionary.
I have put the sample below. How can I attach a file, if anyone needs to see the file.
Kindly provide a solution.
[TABLE="width: 767"]
<tbody>[TR]
[TD]Name[/TD]
[TD]CAT[/TD]
[TD]Sales[/TD]
[TD]Location[/TD]
[TD]Name[/TD]
[TD]Totals[/TD]
[TD][/TD]
[TD]CAT[/TD]
[TD]Totals[/TD]
[TD][/TD]
[TD]LOCATION[/TD]
[TD]Totals[/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD]FHR[/TD]
[TD="align: right"]27[/TD]
[TD]U.K[/TD]
[TD]Chris[/TD]
[TD="align: right"]9408[/TD]
[TD][/TD]
[TD]FHR[/TD]
[TD="align: right"]15168[/TD]
[TD][/TD]
[TD]U.K[/TD]
[TD="align: right"]10987[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]MHR[/TD]
[TD="align: right"]25[/TD]
[TD]U.K[/TD]
[TD]Mike[/TD]
[TD="align: right"]3712[/TD]
[TD][/TD]
[TD]MHR[/TD]
[TD="align: right"]15680[/TD]
[TD][/TD]
[TD]US[/TD]
[TD="align: right"]8817[/TD]
[/TR]
[TR]
[TD]Ali[/TD]
[TD]FHR[/TD]
[TD="align: right"]39[/TD]
[TD]U.K[/TD]
[TD]Ali[/TD]
[TD="align: right"]12928[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CHINA[/TD]
[TD="align: right"]11044[/TD]
[/TR]
[TR]
[TD]Davis[/TD]
[TD]MHR[/TD]
[TD="align: right"]14[/TD]
[TD]U.K[/TD]
[TD]Davis[/TD]
[TD="align: right"]4800[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]MHR[/TD]
[TD="align: right"]33[/TD]
[TD]US[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD]MHR[/TD]
[TD="align: right"]41[/TD]
[TD]CHINA[/TD]
[TD][/TD]
[TD][/TD]
[TD]U.K[/TD]
[TD]US[/TD]
[TD]CHINA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ali[/TD]
[TD]FHR[/TD]
[TD="align: right"]20[/TD]
[TD]U.K[/TD]
[TD][/TD]
[TD]Chris[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Davis[/TD]
[TD]FHR[/TD]
[TD="align: right"]22[/TD]
[TD]US[/TD]
[TD][/TD]
[TD]Mike[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ali[/TD]
[TD]FHR[/TD]
[TD="align: right"]14[/TD]
[TD]CHINA[/TD]
[TD][/TD]
[TD]Ali[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ali[/TD]
[TD]FHR[/TD]
[TD="align: right"]17[/TD]
[TD]U.K[/TD]
[TD][/TD]
[TD]Davis[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD]FHR[/TD]
[TD="align: right"]45[/TD]
[TD]U.K[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ali[/TD]
[TD]MHR[/TD]
[TD="align: right"]24[/TD]
[TD]CHINA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Davis[/TD]
[TD]MHR[/TD]
[TD="align: right"]39[/TD]
[TD]U.K[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ali[/TD]
[TD]FHR[/TD]
[TD="align: right"]26[/TD]
[TD]CHINA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I have done the summary using Dictionary in VBA, but i have many loops in my code. Is there a shorter method that I can use or nest the loops one inside the other.
Secondly , I am looking for a solution to summarize the data by name and by Location, 2 criterias using the same code with Dictionary.
I have put the sample below. How can I attach a file, if anyone needs to see the file.
Kindly provide a solution.
[TABLE="width: 767"]
<tbody>[TR]
[TD]Name[/TD]
[TD]CAT[/TD]
[TD]Sales[/TD]
[TD]Location[/TD]
[TD]Name[/TD]
[TD]Totals[/TD]
[TD][/TD]
[TD]CAT[/TD]
[TD]Totals[/TD]
[TD][/TD]
[TD]LOCATION[/TD]
[TD]Totals[/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD]FHR[/TD]
[TD="align: right"]27[/TD]
[TD]U.K[/TD]
[TD]Chris[/TD]
[TD="align: right"]9408[/TD]
[TD][/TD]
[TD]FHR[/TD]
[TD="align: right"]15168[/TD]
[TD][/TD]
[TD]U.K[/TD]
[TD="align: right"]10987[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]MHR[/TD]
[TD="align: right"]25[/TD]
[TD]U.K[/TD]
[TD]Mike[/TD]
[TD="align: right"]3712[/TD]
[TD][/TD]
[TD]MHR[/TD]
[TD="align: right"]15680[/TD]
[TD][/TD]
[TD]US[/TD]
[TD="align: right"]8817[/TD]
[/TR]
[TR]
[TD]Ali[/TD]
[TD]FHR[/TD]
[TD="align: right"]39[/TD]
[TD]U.K[/TD]
[TD]Ali[/TD]
[TD="align: right"]12928[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CHINA[/TD]
[TD="align: right"]11044[/TD]
[/TR]
[TR]
[TD]Davis[/TD]
[TD]MHR[/TD]
[TD="align: right"]14[/TD]
[TD]U.K[/TD]
[TD]Davis[/TD]
[TD="align: right"]4800[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]MHR[/TD]
[TD="align: right"]33[/TD]
[TD]US[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD]MHR[/TD]
[TD="align: right"]41[/TD]
[TD]CHINA[/TD]
[TD][/TD]
[TD][/TD]
[TD]U.K[/TD]
[TD]US[/TD]
[TD]CHINA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ali[/TD]
[TD]FHR[/TD]
[TD="align: right"]20[/TD]
[TD]U.K[/TD]
[TD][/TD]
[TD]Chris[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Davis[/TD]
[TD]FHR[/TD]
[TD="align: right"]22[/TD]
[TD]US[/TD]
[TD][/TD]
[TD]Mike[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ali[/TD]
[TD]FHR[/TD]
[TD="align: right"]14[/TD]
[TD]CHINA[/TD]
[TD][/TD]
[TD]Ali[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ali[/TD]
[TD]FHR[/TD]
[TD="align: right"]17[/TD]
[TD]U.K[/TD]
[TD][/TD]
[TD]Davis[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD]FHR[/TD]
[TD="align: right"]45[/TD]
[TD]U.K[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ali[/TD]
[TD]MHR[/TD]
[TD="align: right"]24[/TD]
[TD]CHINA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Davis[/TD]
[TD]MHR[/TD]
[TD="align: right"]39[/TD]
[TD]U.K[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ali[/TD]
[TD]FHR[/TD]
[TD="align: right"]26[/TD]
[TD]CHINA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Code:
Sub SummaryCategoriesinDictionary()
Dim Catdict As Dictionary
Set Catdict = New Dictionary
Dim Namedict As Dictionary
Set Namedict = New Dictionary
Dim Locdict As Dictionary
Set Locdict = New Dictionary
Dim lastrow As Long
Dim x As Long
Dim key As Variant
Dim name As String
Dim cat As String
Dim value As Integer
Dim location As String
Range("E1:L5").ClearContents
lastrow = Range("A2", Range("A2").End(xlDown)).Rows.Count
lastrow = lastrow + 1
For x = 2 To lastrow
name = Cells(x, 1).value
cat = Cells(x, 2).value
location = Cells(x, 4).value
value = Cells(x, 3).value
Namedict(name) = Namedict(name) + value
Catdict(cat) = Catdict(cat) + value
Locdict(location) = Locdict(location) + value
Next x
'For Names
x = 2
For Each key In Namedict.Keys
Range("E1").value = "Name"
Range("F1").value = "Totals"
Cells(x, 5).value = key
Cells(x, 6).value = Namedict(key)
x = x + 1
Next key
'For Category
x = 2
For Each key In Catdict.Keys
Range("H1").value = "CAT"
Range("I1").value = "Totals"
Cells(x, 8).value = key
Cells(x, 9).value = Catdict(key)
x = x + 1
Next key
'For Location
x = 2
For Each key In Locdict.Keys
Range("K1").value = "LOCATION"
Range("L1").value = "Totals"
Cells(x, 11).value = key
Cells(x, 12).value = Locdict(key)
x = x + 1
Next key
End Sub
Last edited by a moderator: