Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating the number of unique values within a large range.
I would be most grateful if someone could offer me some help.
I need to count the number of unique values within a column range. The range is between B2:B65536. Each value consists of a series of alpha and numerical characters and there are blank cells within the range. There will be around 30,000 plus entries. I have used various formulas, but due to the vast number of rows of data they crash. An example of one of the formulas I used is below: =SUMPRODUCT((1/COUNTIF(A1:A5,A1:A5&""))) I am desperate for an answer but am confused as to how to do this? Any help or assistance would be very much greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating the number of unique values within a large range.
Hi,
From a copy of the worksheet, sort it on the column you are trying to find the unique values in, Column B. Then use Data/Filter/Advanced Filter/Unique Records Only - This will give you the unique records. David "SiH23" wrote: I would be most grateful if someone could offer me some help. I need to count the number of unique values within a column range. The range is between B2:B65536. Each value consists of a series of alpha and numerical characters and there are blank cells within the range. There will be around 30,000 plus entries. I have used various formulas, but due to the vast number of rows of data they crash. An example of one of the formulas I used is below: =SUMPRODUCT((1/COUNTIF(A1:A5,A1:A5&""))) I am desperate for an answer but am confused as to how to do this? Any help or assistance would be very much greatly appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating the number of unique values within a large range.
This VBA code will run quite fast:
Function CountUnique(rng As Range) As Long Dim i As Long Dim arr Dim coll As Collection arr = rng Set coll = New Collection On Error Resume Next For i = 1 To UBound(arr) coll.Add arr(i, 1), CStr(arr(i, 1)) Next i CountUnique = coll.Count End Function Sub test() MsgBox CountUnique(Selection) End Sub So, in your case select the range B2:B65536 and run the Sub test. If still not fast enough than use Olaf Schmidt's dhSortedDictionary, which can be downloaded from he http://www.thecommon.net/9.html RBS "SiH23" wrote in message ... I would be most grateful if someone could offer me some help. I need to count the number of unique values within a column range. The range is between B2:B65536. Each value consists of a series of alpha and numerical characters and there are blank cells within the range. There will be around 30,000 plus entries. I have used various formulas, but due to the vast number of rows of data they crash. An example of one of the formulas I used is below: =SUMPRODUCT((1/COUNTIF(A1:A5,A1:A5&""))) I am desperate for an answer but am confused as to how to do this? Any help or assistance would be very much greatly appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating the number of unique values within a large range.
Hi,
I suspect you've created a monster with a column of 30k data entries. Try this if you want to count unique ones. =SUMPRODUCT((B2:B1000<"")/(COUNTIF(B2:B1000,B2:B1000&""))) It will take a long time to calculate if you use 65535 cells as in your example so I suggest you switch calculation to manual to prevent frequent recalculation. Mike "SiH23" wrote: I would be most grateful if someone could offer me some help. I need to count the number of unique values within a column range. The range is between B2:B65536. Each value consists of a series of alpha and numerical characters and there are blank cells within the range. There will be around 30,000 plus entries. I have used various formulas, but due to the vast number of rows of data they crash. An example of one of the formulas I used is below: =SUMPRODUCT((1/COUNTIF(A1:A5,A1:A5&""))) I am desperate for an answer but am confused as to how to do this? Any help or assistance would be very much greatly appreciated. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating the number of unique values within a large range.
Hello,
I suggest to avoid formulas with sumproduct/countif for this task. See http://www.sulprobil.com/html/excel_don_ts.html Look for Charles Williams' function COUNTU in his article. Regards, Bernd |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating the number of unique values within a large range.
Just a correction to do with the posted link.
dhSortedDictionary should not be used (it can cause an error) and instead one should use the file dhRichClient, which can be downloaded from: www.datenhaus.de/Downloads/dhRichClientDemo.zip This has an updated version of cSortedDictionary and beside that a lot more, eg a very good VB(A) wrapper for SQLite. RBS "RB Smissaert" wrote in message ... This VBA code will run quite fast: Function CountUnique(rng As Range) As Long Dim i As Long Dim arr Dim coll As Collection arr = rng Set coll = New Collection On Error Resume Next For i = 1 To UBound(arr) coll.Add arr(i, 1), CStr(arr(i, 1)) Next i CountUnique = coll.Count End Function Sub test() MsgBox CountUnique(Selection) End Sub So, in your case select the range B2:B65536 and run the Sub test. If still not fast enough than use Olaf Schmidt's dhSortedDictionary, which can be downloaded from he http://www.thecommon.net/9.html RBS "SiH23" wrote in message ... I would be most grateful if someone could offer me some help. I need to count the number of unique values within a column range. The range is between B2:B65536. Each value consists of a series of alpha and numerical characters and there are blank cells within the range. There will be around 30,000 plus entries. I have used various formulas, but due to the vast number of rows of data they crash. An example of one of the formulas I used is below: =SUMPRODUCT((1/COUNTIF(A1:A5,A1:A5&""))) I am desperate for an answer but am confused as to how to do this? Any help or assistance would be very much greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculate values in an unlimited range of cells in a column WITHOUTalso calculating values that had previously been filtered AGAINST? | Excel Discussion (Misc queries) | |||
Calculating the number of unique values within a large range | Excel Worksheet Functions | |||
Calculating the number of unique values with a range | Excel Discussion (Misc queries) | |||
Counting the number of unique values within a range | Excel Discussion (Misc queries) | |||
Filter a pivot table with large number of unique items in the filterlist | Excel Programming |