compile list
 

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

compile list
 

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

compile list
 

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

compile list
 

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

compile list
 

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

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

compile list
 

I cant seem to get this formula to work past the first row.
"smonsmo" wrote:

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

compile list
 

Biffs formula works perfectly. Press F2 to edit the first formula, enter as
array (ctrl + Shift + Enter) and drag down

If this still does not work look at you original data is the job# exactly
the same on each row?

Regards

Peter

"smonsmo" wrote:

I cant seem to get this formula to work past the first row.
"smonsmo" wrote: