compile list
Do I also need to change $1:1 to something else.
No. Replace rng with your actual range or create the defined name rng and
use it.
=IF(ROWS($1:1)<=SUM((C$2:C$294<"")/COUNTIF(C$2:C$294,C$2:C$294&"")),INDEX(C$2:C$294,S MALL(IF(C$2:C$294<"",IF(ROW(C$2:C$294)-MIN(ROW(C$2:C$294))+1=MATCH(C$2:C$294,C$2:C$294,0) ,ROW(C$2:C$294)-MIN(ROW(C$2:C$294))+1)),ROWS($1:1))),"")
=IF(ROWS($1:1)<=SUM((rng<"")/COUNTIF(rng,rng&"")),INDEX(rng,SMALL(IF(rng<"",IF (ROW(rng)-MIN(ROW(rng))+1=MATCH(rng,rng,0),ROW(rng)-MIN(ROW(rng))+1)),ROWS($1:1))),"")
Biff
"smonsmo" wrote in message
...
Do I also need to change $1:1 to something else. I recieve a column of
blanks now.
"T. Valko" wrote:
In the formula, rng refers to your range C2:C294.
I have a column c2:c294 that contains Job names.
The formula *MUST* be entered as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER).
Biff
"smonsmo" wrote in message
...
I entered c1 in each of the rng. Is that correct? If so, when I copy
down,
it only works on the first line. I assume I did something wrong. help
"T. Valko" wrote:
Try this:
Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just
ENTER):
=IF(ROWS($1:1)<=SUM((rng<"")/COUNTIF(rng,rng&"")),INDEX(rng,SMALL(IF(rng<"",IF (ROW(rng)-MIN(ROW(rng))+1=MATCH(rng,rng,0),ROW(rng)-MIN(ROW(rng))+1)),ROWS($1:1))),"")
Copy down until you get blanks
Biff
"smonsmo" wrote in message
...
I have a column c2:c294 that contains Job names. There may be
identical
entries. I need to compile a list of all entries without duplicates
or
blank
cells. This will need to be done automatically. Data sort or a
macro
are
the obvious choices however in this case will not serve.
Any Ideas?
Thanks
|