Need to Count unique items in a column with VBA

[MikeJP]

I need to count the number of unique items in a column (excluding blank cells if possible) in VBA. I want to use the number as follows:

For x = 1 to (number of unique items)

To control how many times I pass through a loop.

Thanks
Mike Piles

 

[countingapples]

Ok. Here is the data I'm drawing from. I want to search column U.

IncidentPersonnelReportsTemplateVer111.xls
	S	T	U	V
1	Month	DayofWeek	IncidentTypeClass	IncidentType
2	November	Wednesday	Fire	Brush,orbrushandgrassmixturefire
3	November	Wednesday	Fire	Brush,orbrushandgrassmixturefire
4	November	Wednesday	Fire	Brush,orbrushandgrassmixturefire
5	November	Sunday	Fire	Brush,orbrushandgrassmixturefire
6	November	Sunday	Fire	Brush,orbrushandgrassmixturefire
7	November	Sunday	Fire	Brush,orbrushandgrassmixturefire
8	November	Thursday	Fire	Brush,orbrushandgrassmixturefire
9	November	Thursday	Fire	Brush,orbrushandgrassmixturefire
10	November	Thursday	Fire	Brush,orbrushandgrassmixturefire
11	November	Saturday	Rescue&EmergencyMedicalServiceIncidents	Motorvehicle/pedestrianaccident(MVPed)
12	November	Saturday	Rescue&EmergencyMedicalServiceIncidents	Motorvehicle/pedestrianaccident(MVPed)
13	November	Saturday	Rescue&EmergencyMedicalServiceIncidents	Motorvehicle/pedestrianaccident(MVPed)
14	November	Monday	Rescue&EmergencyMedicalServiceIncidents	Highanglerescue
15	November	Monday	Rescue&EmergencyMedicalServiceIncidents	Highanglerescue
PersonnelResponseData

This is the pivot table where I need the count to go, it should be before number of personnel.

IncidentPersonnelReportsTemplateVer111.xls
	A	B	C	D
1
2	UnitResponseSummary
3
4	Month	(All)
5	Year	(All)
6	Station	(All)
7	Shift	(All)
8	District	(All)
9	ActivityPeriod	(All)
10	IncidentType	(All)
11
12	UnitID	IncidentClass	NumberofPersonnel	TotalTime
13	101
14		Rescue&EmergencyMedicalServiceIncidents	9	135
15	101Total		9	135
16
17	201
18		FalseAlarm&FalseCall	40	336
19		Fire	30	435
20		HazardousConditions(Nofire)	4	112
21		Rescue&EmergencyMedicalServiceIncidents	49	1,829
22	201Total		123	2,712
23
Unit Response Summary

The piece of code I had posted before is the relevent section of code for the data field where the count should occur and become part of my table.

Does that clarify any further for you? The code you've given does indeed work, it just keeps telling me that I have no unique items.

 

[NateO]

The following works for me:

Book2
	A	B	C	D	E
1	Month	DayofWeek	IncidentTypeClass	IncidentType	UniqueCount
2	November	Wednesday	Fire	Brush,orbrushandgrassmixturefire	3
3	November	Wednesday	Fire	Brush,orbrushandgrassmixturefire	3
4	November	Wednesday	Fire	Brush,orbrushandgrassmixturefire
5	November	Sunday	Fire	Brush,orbrushandgrassmixturefire
6	November	Sunday	Fire	Brush,orbrushandgrassmixturefire
7	November	Sunday	Fire	Brush,orbrushandgrassmixturefire
8	November	Thursday	Fire	Brush,orbrushandgrassmixturefire
9	November	Thursday	Fire	Brush,orbrushandgrassmixturefire
10	November	Thursday	Fire	Brush,orbrushandgrassmixturefire
11	November	Saturday	Rescue&EmergencyMedicalServiceIncidents	Motorvehicle/pedestrianaccident(MVPed)
12	November	Saturday	Rescue&EmergencyMedicalServiceIncidents	Motorvehicle/pedestrianaccident(MVPed)
13	November	Saturday	Rescue&EmergencyMedicalServiceIncidents	Motorvehicle/pedestrianaccident(MVPed)
14	November	Monday	Rescue&EmergencyMedicalServiceIncidents	Highanglerescue
15	November	Monday	Rescue&EmergencyMedicalServiceIncidents	Highanglerescue
Sheet5

<font face=Courier New><SPAN style="color:darkblue">Function</SPAN> CntUnique(myRng<SPAN style="color:darkblue">As</SPAN> Range)<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Long</SPAN><SPAN style="color:darkblue">Dim</SPAN> Uni<SPAN style="color:darkblue">As</SPAN> Collection, cl<SPAN style="color:darkblue">As</SPAN> Range, LpRange<SPAN style="color:darkblue">As</SPAN> Range<SPAN style="color:darkblue">Dim</SPAN> clswfrm<SPAN style="color:darkblue">As</SPAN> Range, clswcst<SPAN style="color:darkblue">As</SPAN> Range<SPAN style="color:darkblue">Dim</SPAN> TotUni<SPAN style="color:darkblue">As</SPAN><SPAN style="color:darkblue">Long</SPAN><SPAN style="color:darkblue">On</SPAN><SPAN style="color:darkblue">Error</SPAN><SPAN style="color:darkblue">Resume</SPAN><SPAN style="color:darkblue">Next</SPAN><SPAN style="color:darkblue">Set</SPAN> clswfrm = myRng.SpecialCells(xlFormulas)<SPAN style="color:darkblue">Set</SPAN> clswcst = myRng.SpecialCells(xlConstants)<SPAN style="color:darkblue">Set</SPAN> myRng =<SPAN style="color:darkblue">Nothing</SPAN><SPAN style="color:green"><SPAN style="color:green">'free up memory</SPAN></SPAN><SPAN style="color:darkblue">On</SPAN><SPAN style="color:darkblue">Error</SPAN><SPAN style="color:darkblue">GoTo</SPAN> 0<SPAN style="color:darkblue">If</SPAN> clswfrm<SPAN style="color:darkblue">Is</SPAN><SPAN style="color:darkblue">Nothing</SPAN> And clswcst<SPAN style="color:darkblue">Is</SPAN><SPAN style="color:darkblue">Nothing</SPAN><SPAN style="color:darkblue">Then</SPAN>
    MsgBox "No Unique Cells"
    <SPAN style="color:darkblue">Exit</SPAN><SPAN style="color:darkblue">Function</SPAN>
    <SPAN style="color:darkblue">ElseIf</SPAN><SPAN style="color:darkblue">Not</SPAN> clswfrm<SPAN style="color:darkblue">Is</SPAN><SPAN style="color:darkblue">Nothing</SPAN> And<SPAN style="color:darkblue">Not</SPAN> clswcst<SPAN style="color:darkblue">Is</SPAN><SPAN style="color:darkblue">Nothing</SPAN><SPAN style="color:darkblue">Then</SPAN>
        <SPAN style="color:darkblue">Set</SPAN> LpRange = Union(clswcst, clswfrm)
    <SPAN style="color:darkblue">ElseIf</SPAN> clswfrm<SPAN style="color:darkblue">Is</SPAN><SPAN style="color:darkblue">Nothing</SPAN><SPAN style="color:darkblue">Then</SPAN><SPAN style="color:darkblue">Set</SPAN> LpRange = clswcst
    Else:<SPAN style="color:darkblue">Set</SPAN> LpRange = clswfrm<SPAN style="color:darkblue">End</SPAN><SPAN style="color:darkblue">If</SPAN><SPAN style="color:darkblue">Set</SPAN> clswfrm = Nothing:<SPAN style="color:darkblue">Set</SPAN> clswcst =<SPAN style="color:darkblue">Nothing</SPAN><SPAN style="color:green">'Free up memory</SPAN><SPAN style="color:darkblue">Set</SPAN> Uni =<SPAN style="color:darkblue">New</SPAN> Collection<SPAN style="color:darkblue">On</SPAN><SPAN style="color:darkblue">Error</SPAN><SPAN style="color:darkblue">Resume</SPAN><SPAN style="color:darkblue">Next</SPAN><SPAN style="color:darkblue">For</SPAN><SPAN style="color:darkblue">Each</SPAN> cl<SPAN style="color:darkblue">In</SPAN> LpRange
    Uni.Add cl.Value,<SPAN style="color:darkblue">CStr</SPAN>(cl.Value)<SPAN style="color:green">'assign unique key string</SPAN><SPAN style="color:darkblue">Next</SPAN> cl<SPAN style="color:darkblue">On</SPAN><SPAN style="color:darkblue">Error</SPAN><SPAN style="color:darkblue">GoTo</SPAN> 0<SPAN style="color:darkblue">Set</SPAN> LpRange =<SPAN style="color:darkblue">Nothing</SPAN><SPAN style="color:green"><SPAN style="color:green">'free up memory</SPAN></SPAN>
