Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    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

  2. #2
    Plutonium Lounger
    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... ?

  3. #3
    5 Star Lounger
    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 formula-based

  4. #4
    5 Star Lounger
    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- "Data-Sort" 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>

  5. #5
    WS Lounge VIP sdckapr's Avatar
    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 ctrl-shift-enter) 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

  6. #6
    Platinum Lounger
    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.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Platinum Lounger
    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.jkp-ads.com
    Professional Office Developers Association

  8. #8
    WS Lounge VIP sdckapr's Avatar
    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

  9. #9
    5 Star Lounger
    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

  10. #10
    5 Star Lounger
    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

  11. #11
    Gold Lounger
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •