| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating the number of unique values with a 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.misc
|
|||
|
|||
|
Calculating the number of unique values with a range
Chip Pearson has got 'an answer' (http://www.cpearson.com/excel/Duplicates.aspx) for you -- Pecoflyer ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24224 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating the number of unique values with a range
try this
=SUM(1/COUNTIF(B2:B65636,B2:B65636)) ( array function use Ctrl + Shift + Enter ) On Nov 2, 7:31*pm, 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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating the number of unique values with a range
Formula which will not take blanks into account -- Pecoflyer ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24224 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating the number of unique values with a range
See your other post
-- Biff Microsoft Excel MVP "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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculating the number of unique values with a range
Have you ever tried that formula on that big of a range, and if so, how long
did it take to calculate (if it ever did!) ? -- Biff Microsoft Excel MVP "muddan madhu" wrote in message ... try this =SUM(1/COUNTIF(B2:B65636,B2:B65636)) ( array function use Ctrl + Shift + Enter ) On Nov 2, 7:31 pm, 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting the number of unique values within a range | Excel Discussion (Misc queries) | |||
| count unique values in a filtered range | Excel Worksheet Functions | |||
| Counting Unique Values by Date Range | Excel Worksheet Functions | |||
| Unique values from date range | Excel Discussion (Misc queries) | |||
| How do I get the unique values from a range? | Excel Worksheet Functions | |||
Linear Mode