TotUni = Uni.Count
CntUnique = Uni.Count<SPAN style="color:darkblue">Set</SPAN> Uni =<SPAN style="color:darkblue">Nothing</SPAN><SPAN style="color:darkblue">End</SPAN><SPAN style="color:darkblue">Function</SPAN></FONT>

 

[countingapples]

Ok, I've put in the function and referenced it as follows:

With ActiveSheet.PivotTables("Unit Response Summary").PivotFields( _
        "Incident Type Class")
        .Orientation = xlDataField
        .Position = 1
        .Caption = "Count"
        .Function = CntUnique(Incident Type Class)
        .NumberFormat = "#,##0"
End With

It gives a "Compile error: Syntax Error" message. When I try and use "U:U" or "U" or "U,U" to reference the correct range, I get "Compile error: Type mismatch"

Any thoughts on what I'm doing wrong?

 

[NateO]

You can't use it in a PT. Not sure why you would...

 

[countingapples]

This is for a user interface. They will only see the pivot tables. The data sheet is going to be hidden.

Do I have any other options here?

 

[Ekim]

MikeJP,

You don’t mention the type of data that you are counting - numbers, alphas or alphanumerics? The following macro will count unique items for all types of data (blank cells are ignored). If you are counting just numbers, then change COUNTA to COUNT (assumes no zeros).

Assume that your data is in column A. Cell A1 must have a label. Added advantage of returning a list of the unique items in column B.

Count of unique items returned to cell D2.

Sub test1()
Range("A1:A65536").AdvancedFilter Action:=xlFilterCopy, _
   CopyToRange:=Cells(1, 2), Unique:=True
Range("D2").FormulaR1C1 = "=COUNTA(RC[-2]:R[64998]C[-2])"
End Sub

More “fancy”:

Sub test2()
Dim myRng As Range
Set myRng = Sheets(2).[A:A]

myRng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Cells(1, 2), Unique:=True

With Range("D2")
    .FormulaR1C1 = "=COUNTA(RC[-2]:R[64998]C[-2])"
    .Value = .Value
End With

End Sub

HTH


Mike

 

[NateO]

Hmmm, maybe turn off screen updates, filter in place, count the visible cells (special cells, one need not worry about count() or counta() here, although counta() should suffice), kill the filter, turn on your updates, and you have a VBA process, without mucking with your actual spreadsheet too much.

Apples, tack the count on beside the PT.

I'm still not following, what are you counting again?

I'm starting to think of sql and cross tabs, never have done one with an Excel table... Might be crazy enough to work...

 

[countingapples]

I need a count of each type of incident class for each unit ID. This number is then used in determining the average number of personnel for each incident class for each unit ID. This I was planning on putting to the side of the PT. I'm open to any ideas you may have...

 

[NateO]

Well, can't you simply change the sum here: http://www.cpearson.com/excel/pivots.htm to a count?

I.e.,

.Function = xlCount

 

[countingapples]

That is what I originally had. However it returns the number of times a category is listed, so I end up with duplicates. It's mirroring the personnel counts.

To better illustrate my conundrum please look at the pivot I posted. For the first unit there are 9 people listed. The incident count also was showing 9, but when you look at the detail behind these numbers you see that it was a single incident with 9 personnel involved, hence my search for a more accurate way of reporting the incident counts.