Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
ChelleA
 
Posts: n/a
Default How do I sort a column a unique number?

I have a listing of proceedures for each patient. But I want to summarize
the number of patients with out having to manually have to go in and count.
Is there a formula, or function that can do this?
Example:
column A column B column C
Patient number Proceedure number proceedure date
4455 152 1/1/05
4455 188 1/1/05
4455 145 1/1/05

When you sum the column A it gives you a value of one for each occurance,
but I'd like to tell me the number of patients (1) at visited not number of
proceedures (3).

  #2   Report Post  
Dan Knight
 
Posts: n/a
Default

ChelleA;
Doing a search in Excel Help for "How do I count unique values in a list"
returns the following advice:

"Sometimes you want to find out how many unique values there are in a range
that contains duplicate values....." use the following formula:

=SUM(IF(FREQUENCY(A2:A10,A2:A10)0,1))

Where A2:A10 = equals the range of cells containing the data.





"ChelleA" wrote:

I have a listing of proceedures for each patient. But I want to summarize
the number of patients with out having to manually have to go in and count.
Is there a formula, or function that can do this?
Example:
column A column B column C
Patient number Proceedure number proceedure date
4455 152 1/1/05
4455 188 1/1/05
4455 145 1/1/05

When you sum the column A it gives you a value of one for each occurance,
but I'd like to tell me the number of patients (1) at visited not number of
proceedures (3).

  #3   Report Post  
Myrna Larson
 
Posts: n/a
Default

Assuming the data occupies A2:A100

=SUM(1/COUNTIF(A2:A100,A2:A100))

entered as an array formula with CTRL+SHIFT+ENTER.



On Thu, 17 Feb 2005 14:45:03 -0800, "ChelleA"
wrote:

I have a listing of proceedures for each patient. But I want to summarize
the number of patients with out having to manually have to go in and count.
Is there a formula, or function that can do this?
Example:
column A column B column C
Patient number Proceedure number proceedure date
4455 152 1/1/05
4455 188 1/1/05
4455 145 1/1/05

When you sum the column A it gives you a value of one for each occurance,
but I'd like to tell me the number of patients (1) at visited not number of
proceedures (3).


  #4   Report Post  
Myrna Larson
 
Posts: n/a
Default

Correct me if I'm wrong, but doesn't FREQUENCY work only with numeric data?
That's what she shows in the sample date, but I'm not sure that is always the
case.

On Thu, 17 Feb 2005 15:01:01 -0800, "Dan Knight"
wrote:

ChelleA;
Doing a search in Excel Help for "How do I count unique values in a list"
returns the following advice:

"Sometimes you want to find out how many unique values there are in a range
that contains duplicate values....." use the following formula:

=SUM(IF(FREQUENCY(A2:A10,A2:A10)0,1))

Where A2:A10 = equals the range of cells containing the data.





"ChelleA" wrote:

I have a listing of proceedures for each patient. But I want to summarize
the number of patients with out having to manually have to go in and count.
Is there a formula, or function that can do this?
Example:
column A column B column C
Patient number Proceedure number proceedure date
4455 152 1/1/05
4455 188 1/1/05
4455 145 1/1/05

When you sum the column A it gives you a value of one for each occurance,
but I'd like to tell me the number of patients (1) at visited not number of
proceedures (3).


  #5   Report Post  
Gord Dibben
 
Posts: n/a
Default

No correction necessary Myrna.


Gord

On Thu, 17 Feb 2005 17:27:00 -0600, Myrna Larson
wrote:

Correct me if I'm wrong, but doesn't FREQUENCY work only with numeric data?
That's what she shows in the sample date, but I'm not sure that is always the
case.

On Thu, 17 Feb 2005 15:01:01 -0800, "Dan Knight"
wrote:

ChelleA;
Doing a search in Excel Help for "How do I count unique values in a list"
returns the following advice:

"Sometimes you want to find out how many unique values there are in a range
that contains duplicate values....." use the following formula:

=SUM(IF(FREQUENCY(A2:A10,A2:A10)0,1))

