Rank without duplicates, skipping ranks and allowing for new entries

[SHW2022]

Hi,

I am trying to put a list of companies in alphabetical order (by formula and not using the sort function) and I think the first step to do this is to rank the companies.

What I have is my list looks a bit like this

Company A
Company B
Company A
Company C
Company C
Company D

So I would want all Company A's to have rank 1 , all Company B's have rank 2 etc. The rank function would rank Company B as 3 as there are two Company A's
I found the following formula in a different thread which works: =1+SUMPRODUCT((List of names>Company Name)/COUNTIF(List of Names, List of Names))
However, it can't seem to handle if there is a blank cell at the end of my "List of Names"
I want there to be blanks in my defined range so that if for example "Company E" gets added it then gets assigned a rank

Can anyone see a work around?

 

[SHW2022]

Never mind I have just discovered the unique function!

 

[smozgur]

Can you please share the usage of the function for your question, then the post could be marked as the solution as well as it helps future readers? Otherwise, please do not mark a post as solution that doesn't contain an answer.

 

[Peter_SSs]

Never mind I have just discovered the unique function!

Welcome to the MrExcel board!
Seems odd to me that you are happy to use the UNIQUE() function but not the SORT() function.

 

[SHW2022]

Sorry, I think I misspoke in my first message. Happy to use the sort() function but didn't want to use the sort button as I need to keep my data in its original order.
I am trying to alphabetise my list that has numerous duplicates and my plan was to rank them somehow (as per my request) and then use an Xlookup to order the names on a separate tab. However I found that I can use =sort(unique(List of Names)) and this achieves what I am trying to do without having to create a ranking column.

 

[Peter_SSs]

Happy to use the sort() function but didn't want to use the sort button ...

Ah, now I understand. Thanks for the clarification.