chriscorpion786
Board Regular
- Joined
- Apr 3, 2011
- Messages
- 108
- 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.
<tbody>
</tbody>
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.
| Name | CAT | Sales | Location | Name | Totals | CAT | Totals | LOCATION | Totals | ||
| Chris | FHR | 27 | U.K | Chris | 9408 | FHR | 15168 | U.K | 10987 | ||
| Mike | MHR | 25 | U.K | Mike | 3712 | MHR | 15680 | US | 8817 | ||
| Ali | FHR | 39 | U.K | Ali | 12928 | CHINA | 11044 | ||||
| Davis | MHR | 14 | U.K | Davis | 4800 | ||||||
| Mike | MHR | 33 | US | ||||||||
| Chris | MHR | 41 | CHINA | U.K | US | CHINA | |||||
| Ali | FHR | 20 | U.K | Chris | |||||||
| Davis | FHR | 22 | US | Mike | |||||||
| Ali | FHR | 14 | CHINA | Ali | |||||||
| Ali | FHR | 17 | U.K | Davis | |||||||
| Chris | FHR | 45 | U.K | ||||||||
| Ali | MHR | 24 | CHINA | ||||||||
| Davis | MHR | 39 | U.K | ||||||||
| Ali | FHR | 26 | CHINA |
<tbody>
</tbody>
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:
