Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
number of types
hi,
there are a column made up from , say, a, b, c, a, c, d ,a,c,e how to know the number of types, thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
number of types
Hi Excel News,
Try the formula: =SUM(IF(FREQUENCY(IF(LEN(A1:A50)0,MATCH(A1:A50,A1 :A50,0),""), IF(LEN(A1:A50)0,MATCH(A1:A50,A1:A50,0),""))0,1)) (Amend the range A1:A50 to suit). For this and alternative formulae, see the section entitled: Counting Distinct Entries In A Range on Chip Pearson's Duplicates page at: http://www.cpearson.com/excel/Duplicates.aspx --- Regards. Norman "EXCEL$B!!(BNEWS" wrote in message ... hi, there are a column made up from , say, a, b, c, a, c, d ,a,c,e how to know the number of types, thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
number of types
=SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&""))
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "EXCEL$B!!(BNEWS" wrote in message ... hi, there are a column made up from , say, a, b, c, a, c, d ,a,c,e how to know the number of types, thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
number of types
Hello,
I suggest to use Charles Williams' UDF countu: http://msdn.microsoft.com/en-us/library/aa730921.aspx Regards, Bernd |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
number of types
You could make it about 6-7 times faster still by using cSortedDictionary,
which you can download from he http://www.thecommon.net/9.html Option Explicit Private lStartTime As Long Private Declare Function timeGetTime Lib "winmm.dll" () As Long Public Function COUNTU(theRange As Range) As Variant Dim r As Long Dim c As Long Dim colUniques As Collection Dim cSD As cSortedDictionary Dim vArr As Variant Dim vCell As Variant Dim vLcell As Variant Dim oRng As Range Dim bCSD As Boolean Set colUniques = New Collection Set cSD = New cSortedDictionary Set oRng = Intersect(theRange, theRange.Parent.UsedRange) vArr = oRng bCSD = True On Error Resume Next If bCSD Then For Each vCell In vArr If vCell < vLcell Then If Len(vCell) 0 Then cSD.Add vCell End If End If vLcell = vCell Next vCell Else For Each vCell In vArr If vCell < vLcell Then If Len(vCell) 0 Then colUniques.Add vCell, CStr(vCell) End If End If vLcell = vCell Next vCell End If If bCSD Then COUNTU = cSD.Count Else COUNTU = colUniques.Count End If End Function Sub test() Dim lUnique As Long StartSW lUnique = COUNTU(Range(Cells(1), Cells(65536, 1))) StopSW MsgBox lUnique End Sub Sub StartSW() lStartTime = timeGetTime() End Sub Function StopSW(Optional bMsgBox As Boolean = True, _ Optional vMessage As Variant, _ Optional lMinimumTimeToShow As Long = -1) As Variant Dim lTime As Long lTime = timeGetTime() - lStartTime If lTime lMinimumTimeToShow Then If IsMissing(vMessage) Then StopSW = lTime Else StopSW = lTime & " - " & vMessage End If End If If bMsgBox Then If lTime lMinimumTimeToShow Then MsgBox "Done in " & lTime & " msecs", , vMessage End If End If End Function Also, you may want to leave this out: If vCell < vLcell Then If Len(vCell) 0 Then Haven't tested with the regular dictionary object. RBS "Bernd P" wrote in message ... Hello, I suggest to use Charles Williams' UDF countu: http://msdn.microsoft.com/en-us/library/aa730921.aspx Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I hide unused file types from file types list in save dial | Excel Discussion (Misc queries) | |||
Excel 2007 error "some chart types cannot be combined with other chart types. Select a different chart types" | Charts and Charting in Excel | |||
How to determine the number of types? | Excel Discussion (Misc queries) | |||
Counting the number of Error Types | Excel Worksheet Functions | |||
Types in Add-In | Excel Programming |