# Thread: Sort strings (Ex2003)

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

3. ## Re: Sort strings (Ex2003)

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

Steve

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

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

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

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

11. ## Re: Sort strings (Ex2003)

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

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

13. ## 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. ## 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. ## 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)

Page 1 of 2 12 Last

#### Posting Permissions

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