Where A2:A10 = equals the range of cells containing the data.





"ChelleA" wrote:

I have a listing of proceedures for each patient. But I want to summarize
the number of patients with out having to manually have to go in and count.
Is there a formula, or function that can do this?
Example:
column A column B column C
Patient number Proceedure number proceedure date
4455 152 1/1/05
4455 188 1/1/05
4455 145 1/1/05

When you sum the column A it gives you a value of one for each occurance,
but I'd like to tell me the number of patients (1) at visited not number of
proceedures (3).




  #6   Report Post  
ChelleA
 
Posts: n/a
Default

This formula didn't work when my patient number was considered text...once I
converted the text to numbers it did. Thanks!

"Dan Knight" wrote:

ChelleA;
Doing a search in Excel Help for "How do I count unique values in a list"
returns the following advice:

"Sometimes you want to find out how many unique values there are in a range
that contains duplicate values....." use the following formula:

=SUM(IF(FREQUENCY(A2:A10,A2:A10)0,1))

Where A2:A10 = equals the range of cells containing the data.





"ChelleA" wrote:

I have a listing of proceedures for each patient. But I want to summarize
the number of patients with out having to manually have to go in and count.
Is there a formula, or function that can do this?
Example:
column A column B column C
Patient number Proceedure number proceedure date
4455 152 1/1/05
4455 188 1/1/05
4455 145 1/1/05

When you sum the column A it gives you a value of one for each occurance,
but I'd like to tell me the number of patients (1) at visited not number of
proceedures (3).

  #7   Report Post  
ChelleA
 
Posts: n/a
Default

Myrna, you were also correct thanks again.

"Myrna Larson" wrote:

Assuming the data occupies A2:A100

=SUM(1/COUNTIF(A2:A100,A2:A100))

entered as an array formula with CTRL+SHIFT+ENTER.



On Thu, 17 Feb 2005 14:45:03 -0800, "ChelleA"
wrote:

I have a listing of proceedures for each patient. But I want to summarize
the number of patients with out having to manually have to go in and count.
Is there a formula, or function that can do this?
Example:
column A column B column C
Patient number Proceedure number proceedure date
4455 152 1/1/05
4455 188 1/1/05
4455 145 1/1/05

When you sum the column A it gives you a value of one for each occurance,
but I'd like to tell me the number of patients (1) at visited not number of
proceedures (3).



  #8   Report Post  
RagDyeR
 
Posts: n/a
Default

If there is a chance that the datalist might contain a blank cell, or that
you might have to delete an entry at a later time, you can try this, which
works with blanks, as well as text or numbers:

=SUMPRODUCT((A1:A30<"")/COUNTIF(A1:A30,A1:A30&""))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"ChelleA" wrote in message
...
Myrna, you were also correct thanks again.

"Myrna Larson" wrote:

Assuming the data occupies A2:A100

=SUM(1/COUNTIF(A2:A100,A2:A100))

entered as an array formula with CTRL+SHIFT+ENTER.



On Thu, 17 Feb 2005 14:45:03 -0800, "ChelleA"
wrote:

I have a listing of proceedures for each patient. But I want to

summarize
the number of patients with out having to manually have to go in and

count.
Is there a formula, or function that can do this?
Example:
column A column B column C
Patient number Proceedure number proceedure date
4455 152 1/1/05
4455 188 1/1/05
4455 145 1/1/05

When you sum the column A it gives you a value of one for each occurance,
but I'd like to tell me the number of patients (1) at visited not number

of
proceedures (3).





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I sort by row instead of by column? PercivalMound Excel Worksheet Functions 7 August 28th 06 10:41 PM
Count number of unique items in a column that contains duplicates Steembeem Excel Worksheet Functions 3 February 2nd 05 01:51 AM
How do I sort a column of formulas in Excel? Gordon Excel Discussion (Misc queries) 2 November 27th 04 02:55 AM
How do I sort a column of formulas in Excel? Gordon Excel Discussion (Misc queries) 0 November 26th 04 04:19 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 01:43 PM.

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

About Us

"It's about Microsoft Excel"