Hi,
I have 2 column of values and I need to count the number of duplicates and sort them according to their type. Is there any fast way using formula or VBA to do this besides using a PivotTable? Currently I am using multiple FOR loops which makes it very slow.
Here's my data:
I will need to come up with
ID Type 123 New 123 New 100 Old 101 New 123 Old
ID New Old 123 2 1 100 1 101 1
Last edited by excelnewbie80; 06-18-2012 at 09:22 AM.
How about a pivot table?
Or------D------ ------E------ -F- -----G----- 1 Count of Type Column Labels 2 Row Labels New Old Grand Total 3 100 1 1 4 101 1 1 5 123 2 1 3 6 Grand Total 3 2 5
I2 copied to the right and down-I- -J- -K- 1 ID New Old 2 123 2 1 3 100 0 1 4 101 1 0
=COUNTIFS($A$2:$A$6,$I2,$B$2:$B$6,J$1)
Last edited by jeffreybrown; 06-18-2012 at 09:21 AM.
HTH
Regards, Jeff
Yup, I know Pivot Table is one way, are there other ways?
I made an addition to post #2
Try this:-
Results start "C1"
Regards MickSub MG18Jun39 Dim Rng As Range Dim Dn As Range Dim n As Long Dim Q Dim Col As Integer n = 1 Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp)) ReDim ray(1 To Rng.Count + 1, 1 To 3) ray(1, 1) = "I/D": ray(1, 2) = "New": ray(1, 3) = "Old" With CreateObject("scripting.dictionary") .CompareMode = vbTextCompare For Each Dn In Rng Col = IIf(Dn.Offset(, 1) = "New", 2, 3) If Not .Exists(Dn.Value) Then n = n + 1 ray(n, 1) = Dn ray(n, Col) = 1 .Add Dn.Value, Array(ray, n) Else Q = .Item(Dn.Value) ray(Q(1), Col) = ray(Q(1), Col) + 1 .Item(Dn.Value) = Q End If Next Range("C1").Resize(.Count + 1, 3) = ray End With End Sub
Thanks for the replies, I have tried both ways and in the end I still feel that pivot table is the fastest.
We are both glad to have helped, but in the end, glad you have something you can work with. Thanks for the feedback
Please don't forget to mark your thread as solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks