View Full Version : counting non numeric with pivot tables
Jenst
09-25-2016, 03:27 AM
Hi! I have a table in which I have three non numeric columns and I want to count them(G1,simplyfied). I only get it managed to count one of the columns in a pivot table, but I want to have the result you can see in G3 in a pivot table.
Has anyone an idea how to do it, at first without vba?
G1
Rating1
Rating2
Rating3
a
a
b
a
b
b
a
a
b
c
c
a
G3
Category
Count
a
6
b
4
c
2
Many thanks, Jens
Why do you want to use pivot?
Why don't you want to use VBA?
It's easy to achieve it by VBA.
Jenst
09-25-2016, 02:32 PM
Hi Mana, ok, I have done it without pivot tables now. ;)
Please try this.
Option Explicit
Sub test()
Dim dic As Object
Dim c As Range
Set dic = CreateObject("scripting.dictionary")
For Each c In Sheets("sheet1").Range("a2:c5")
dic(c.Value) = dic(c.Value) + 1
Next
With Sheets("sheet2")
.UsedRange.ClearContents
.Range("a1:b1").Value = Array("category", "count")
.Range("a2").Resize(dic.Count).Value = WorksheetFunction.Transpose(dic.keys)
.Range("b2").Resize(dic.Count).Value = WorksheetFunction.Transpose(dic.items)
End With
End Sub
Jenst
09-26-2016, 04:54 PM
Great, even simpler than my solution. Thank you mana for all your tips and helping solutions.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.