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

 

[NateO]

Sure,

sum([Full Name])

Is a real problem. You can't sum names, you can sum numeric data types. Why would you want a detailed list of names in a summary of this nature... Pull that field out of there eh.

 

[NateO]

I assume you want a count of the names versus the sum. Try the following:

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> PtReplace2()
<SPAN style="color:darkblue">Dim</SPAN> cn <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>, rs <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>
<SPAN style="color:darkblue">Dim</SPAN> clcMde <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Long</SPAN>

clcMde = Application.Calculation
Application.ScreenUpdating = <SPAN style="color:darkblue">False</SPAN>
Application.Calculation = xlCalculationManual

Sheets("Unit Response Summary").[a2:iv65536].ClearContents

<SPAN style="color:darkblue">Set</SPAN> cn = CreateObject("ADODB.Connection")

cn.<SPAN style="color:darkblue">Open</SPAN> "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
    ThisWorkbook.FullName & ";Extended Properties=Excel 8.0;"  <SPAN style="color:green">'Create DB connection</SPAN>
    
<SPAN style="color:darkblue">Set</SPAN> rs = CreateObject("ADODB.Recordset")
<SPAN style="color:darkblue">With</SPAN> rs
    <SPAN style="color:darkblue">Set</SPAN> .ActiveConnection = cn
    .Source = "Select t1.Blah, Count(*) <SPAN style="color:darkblue">As</SPAN> Cnt " & _
        "From (<SPAN style="color:darkblue">Select</SPAN> Distinct [Incident Key], [Incident <SPAN style="color:darkblue">Type</SPAN> Class] <SPAN style="color:darkblue">As</SPAN> Blah " & _
        "From [PersonnelResponseData$a1:af65536]) AS T1 Group By T1.Blah"
    .<SPAN style="color:darkblue">Open</SPAN> , , 3, 3
    Sheets("Unit Response Summary").[a2].CopyFromRecordset rs
    .<SPAN style="color:darkblue">Close</SPAN>
    
    .Source = "Select Sum([Hours on Call]), Count([full name]) " & _
        "From [PersonnelResponseData$a1:af65536] Group By [Incident <SPAN style="color:darkblue">Type</SPAN> Class]"
    .<SPAN style="color:darkblue">Open</SPAN> , , 3, 3
    Sheets("Unit Response Summary").[c2].CopyFromRecordset rs
    .<SPAN style="color:darkblue">Close</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>
cn.<SPAN style="color:darkblue">Close</SPAN>
<SPAN style="color:darkblue">Set</SPAN> rs = Nothing: <SPAN style="color:darkblue">Set</SPAN> cn = <SPAN style="color:darkblue">Nothing</SPAN>

Application.Calculation = clcMde
Application.ScreenUpdating = <SPAN style="color:darkblue">True</SPAN>

<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

Hope this helps.

 

[countingapples]

Beautiful! ray:

Can this be modified to put the Unit ID in column A and do the breakdown from there?

 

[NateO]

You mean group by unit id instead of Incident Type Class?

Sure, I don't see why not. But one would have to completely rethink the query/sub query logic, as with the 2nd query, and add a third, as there are a different number of associated records. The logic currently cannot line up, Incident type class has 8 categories, while there are 69 Unique IDs. How can you have a table of the two together?

You'll need to figure out how you want to group this before you write code to do so eh.

 

[countingapples]

It's supposed to be a cross tab summary set out as follows:

Group: Unit Id
Sub Group: Incident Type

Then provide the following data:
Count of Incidents for each Incident Type per Unit ID
Average Number of Personnel for same
Total Time on Incidents

This is why I'm stumped on a dynamic approach. Static is easy peasy, dynamic is beyond by current abilities.