I wish I could...

Thanks in advance

Assuming source data running in A1 down, and what's required is an
auto-ascending sort of only the unique source data items in col F, with a
corresponding occurences count in col G

Put in E1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",IF(ISNUMBER(A1+0),A1+ROW()/10^10,IF(ISTEXT(A1),CODE(LEFT(A1))+ROW()*10^10))))

Put in G1:
=IF(ROW()>COUNT(E:E),"",INDEX(A:A,MATCH(SMALL(E:E,ROW()),E:E,0)))

Put in H1:
=IF(F1="","",COUNTIF(A:A,F1))

Then select E1:H1, copy down to cover the max expected extent of source data
in col A. Hide away col E. Col F will yield the list of uniques from col A,
sorted in ascending order*, while col G returns the corresponding count of
the uniques' occurences.
*Numbers (either real or text numbers) will be sorted ahead of alphas
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

http://cjoint.com/?kmd6wEsiac

Biff

"Max" <demec...@yahoo.com> wrote in message
news:91F8BA64-A907-401B...@microsoft.com...

http://cjoint.com/?kmehWBQGic

Biff

Lines
> Put in G1: ..
> Put in H1: ..
> Then select E1:H1 ...

should read as
> Put in F1: ..
> Put in G1: ..
> Then select E1:G1 ...

And for an auto-descending sort of only the unique source data items in col
F ..
try these ..

Put instead in E1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",IF(ISNUMBER(A1+0),A1+0,IF(ISTEXT(A1),CODE(LEFT(A1))*10^10-ROW()))))

Put instead in F1:
=IF(ROW()>COUNT(E:E),"",INDEX(A:A,MATCH(LARGE(E:E,ROW()),E:E,0)))

(no change to G1's formula)

Then just copy E1:G1 down as before to cover the max expected extent of

sorted in descending order*, while col G returns the corresponding count of
the uniques' occurences.
*Alphas will be sorted ahead of numbers (either real or text numbers)

> Put in E1:
> =IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",IF(ISNUMBER(A1+0),A1+ROW()/10^10,IF(ISTEXT(A1),CODE(LEFT(A1))+ROW()*10^10))))

should instead be:

Put in E1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",IF(ISNUMBER(A1+0),A1+0,IF(ISTEXT(A1),CODE(LEFT(A1))*10^10+ROW()))))

should instead be:
Put in E1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",IF(ISNUMBER(A1+0),A1+0,IF(ISTEXT(A1),CODE(LEFT(A1))*10^10+ROW()))))

"Biff" <biffi...@comcast.net> wrote in message
news:OOSznNa7...@TK2MSFTNGP03.phx.gbl...

For the benefit of the archives and future readers, think you should also
consider posting your suggestion in text besides providing the link to the
sample file (that's what I do <g>). The cjoint link is transient and would
expire eventually (after 14 days ?), so future readers would not derive the
benefit of your response.

news:%23weQmIa...@TK2MSFTNGP02.phx.gbl...

Personally, I would use Data>Sort and then Data>Filter>Advanced Filter to do
this. Maybe even record it as a macro.

Biff

news:OoQ9vca7...@TK2MSFTNGP05.phx.gbl...

How can anything which works be scary? More a work of fine art to appreciate
rather than anything to be scared about. The complicated looking parts of it
perhaps might take a little more effort on the OP's / readers' part to study
and understand how it works so that one could readily adapt it to suit or
cross apply it to other similar situations ..

.. tsk, tsk, lame excuse there <g>. I've seen many of your posts where you
had patiently explained your complex formulas very well in your responses.

well, the OP did express his / her wish for:
" .. a combination of functions to automate the sorting (in ascending or
desc. order)"

For discussions' sake, how should your formulas be modified for a descending
sort ?

For easy reference here, below is what you had suggested in your sample:

Source data assumed within A1:A21

Array-entered in B1:
=INDEX($A$1:$A$21,MATCH(MIN(COUNTIF($A$1:$A$21,"<"&$A$1:$A$21&"")+
COUNT($A$1:$A$21)*ISTEXT($A$1:$A$21)+100000*ISBLANK($A$1:$A$21)),COUNTIF($A$1:$A$21,"<"&$A$1:$A$21&"")+COUNT($A$1:$A$21)*ISTEXT($A$1:$A$21)+100000*ISBLANK($A$1:$A$21),0))

Array-entered in B2:
=INDEX($A$1:$A$21,MATCH(0,COUNTIF($A$1:$A$21,"<"&$A$1:$A$21)+COUNT($A$1:$A$21)*ISTEXT($A$1:$A$21)+100000*ISBLANK($A$1:$A$21)-SUM(COUNTIF($A$1:$A$21,"="&B$1:B1)),0))
B2 is then copied down to B21

B1:B21 yields the auto-ascending sort of the items in A1:A21

(Array-entered: Press CTRL+SHIFT+ENTER, instead of just pressing ENTER to
commit the formula)

For Ascending sort:
Put in E1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",IF(ISNUMBER(A1+0),A1+0,CODE(LEFT(A1))*10^10+ROW())))

For Descending sort:
Put in E1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",IF(ISNUMBER(A1+0),A1+0,CODE(LEFT(A1))*10^10-ROW())))

(Other formulas remain unchanged)

Let me express my sincere thanks to both of you, not only for coming up with
brilliant solutions for my posted problem, but for your enlightening attitude
to share your polished, shining knowledge, for volunteering to help us better
understand the joy of using XL.

God bless you and keep up this marvelous job!
gmisi

In each formula where you see "<" just change that to ">".

Biff

news:uYZHq3b7...@TK2MSFTNGP05.phx.gbl...

Biff, thanks !

Biff

news:eXPFvDm7...@TK2MSFTNGP03.phx.gbl...