# Rank text alphabetically with unique rank from 1, 2, 3, 4

1. ## Rank text alphabetically with unique rank from 1, 2, 3, 4

Hi everybody,

I would need some help on this formula that has been a head-scratcher for me. I have a list of text cells and would like to extract unique values to be able to put them on an array so that I can do sumifs on them (like a pivot table).

The list I have would look like this:
Component A
Component B
Component C
Component A
Component B
Component B
Component C

I would like to rank them from 1 to XXX depending on how many there are to be able to put them in an array. I also want for similar names to have a similar rank and the ranks to go up 1 by 1 (so no 1 and then 3 if there are two first similar names etc ...).

I tried doing this with a classic COUNTIF(List;"<"&End of list)+1 but it doesn't work as it considers similar names as same rank but ex-aequo so I end up with 1, 1, 3, 3, 3, 3, 3, 3, 3, 10 instead of 1,1, 2,2,2,2,2,2, 2, 3.

Thank you so much for your help!

2. ## Re: Rank text alphabetically with unique rank from 1, 2, 3, 4

Hello and welcome to the forum.

I'm sure that your sample data does not accurately depict your actual data.
That being said, the following formula in D9 produces the "Wanted outcome" in your example.

=LOOKUP(RIGHT(C9),{"A","B","C"},{1,2,3})

I end up with 1, 1, 3, 3, 3, 3, 3, 3, 3, 10 instead of 1,1, 2,2,2,2,2,2, 2, 3
If you built a formula that produced the first result but want something that produces the second result, see the link below.

Rank without skipping:
https://www.extendoffice.com/documen...g-numbers.html

3. ## Re: Rank text alphabetically with unique rank from 1, 2, 3, 4

Thanks, but you are right. It does not depict my actual data : the A, B, C that I added were just out of confidentiality. The data I have is like Motor, motor, engine, light, window and I have to rank them. There is unfortunately no "logical" obvious rank that I can use to come up with numbers.

4. ## Re: Rank text alphabetically with unique rank from 1, 2, 3, 4

In that case, please create a representative example along with the expected outcome (manually entered) of the formula.

5. ## Re: Rank text alphabetically with unique rank from 1, 2, 3, 4

Here it is. I managed to find a matrix formula that works except if there is a blank (which in my case there is). The only problem is the blank creates somewhere a division by 0 which messes up the entire formula.

6. ## Re: Rank text alphabetically with unique rank from 1, 2, 3, 4

In D9, try this

=IF(C9="","",SUM(IF((C9>\$C\$9:\$C\$21)*(\$C\$9:\$C\$21<>""),1/COUNTIF(\$C\$9:\$C\$21,\$C\$9:\$C\$21)))+1) Ctrl Shift Enter

7. ## Re: Rank text alphabetically with unique rank from 1, 2, 3, 4

It works like a charm. I have to say, I am pretty impressed as I had copied the first formula without being able to understand it and you made it work even better.

Hats off to you and thank you for answering so fast and so accurately!

8. ## Re: Rank text alphabetically with unique rank from 1, 2, 3, 4

Great, happy to help!

9. ## Re: Rank text alphabetically with unique rank from 1, 2, 3, 4

How do I do that? I have been looking for an option/button but don't seem to be able to find any.

10. ## Re: Rank text alphabetically with unique rank from 1, 2, 3, 4

Thanks for the rep!

11. ## Re: Rank text alphabetically with unique rank from 1, 2, 3, 4

Hey 63 falcondude!

One quick question based on the formula you were kind to help me with yesterday. I implemented it on my file but unlike the file you sent (Wanted outcome), if I try to match the last number (in my example 3), the match gives a N/A error. Would you know why this is happening? In the file wanted outcome, the same formula generates 1, 2 and 3 that are all recognized by the match formula ...

12. ## Re: Rank text alphabetically with unique rank from 1, 2, 3, 4

Try this in A22:

=IF(D22="","",IF(COUNTIF(\$D\$22:D22,D22)=1,MAX(\$A\$21:A21)+1,INDEX(\$A\$22:A22,MATCH(D22,\$D\$22:D22,0))))

13. ## Re: Rank text alphabetically with unique rank from 1, 2, 3, 4

Great, thanks. As I understand, instead of correcting the match, you changed the formula that now ranks the text by order of appearance?

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1