I wish I could come up with a combination of functions to automate the
sorting (in ascending or desc. order) columnA (the lenght is different on
each sheet) containing multiple text and/or number values (even blank ones)
so, that in colF will be the sorted list of unique values and colG will show
how many times this value figures in the unsorted list ---- but I can't, so I
ask for your kind help.

Thanks in advance

Venturing some thoughts ..

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(ISNUMB ER(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
---
"gmisi" wrote:
I wish I could come up with a combination of functions to automate the
sorting (in ascending or desc. order) columnA (the lenght is different on
each sheet) containing multiple text and/or number values (even blank ones)
so, that in colF will be the sorted list of unique values and colG will show
how many times this value figures in the unsorted list ---- but I can't, so I
ask for your kind help.

Thanks in advance

See this sample file:

http://cjoint.com/?kmd6wEsiac

Biff

"Max" wrote in message
...
Venturing some thoughts ..

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(ISNUMB ER(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
---
"gmisi" wrote:
I wish I could come up with a combination of functions to automate the
sorting (in ascending or desc. order) columnA (the lenght is different
on
each sheet) containing multiple text and/or number values (even blank
ones)
so, that in colF will be the sorted list of unique values and colG will
show
how many times this value figures in the unsorted list ---- but I can't,
so I
ask for your kind help.

Thanks in advance

This doesn't work on my sample file:

http://cjoint.com/?kmehWBQGic

Biff

"Max" wrote in message
...
Venturing some thoughts ..

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(ISNUMB ER(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
---
"gmisi" wrote:
I wish I could come up with a combination of functions to automate the
sorting (in ascending or desc. order) columnA (the lenght is different
on
each sheet) containing multiple text and/or number values (even blank
ones)
so, that in colF will be the sorted list of unique values and colG will
show
how many times this value figures in the unsorted list ---- but I can't,
so I
ask for your kind help.

Thanks in advance

Sorry, some typos in the preceding response ..

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(ISNUMB ER(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
source data in col A. Hide away col E. Col F will yield the list of uniques
from col A,
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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Correction to earlier formula ...
(with thanks to Biff for detecting this)

Put in E1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)1,"",IF(ISNUMB ER(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(ISNUMB ER(A1+0),A1+0,IF(ISTEXT(A1),CODE(LEFT(A1))*10^10+R OW()))))

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Biff, thanks for detecting this

Correction to earlier formula ...
Put in E1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)1,"",IF(ISNUMB ER(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(ISNUMB ER(A1+0),A1+0,IF(ISTEXT(A1),CODE(LEFT(A1))*10^10+R OW()))))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Biff" wrote in message
...
This doesn't work on my sample file:

http://cjoint.com/?kmehWBQGic

Biff

Biff,

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Biff" wrote in message
...
See this sample file:

http://cjoint.com/?kmd6wEsiac

Biff

The formulas are too complicated and will scare people off! Not only that,
I'm a one finger typer and sometimes it takes forever to explain my posts.

Personally, I would use DataSort and then DataFilterAdvanced Filter to do
this. Maybe even record it as a macro.

Biff

"Max" wrote in message
...
Biff,

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Biff" wrote in message
...
See this sample file:

http://cjoint.com/?kmd6wEsiac

Biff

"Biff" wrote
The formulas are too complicated and will scare people off!

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 ..

I'm a one finger typer and sometimes it takes forever to explain my posts.

... 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.

Personally, I would use DataSort and then DataFilterAdvanced Filter to
do this. Maybe even record it as a macro.

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*ISBLAN K($A$1:$A$21)),COUNTIF($A$1:$A$21,"<"&$A$1:$A$21&" ")+COUNT($A$1:$A$21)*ISTEXT($A$1:$A$21)+100000*ISB LANK($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)+10 0000*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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Just a slight revision to the earlier criteria formulas in col E ..

For Ascending sort:
Put in E1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)1,"",IF(ISNUMB ER(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(ISNUMB ER(A1+0),A1+0,CODE(LEFT(A1))*10^10-ROW())))

(Other formulas remain unchanged)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Dear Max & Biff,

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


"Max" wrote:

Just a slight revision to the earlier criteria formulas in col E ..

For Ascending sort:
Put in E1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)1,"",IF(ISNUMB ER(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(ISNUMB ER(A1+0),A1+0,CODE(LEFT(A1))*10^10-ROW())))

(Other formulas remain unchanged)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

You're welcome, gmisi !
Appreciate the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"gmisi" wrote in message
...
Dear Max & Biff,

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

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

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

Biff

"Max" wrote in message
...
"Biff" wrote
The formulas are too complicated and will scare people off!

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 ..

I'm a one finger typer and sometimes it takes forever to explain my
posts.

.. 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.

Personally, I would use DataSort and then DataFilterAdvanced Filter to
do this. Maybe even record it as a macro.

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*ISBLAN K($A$1:$A$21)),COUNTIF($A$1:$A$21,"<"&$A$1:$A$21&" ")+COUNT($A$1:$A$21)*ISTEXT($A$1:$A$21)+100000*ISB LANK($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)+10 0000*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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

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

Biff, thanks !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

You're welcome, Max!

Biff

"Max" wrote in message
...
"Biff" wrote:
For discussions' sake, how should your formulas be modified for a
descending sort ?

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

Biff, thanks !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---