compile list

S

smonsmo

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
 
T

T. Valko

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
 
S

smonsmo

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

T. Valko

In the formula, rng refers to your range C2:C294.
I have a column c2:c294 that contains Job names.
Click to expand...

The formula *MUST* be entered as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER).

Biff
 
S

smonsmo

Do I also need to change $1:1 to something else. I recieve a column of
blanks now.
 
T

T. Valko

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,SMALL(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
 
B

Billy Liddel

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
 
Top