Extract unique values with criteria, and sort them

  • Thread starter Thread starter Mark Worthington
  • Start date Start date
M

Mark Worthington

I have read with interest the single formula (using standard
functions) to extract unique values and sort them (minus the criteria
bit)….. the latest I have from Stephen Dunn is :

=INDEX(Data,MATCH(SUM(COUNTIF(Data,OFFSET(CellStart,0,0,SUM(ROW()-ROW(CellStart))))),
COUNTIF(Data,"<"&Data)+ISTEXT(Data)*COUNT(Data)-ISBLANK(Data),0))

array entered. I'll call this "SD_Formula".

Now this is very smart indeed. However, it does produce the #N/A
error, and using standard error checking fails, ie:

=IF(ISNA(SD_Formula) ,"", SD_Formula) does not work.

It appears that SD_Formula must be completed before any error checking
occurs (an internal Excel bug?!). A colleague of mine had a brainwave,
and decided to force the completion of SD_Formula BEFORE the effective
error checking. The solution is to create a named formula. Defining a
name such as Unique_Sort with the Refers To = SD_Formula allows the
following cell entry :

=IF(ISNA(Unique_Sorted),"",Unique_Sorted)

array entered. This removes the #N/A error.

I thinks that solution compliments the original formula!

Mark
 
Hi Mark,

It's quite spooky that I chose today to re-join the group, and here's one of
my formulae from quite some time ago!

Anyway, you don't need to re-state the formula to calculate when to use "".
At the time I was working on the basis that it is relatively simple to omit
the #N/A problem by counting the number of uniques and comparing it to the
row number; forgetting that future users of the formula may not realise that
point. My apologies. Try this:

=IF(ROW()-ROW(CellStart)+1>SUMPRODUCT((Data<>"")/
COUNTIF(Data,Data&"")),"",SD_Formula)


Steve D.
 
Steve,

I love that phrase "relatively simple"!!

Thanks for the post and the solution; it's much neater keeping it
self-contained on the cell formula.

I have modified it to work for data in rows rather than columns,
adding a comma and changing Row to Column. A simple thing to do, but
may be worth posting for other people's benefit.

For data in columns :

=INDEX(Data,MATCH(SUM(COUNTIF(Data,OFFSET(CellStart,0,0,SUM(ROW()-ROW(CellStart))))),
COUNTIF(Data,"<"&Data)+ISTEXT(Data)*COUNT(Data)-ISBLANK(Data),0))

For data in rows :

=INDEX(Data,MATCH(SUM(COUNTIF(Data,OFFSET(CellStart,0,0,,SUM(COLUMN
()-COLUMN (CellStart))))),
COUNTIF(Data,"<"&Data)+ISTEXT(Data)*COUNT(Data)-ISBLANK(Data),0))

Regards,

Mark
 
I knew that was a bad choice of words the moment that I hit send (often the
point that I realise what a twit I'm being).

Relative to the development process of the main formula, and relative to
experience, etc, etc...



Mark Worthington said:
Steve,

I love that phrase "relatively simple"!!
Click to expand...
<snip>
 
Steve,

No offence meant. And I agree, "relative to the development process of
the main formula" it certainly appears simple!

Cheers,

Mark
 
Back
Top