Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort strings (Ex2003)

    Looked: didn't find. Tried... no success (yet)

    Like to rank strings using a formula (not "data sort", not VBA -that's easy)

    Example:
    A1 B
    A2 A
    A3 A

    Should give in B1 to B3: 2, 1, 1 meaning "B" is second, "A" is 1st. Of course, this should work for longer strings & more rows. Any tips?
    Tnks
    Erik Jan

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sort strings (Ex2003)

    Hi Erik Jan

    May have missed your point here but could you not just use VLOOKUP:

    =VLOOKUP(A1,$D$1:$E$3,2,TRUE)

    Where D1:E3 contain the ranks for an A, B and C
    Jerry

  3. #3
    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: Sort strings (Ex2003)

    How about the answers in the thread you started at Re: Sorting, but no VBA? (EXCEL 97/2000)?

    Steve

  4. #4
    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: Sort strings (Ex2003)

    Shouldn't the rank be 3,1,1? [if you have 2 firsts, there is no second...]
    Then :
    <pre>=COUNTIF($A$1:$A$3,"<"&A1)+1</pre>


    will give the answer

    Steve

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort strings (Ex2003)

    Thanks, yeah, of course I found these when I searched too. Here however I wanted to use formulas to display the TEXT in the right order, In my current 'problem', the text is fixed (just text) and I want cells to display the order like (1..2..3)

  6. #6
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort strings (Ex2003)

    Sorry... don't understand this one. Can you clarify (also: see my other replies)

  7. #7
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort strings (Ex2003)

    Thanks, the formula sort of works (I had 'distilled' something similar from searches to earlier similar cases).

    HOWEVER... what I really want is that all numbers are sequential (I never want to have gaps); so if I have 5 numbers to rank, I'd like to get e.g. 1,1,2,3,4 as an output, or 1,2,3,4,5 or 1,1,1,1,1 or 1,2,2,3,3 or 1,2,3,3,3 etc.

    Sorry to make it difficult but that's really what I need...

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Sort strings (Ex2003)

    See if this works for you - assumes your data is in A1:A10:
    =SUMPRODUCT(($A$1:$A$10<A1)*(1/COUNTIF($A$1:$A$10,$A$1:$A$10)))+1
    entered in B1 and copied down.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort strings (Ex2003)

    Brilliant, that works!

    Use this one to auto-adjust to (upto) 200 rows:

    =SUMPRODUCT((INDIRECT("$A$1:$A$" & COUNTA($A$1:$A$200))<A1)*(1/COUNTIF(INDIRECT("$A$1:$A$" & COUNTA($A$1:$A$200)),INDIRECT("$A$1:$A$" & COUNTA($A$1:$A$200)))))+1

    (Of course, a helper-cell with just the output of the row-count would make this easier, but the formula above is 'self-supporting')

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Sort strings (Ex2003)

    Probably easier to define a named range (let's call it DataRange) as:
    =OFFSET($A$1,0,0,COUNTA($A:$A),1)
    and then use:
    =SUMPRODUCT((DataRange<A1)*(1/COUNTIF(DataRange,DataRange)))+1

    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort strings (Ex2003)

    Yep, makes sense but believe there's an error in here, I get #DIV/0 errors now

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Sort strings (Ex2003)

    Do you have any data in column A that doesn't belong in the list? If your defined name includes blank cells in it, you will get #DIV/0 errors.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort strings (Ex2003)

    I take that back... still believe your solution is brilliant but regretfully I just found out that IT DOES NOT WORK.

    If I use a longer list with more duplicates I get errors. Hope you can find the problem and correct, the logic is beyond me a bit at this time

  14. #14
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort strings (Ex2003)

    Yes, there are blank cells (my formula was 'auto-adjusting' to cope with longer lists). PLease note another reply I just send where I think I've stumbled on an error: the results are incorrect sometimes. Hope you can help

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Sort strings (Ex2003)

    Can you post a sample that does not work? (you may need to zip it to keep it within the 100KB limit)
    Regards,
    Rory

    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

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