A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Counting Unique Values



 
 
Thread Tools Display Modes
  #21  
Old October 29th, 2006, 08:52 PM posted to microsoft.public.excel.worksheet.functions
Epinn
external usenet poster
 
Posts: 754
Default Counting Unique Values

Roger,

Ah, that's why you are the expert. (I laughed at my formula after I have seen yours.) I am glad I posted and you responded. Lately, I have been learning about CHAR and I am attracted to it while I have forgotten the simple things in life. g I thought I needed REPT to catch all. I read an example of *adding* trailing spaces and REPT is used. Is it true that we need REPT when adding trailing spaces?

Thank you for helping me all the time. By the way, have you read my post about using PivotTable to check for duplicates?

Epinn

"Roger Govier" wrote in message ...
Absolutely no difference to the much simpler
=SUBSTITUTE(A1," ","")

--
Regards

Roger Govier


"Epinn" wrote in message
...
This is how I would remove trailing spaces.

=SUBSTITUTE(A1,CHAR(32),REPT("",255))

Please correct me if I am wrong. Thanks.

Epinn

"Epinn" wrote in message
...
The formulae discussed in this thread should serve Bob's needs.

Experts, please confirm that the formulae are only good as long as there
are no trailing spaces.

e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice.

If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right?

Epinn

"bob" wrote in message
...
I have a column with the following dates:

11/23/05
11/23/05
11/23/05
11/25/05
11/25/05
11/28/05
12/1/05
12/1/05
12/1/05

I want a formula that will tell me how many unique dates there are in
the
column. Can anyone help?

thanks,
Bob




  #22  
Old October 29th, 2006, 09:09 PM posted to microsoft.public.excel.worksheet.functions
Epinn
external usenet poster
 
Posts: 754
Default Counting Unique Values

RD,

Thank you for asking. Sometimes I thought people could read my mind. g

What I was trying to say is this:-

Rob's formula =SUM(1/COUNTIF(A1:A9,A1:A9)) works fine when there are no blanks in the array. However, since I am a fan of SUMPRODUCT, I'll use

=SUMPRODUCT(1/COUNTIF(A1:A9,A1:A9)) instead of

=SUM(1/COUNTIF(A1:A9,A1:A9)) + CSE

even if there are no blanks in the array.

The Sumproduct() formula Biff posted *will work* with blanks!


Totally agree. I am very much aware of what each formula in this thread does as I have spent hours experimenting. Biff's formula is what I live by when it comes to counting unique values. It just doesn't take care of blanks but much more. As JMB highlighted, unlike the FREQUENCY formula which takes care of blanks and numbers, Biff's formula will take care of *text*, numbers and blanks. Sounds like a one-fits-all formula. I should memorize it by heart. g

Now my turn to ask you. What do you mean by "(emphasis mine)?"

By the way, is Ragdyer your last name or first and last name combined i.e. Rag = first name and Dyer = last name? I won't ask you about your gender. g

Epinn

"Ragdyer" wrote in message ...
Don't understand this comment Epinn:
(emphasis mine)
"so I'll use SUMPRODUCT instead of SUM and CSE *IF THERE ARE NO
BLANKS*."

The Sumproduct() formula Biff posted *will work* with blanks!

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Epinn" wrote in message
...
Try test your formula with a range that contains a blank and you'll know why
the experts and I won't recommend it. Also, I am a fan of SUMPRODUCT, so
I'll use SUMPRODUCT instead of SUM and CSE if there are no blanks.

Thanks for dropping by and sharing.

Epinn

"Rob" wrote in message
...
Epinn,

The following formula entered as an array i.e. Ctrl+Shift+Enter will
correctly report 4 unique dates.

=SUM(1/COUNTIF(A1:A9,A1:A9))

HTH Rob

"Epinn" wrote in message
...
The formulae discussed in this thread should serve Bob's needs.

Experts, please confirm that the formulae are only good as long as there are
no trailing spaces.

e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice.

If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right?

Epinn

"bob" wrote in message
...
I have a column with the following dates:

11/23/05
11/23/05
11/23/05
11/25/05
11/25/05
11/28/05
12/1/05
12/1/05
12/1/05

I want a formula that will tell me how many unique dates there are in the
column. Can anyone help?

thanks,
Bob




  #23  
Old October 29th, 2006, 10:28 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
external usenet poster
 
Posts: 5,994
Default Counting Unique Values



"Epinn" wrote in message
...
RD,


By the way, is Ragdyer your last name or first and last name combined i.e.
Rag = first name and Dyer = last name? I won't ask you about your gender.
g

http://tinyurl.com/y2kzhm


  #24  
Old October 29th, 2006, 10:57 PM posted to microsoft.public.excel.worksheet.functions
RagDyeR
external usenet poster
 
Posts: 3,482
Default Counting Unique Values

When quoting a statement, the perception is that the quote is the *exact*
depiction of the original.
However, to accentuate and bring attention to a portion of the quote, the
quote may be *modified*, and is therefore no longer an *exact* depiction of
the original (you didn't capitalize and wrap those words in asterisks).
It is proper decorum to bring attention to the modifications made to the
quote by the person using the quote.
"emphasis mine" is describing that *I* changed the quote by emphasizing a
portion of it.

Ragdyer is my "handle", going back to the old days of CB radio.
I carried it forward to the "new" internet.
It just describes my profession of being in the textile dyeing trade.
By coincidence, RD does not stand for RagDyer, but my actual name of Rick
Dormack.

I just continued to use my handle exclusively, when I was told by some here
that I *shouldn't*!bg
You can surmise by my name that I'm male.

Now, why all the secrecy about your identity?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------



"Epinn" wrote in message
...
RD,

Thank you for asking. Sometimes I thought people could read my mind. g

What I was trying to say is this:-

Rob's formula =SUM(1/COUNTIF(A1:A9,A1:A9)) works fine when there are no
blanks in the array. However, since I am a fan of SUMPRODUCT, I'll use

=SUMPRODUCT(1/COUNTIF(A1:A9,A1:A9)) instead of

=SUM(1/COUNTIF(A1:A9,A1:A9)) + CSE

even if there are no blanks in the array.

The Sumproduct() formula Biff posted *will work* with blanks!


Totally agree. I am very much aware of what each formula in this thread
does as I have spent hours experimenting. Biff's formula is what I live by
when it comes to counting unique values. It just doesn't take care of
blanks but much more. As JMB highlighted, unlike the FREQUENCY formula
which takes care of blanks and numbers, Biff's formula will take care of
*text*, numbers and blanks. Sounds like a one-fits-all formula. I should
memorize it by heart. g

Now my turn to ask you. What do you mean by "(emphasis mine)?"

By the way, is Ragdyer your last name or first and last name combined i.e.
Rag = first name and Dyer = last name? I won't ask you about your gender.
g

Epinn

"Ragdyer" wrote in message
...
Don't understand this comment Epinn:
(emphasis mine)
"so I'll use SUMPRODUCT instead of SUM and CSE *IF THERE ARE NO
BLANKS*."

The Sumproduct() formula Biff posted *will work* with blanks!

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Epinn" wrote in message
...
Try test your formula with a range that contains a blank and you'll know why
the experts and I won't recommend it. Also, I am a fan of SUMPRODUCT, so
I'll use SUMPRODUCT instead of SUM and CSE if there are no blanks.

Thanks for dropping by and sharing.

Epinn

"Rob" wrote in message
...
Epinn,

The following formula entered as an array i.e. Ctrl+Shift+Enter will
correctly report 4 unique dates.

=SUM(1/COUNTIF(A1:A9,A1:A9))

HTH Rob

"Epinn" wrote in message
...
The formulae discussed in this thread should serve Bob's needs.

Experts, please confirm that the formulae are only good as long as there are
no trailing spaces.

e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice.

If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right?

Epinn

"bob" wrote in message
...
I have a column with the following dates:

11/23/05
11/23/05
11/23/05
11/25/05
11/25/05
11/28/05
12/1/05
12/1/05
12/1/05

I want a formula that will tell me how many unique dates there are in the
column. Can anyone help?

thanks,
Bob




  #25  
Old October 29th, 2006, 10:59 PM posted to microsoft.public.excel.worksheet.functions
RagDyeR
external usenet poster
 
Posts: 3,482
Default Counting Unique Values

You've got a good memory Mr. Phillips!bg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Bob Phillips" wrote in message
...


"Epinn" wrote in message
...
RD,


By the way, is Ragdyer your last name or first and last name combined i.e.
Rag = first name and Dyer = last name? I won't ask you about your gender.
g

http://tinyurl.com/y2kzhm



  #26  
Old October 30th, 2006, 01:53 AM posted to microsoft.public.excel.worksheet.functions
Epinn
external usenet poster
 
Posts: 754
Default Counting Unique Values

Ragdyer is my "handle"......

You sound hitech by using the word "handle." When I see the word "handle," I think TINYURL ...... g

So, you have a story to tell about your user ID as well. Dyer is a very legitimate surname.

If I don't hear a "no," I'll call you Rick from now on. Actually, RD has less keystrokes. Well, let my mood decide.

Epinn

"Ragdyer" wrote in message ...
When quoting a statement, the perception is that the quote is the *exact*
depiction of the original.
However, to accentuate and bring attention to a portion of the quote, the
quote may be *modified*, and is therefore no longer an *exact* depiction of
the original (you didn't capitalize and wrap those words in asterisks).
It is proper decorum to bring attention to the modifications made to the
quote by the person using the quote.
"emphasis mine" is describing that *I* changed the quote by emphasizing a
portion of it.

Ragdyer is my "handle", going back to the old days of CB radio.
I carried it forward to the "new" internet.
It just describes my profession of being in the textile dyeing trade.
By coincidence, RD does not stand for RagDyer, but my actual name of Rick
Dormack.

I just continued to use my handle exclusively, when I was told by some here
that I *shouldn't*!bg
You can surmise by my name that I'm male.

Now, why all the secrecy about your identity?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------



"Epinn" wrote in message
...
RD,

Thank you for asking. Sometimes I thought people could read my mind. g

What I was trying to say is this:-

Rob's formula =SUM(1/COUNTIF(A1:A9,A1:A9)) works fine when there are no
blanks in the array. However, since I am a fan of SUMPRODUCT, I'll use

=SUMPRODUCT(1/COUNTIF(A1:A9,A1:A9)) instead of

=SUM(1/COUNTIF(A1:A9,A1:A9)) + CSE

even if there are no blanks in the array.

The Sumproduct() formula Biff posted *will work* with blanks!


Totally agree. I am very much aware of what each formula in this thread
does as I have spent hours experimenting. Biff's formula is what I live by
when it comes to counting unique values. It just doesn't take care of
blanks but much more. As JMB highlighted, unlike the FREQUENCY formula
which takes care of blanks and numbers, Biff's formula will take care of
*text*, numbers and blanks. Sounds like a one-fits-all formula. I should
memorize it by heart. g

Now my turn to ask you. What do you mean by "(emphasis mine)?"

By the way, is Ragdyer your last name or first and last name combined i.e.
Rag = first name and Dyer = last name? I won't ask you about your gender.
g

Epinn

"Ragdyer" wrote in message
...
Don't understand this comment Epinn:
(emphasis mine)
"so I'll use SUMPRODUCT instead of SUM and CSE *IF THERE ARE NO
BLANKS*."

The Sumproduct() formula Biff posted *will work* with blanks!

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Epinn" wrote in message
...
Try test your formula with a range that contains a blank and you'll know why
the experts and I won't recommend it. Also, I am a fan of SUMPRODUCT, so
I'll use SUMPRODUCT instead of SUM and CSE if there are no blanks.

Thanks for dropping by and sharing.

Epinn

"Rob" wrote in message
...
Epinn,

The following formula entered as an array i.e. Ctrl+Shift+Enter will
correctly report 4 unique dates.

=SUM(1/COUNTIF(A1:A9,A1:A9))

HTH Rob

"Epinn" wrote in message
...
The formulae discussed in this thread should serve Bob's needs.

Experts, please confirm that the formulae are only good as long as there are
no trailing spaces.

e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice.

If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right?

Epinn

"bob" wrote in message
...
I have a column with the following dates:

11/23/05
11/23/05
11/23/05
11/25/05
11/25/05
11/28/05
12/1/05
12/1/05
12/1/05

I want a formula that will tell me how many unique dates there are in the
column. Can anyone help?

thanks,
Bob





  #27  
Old October 30th, 2006, 04:42 AM posted to microsoft.public.excel.worksheet.functions
RagDyeR
external usenet poster
 
Posts: 3,482
Default Counting Unique Values

You very nonchalantly bypassed *my* question though!g
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Epinn" wrote in message
...
Ragdyer is my "handle"......


You sound hitech by using the word "handle." When I see the word "handle,"
I think TINYURL ...... g

So, you have a story to tell about your user ID as well. Dyer is a very
legitimate surname.

If I don't hear a "no," I'll call you Rick from now on. Actually, RD has
less keystrokes. Well, let my mood decide.

Epinn

"Ragdyer" wrote in message
...
When quoting a statement, the perception is that the quote is the *exact*
depiction of the original.
However, to accentuate and bring attention to a portion of the quote, the
quote may be *modified*, and is therefore no longer an *exact* depiction of
the original (you didn't capitalize and wrap those words in asterisks).
It is proper decorum to bring attention to the modifications made to the
quote by the person using the quote.
"emphasis mine" is describing that *I* changed the quote by emphasizing a
portion of it.

Ragdyer is my "handle", going back to the old days of CB radio.
I carried it forward to the "new" internet.
It just describes my profession of being in the textile dyeing trade.
By coincidence, RD does not stand for RagDyer, but my actual name of Rick
Dormack.

I just continued to use my handle exclusively, when I was told by some here
that I *shouldn't*!bg
You can surmise by my name that I'm male.

Now, why all the secrecy about your identity?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------



"Epinn" wrote in message
...
RD,

Thank you for asking. Sometimes I thought people could read my mind. g

What I was trying to say is this:-

Rob's formula =SUM(1/COUNTIF(A1:A9,A1:A9)) works fine when there are no
blanks in the array. However, since I am a fan of SUMPRODUCT, I'll use

=SUMPRODUCT(1/COUNTIF(A1:A9,A1:A9)) instead of

=SUM(1/COUNTIF(A1:A9,A1:A9)) + CSE

even if there are no blanks in the array.

The Sumproduct() formula Biff posted *will work* with blanks!


Totally agree. I am very much aware of what each formula in this thread
does as I have spent hours experimenting. Biff's formula is what I live by
when it comes to counting unique values. It just doesn't take care of
blanks but much more. As JMB highlighted, unlike the FREQUENCY formula
which takes care of blanks and numbers, Biff's formula will take care of
*text*, numbers and blanks. Sounds like a one-fits-all formula. I should
memorize it by heart. g

Now my turn to ask you. What do you mean by "(emphasis mine)?"

By the way, is Ragdyer your last name or first and last name combined i.e.
Rag = first name and Dyer = last name? I won't ask you about your gender.
g

Epinn

"Ragdyer" wrote in message
...
Don't understand this comment Epinn:
(emphasis mine)
"so I'll use SUMPRODUCT instead of SUM and CSE *IF THERE ARE NO
BLANKS*."

The Sumproduct() formula Biff posted *will work* with blanks!

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Epinn" wrote in message
...
Try test your formula with a range that contains a blank and you'll know why
the experts and I won't recommend it. Also, I am a fan of SUMPRODUCT, so
I'll use SUMPRODUCT instead of SUM and CSE if there are no blanks.

Thanks for dropping by and sharing.

Epinn

"Rob" wrote in message
...
Epinn,

The following formula entered as an array i.e. Ctrl+Shift+Enter will
correctly report 4 unique dates.

=SUM(1/COUNTIF(A1:A9,A1:A9))

HTH Rob

"Epinn" wrote in message
...
The formulae discussed in this thread should serve Bob's needs.

Experts, please confirm that the formulae are only good as long as there are
no trailing spaces.

e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice.

If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right?

Epinn

"bob" wrote in message
...
I have a column with the following dates:

11/23/05
11/23/05
11/23/05
11/25/05
11/25/05
11/28/05
12/1/05
12/1/05
12/1/05

I want a formula that will tell me how many unique dates there are in the
column. Can anyone help?

thanks,
Bob





  #28  
Old October 30th, 2006, 06:20 AM posted to microsoft.public.excel.worksheet.functions
Epinn
external usenet poster
 
Posts: 754
Default Counting Unique Values

I think I'll use TRIM( ) if you have no objection.

Epinn

"Roger Govier" wrote in message ...
Absolutely no difference to the much simpler
=SUBSTITUTE(A1," ","")

--
Regards

Roger Govier


"Epinn" wrote in message
...
This is how I would remove trailing spaces.

=SUBSTITUTE(A1,CHAR(32),REPT("",255))

Please correct me if I am wrong. Thanks.

Epinn

"Epinn" wrote in message
...
The formulae discussed in this thread should serve Bob's needs.

Experts, please confirm that the formulae are only good as long as there
are no trailing spaces.

e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice.

If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right?

Epinn

"bob" wrote in message
...
I have a column with the following dates:

11/23/05
11/23/05
11/23/05
11/25/05
11/25/05
11/28/05
12/1/05
12/1/05
12/1/05

I want a formula that will tell me how many unique dates there are in
the
column. Can anyone help?

thanks,
Bob




  #29  
Old October 30th, 2006, 11:53 AM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
external usenet poster
 
Posts: 5,994
Default Counting Unique Values

That is because I too was fascinated by your handle, so I looked it up, and
I remember that it was in response to Stephen :-).


Bob

"Ragdyer" wrote in message
...
You've got a good memory Mr. Phillips!bg
--
Regards,

RD

--------------------------------------------------------------------------

-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------

-
"Bob Phillips" wrote in message
...


"Epinn" wrote in message
...
RD,


By the way, is Ragdyer your last name or first and last name combined

i.e.
Rag = first name and Dyer = last name? I won't ask you about your

gender.
g

http://tinyurl.com/y2kzhm





  #30  
Old October 30th, 2006, 11:56 AM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
external usenet poster
 
Posts: 5,994
Default Counting Unique Values


"Ragdyer" wrote in message
...

I just continued to use my handle exclusively, when I was told by some

here
that I *shouldn't*!bg



Whoever suggested that? I remember when 'onedaywhen' was outed as Jamie
Collins, I always thought it was a shame that he dropped the handle, it gave
a certain panache to his posts. I hope you never drop RagDyer, it adds
diversity to the group.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:30 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.