I wish I could...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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(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
 
Sorry, some typos in the preceding response ..

Lines
Put in G1: ..
Put in H1: ..
Then select E1:H1 ...
Click to expand...

should read as
Put in F1: ..
Put in G1: ..
Then select E1:G1 ...
Click to expand...

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

---
 
Correction to earlier formula ...
(with thanks to Biff for detecting this)
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))))
Click to expand...

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, thanks for detecting this

Correction to earlier formula ...
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))))
Click to expand...

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,

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.
 
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 Data>Sort and then Data>Filter>Advanced Filter to do
this. Maybe even record it as a macro.

Biff
 
Biff said:
The formulas are too complicated and will scare people off!
Click to expand...

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.

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

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)

---
 
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(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)

---
 
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
 
Back
Top