+ Reply to Thread
Results 1 to 13 of 13

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

  1. #1
    Registered User
    Join Date
    09-29-2017
    Location
    London
    MS-Off Ver
    2010
    Posts
    40

    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!
    Attached Files Attached Files
    Last edited by Xenthys; 09-29-2017 at 11:35 AM.

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    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. #3
    Registered User
    Join Date
    09-29-2017
    Location
    London
    MS-Off Ver
    2010
    Posts
    40

    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. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    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. #5
    Registered User
    Join Date
    09-29-2017
    Location
    London
    MS-Off Ver
    2010
    Posts
    40

    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.
    Attached Files Attached Files

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    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. #7
    Registered User
    Join Date
    09-29-2017
    Location
    London
    MS-Off Ver
    2010
    Posts
    40

    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. #8
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

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

    Great, happy to help!

    If that solved your question, please mark this thread as SOLVED.

  9. #9
    Registered User
    Join Date
    09-29-2017
    Location
    London
    MS-Off Ver
    2010
    Posts
    40

    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. #10
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

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

    Click on "Thread Tools" from the menu link above your first post.

    Thanks for the rep!

  11. #11
    Registered User
    Join Date
    09-29-2017
    Location
    London
    MS-Off Ver
    2010
    Posts
    40

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

    Thank you in advance!
    Attached Files Attached Files

  12. #12
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,892

    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. #13
    Registered User
    Join Date
    09-29-2017
    Location
    London
    MS-Off Ver
    2010
    Posts
    40

    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?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 6
    Last Post: 10-10-2014, 01:56 AM
  2. Replies: 8
    Last Post: 02-20-2014, 07:03 PM
  3. RANK - Non unique values, contiguous rank required
    By PaulBo in forum Excel General
    Replies: 9
    Last Post: 02-20-2014, 10:34 AM
  4. Breaking ties with rank alphabetically
    By ktms in forum Excel General
    Replies: 7
    Last Post: 11-03-2011, 07:06 PM
  5. RANK numerically and then alphabetically
    By liam-the-1-n-only in forum Excel General
    Replies: 7
    Last Post: 05-12-2010, 10:30 AM
  6. Formula to rank cells alphabetically
    By Dave Shaw in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-10-2006, 09:10 AM
  7. Replies: 1
    Last Post: 08-15-2005, 05:05 PM

Bookmarks

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