Results 1 to 11 of 11

20030523, 07:15 #1
 Join Date
 Jul 2001
 Location
 Terneuzen, Netherlands
 Posts
 895
 Thanks
 0
 Thanked 0 Times in 0 Posts
Sorting, but no VBA? (EXCEL 97/2000)
Every time I start to do some programming I hit onto new little things that I thought I knew but make me stumble again... <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
Yesterday, I wanted to sort a few (say 10) cells with text entries in alphabetical order. Like I said above, I thought that would be simple and I wouldn't require any programming (I can do that):
I set out to use e.g. the RANK() or LARGE() EXCEL function to order the list. However when I did that it seemed that these functions work only on numbers.... Still, if I'd compare two strings [like e.g. =IF(A1>A2,"Larger","Smaller") ], the comparison in itself does work....
Question, can I simply sort some cells with text in EXCEL (no VBA) ?? [if not, I'll use VBA of course...]
Erik Jan

20030523, 07:21 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Sorting, but no VBA? (EXCEL 97/2000)
Data  Sort... ?

20030523, 07:23 #3
 Join Date
 Jul 2001
 Location
 Terneuzen, Netherlands
 Posts
 895
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sorting, but no VBA? (EXCEL 97/2000)
Sorry, I meant automatically (no user actions), so formulabased

20030523, 12:08 #4
 Join Date
 Jul 2001
 Location
 Terneuzen, Netherlands
 Posts
 895
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sorting, but no VBA? (EXCEL 97/2000)
And there I go again...
The fields I want to sort are actualy formulas (referring to cells on another sheet). It seems that a VBA approach is not as simple as I thought afterall (also the manual "DataSort" approach doesn't work here).
Let me clarify (if needed)...
Original Range:
<table border=1><td></td><td>A</td><td>1</td><td>Test</td><td>2</td><td>Alpha</td><td>3</td><td>John</td></table>
My Range I want sorted:
<table border=1><td></td><td>A</td><td>1</td><td>=A1</td><td>2</td><td>=A2</td><td>3</td><td>=A3</td></table>
So before sorting it obviously looks like:
<table border=1><td></td><td>A</td><td>1</td><td>Test</td><td>2</td><td>Alpha</td><td>3</td><td>John</td></table>
After sorting I want it to look like:
<table border=1><td></td><td>A</td><td>1</td><td>Alpha</td><td>2</td><td>John</td><td>3</td><td>Test</td></table>
Thus the formulas should be:
<table border=1><td></td><td>A</td><td>1</td><td>=A2</td><td>2</td><td>=A3</td><td>3</td><td>=A1</td></table>
Maybe it's more difficult then I initially thought (again). If the LARGE worksheet function would work, this would be a piece of cake, formula's would have to be:
<table border=1><td></td><td>A</td><td>1</td><td>=LARGE(A1:A3,1)</td><td>2</td><td>=LARGE(A1:A3,2)</td><td>3</td><td>=LARGE(A1:A3,3)</td></table>

20030523, 12:42 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Sorting, but no VBA? (EXCEL 97/2000)
Large works with numbers not text.
To use large you would have to create a FUNCTION whihc converts a "string" into a number. The numbers can get large!
You could look at first character take the CODE value of the character then go to the second take the code value /1000 and add them together then the third code value/1,000,000 etc etc until the end. (THIS WILL ONLY WORK FOR 5 DIGITS, since excel keeps 15 figures)
I would use the 1000 as the divisor since you have 256 values so each "three digits" represents a character.
After creating the function you would use it to add a column which contains the "value" of the text string (eg ABCD = 65.066067068)
And you can use LARGE on these values.
Or you could use an array formula (confirm with ctrlshiftenter) and avoid the extra column:
=large(UDFName(A1:A100),1)
=large(UDFName(A1:A100),2)
=large(UDFName(A1:A100),3)
Etc
Where UDFName is the name of the function which converts the text into a number
Steve

20030523, 13:20 #6
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sorting, but no VBA? (EXCEL 97/2000)
If it is OK to use an intermediate column:
I have this layout:
<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center valign=bottom>1</td><td valign=bottom>jan</td><td align=right valign=bottom>1</td><td valign=bottom>henk</td><td align=center valign=bottom>2</td><td valign=bottom>piet</td><td align=right valign=bottom>3</td><td valign=bottom>jan</td><td align=center valign=bottom>3</td><td valign=bottom>klaas</td><td align=right valign=bottom>2</td><td valign=bottom>klaas</td><td align=center valign=bottom>4</td><td valign=bottom>henk</td><td align=right valign=bottom>0</td><td valign=bottom>piet</td><td align=center valign=bottom>5</td><td valign=bottom>willem</td><td align=right valign=bottom>4</td><td valign=bottom>willem</td></table>
Cell B1 to B6 contain this ARRAY formula:
=SUM((A1>$A$1:$A$5)*1)
Cell C1:C6:
=INDEX($A$1:$A$5,MATCH(ROW()1,$B$1:$B$5,0))Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20030523, 13:25 #7
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sorting, but no VBA? (EXCEL 97/2000)
Or even better, use this array formula in cells B1:B5 (or any other column on rows 1:5):
=INDEX($A$1:$A$5,MATCH(ROW()1,COUNTIF($A$1:$A$5,"<" & A1:A5),0))Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20030523, 13:58 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Sorting, but no VBA? (EXCEL 97/2000)
Oh, I like this.
MUCH MUCH better than my suggestion: easier to implement and can be done with 1 large array as long as the list.
Very neat.
Steve

20030526, 07:20 #9
 Join Date
 Jul 2001
 Location
 Terneuzen, Netherlands
 Posts
 895
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sorting, but no VBA? (EXCEL 97/2000)
Jan Karel,
This is brilliant <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>, thanks!
Erik Jan

20030527, 08:58 #10
 Join Date
 Jul 2001
 Location
 Terneuzen, Netherlands
 Posts
 895
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sorting, but no VBA? (EXCEL 97/2000)
Update2: I'm still unsure, but have fixed it in my sheet but not (yet) found a solution that works in all situations.
Update: I'm unsure if the edit below is OK, additional changes might be required. I'm looking into it...
This doesn't always seem to work... here's an improvement:
=INDEX($A$1:$A$5,MATCH(ROW()ROW($A$1),COUNTIF($A$1:$A$5,"<" & $A$1:$A$5),0))
In stead of using ROW()1, I subtracted the first row from the current, now it works on any location in the sheet. I also had to make the last range fixed.
Erik Jan

20040902, 11:04 #11
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Sorting, but no VBA? (EXCEL 97/2000)
Have a look at this attached borkbook. It shows another way it possibly implement your requirement!
Regards,
Rudi