Display corresponding name for same number

**kench** · 09-15-2018, 11:56 PM

Hi all expert,

I need help for my work.

Problem :

I have a team of 5 sales person. But when i used formula to list out the sales record in order, with sales person have the same numbers(119) it will only display the first names(Alan) for both records. It should display rank 3 as Melisa having same score as Alan.

Example in Cell
A B C D E F G H
1 Name Product A Product B Total Rank Score Name
2 Alan 90 29 119 1 199 Sam
3 Richard 20 8 28 2 119 Alan
4 Sam 98 101 199 3 119 Alan
5 Teresa 0 0 0 4 28 Richard
6 Melisa 66 53 119 5 0 Teresa

I am using this formula : =INDEX($A$2:$A$6,MATCH(G2,$D$2:$D$6,0))

Thanks alot in advance for your great help.

Best regards
Kc

Thanks

**BadlySpelledBuoy** · 09-16-2018, 02:09 AM

One quick and easy way would be to use a helper column to create a unique rank.
Column F in the attached uses this formula in F2 and copied down to achieve that.

Formula:

Please Login or Register  to view this content.

Not the rank numbers in F2 & F6 even though these people have the same scores.

Then use that rank to lookup the values into order.

Hope that helps.

BSB

**FDibbins** · 09-16-2018, 02:14 AM

That was pretty much what I came up with too, BSB...
=RANK($E2,$E$2:$E$6)+COUNTIF($E2:E2,$E2)-1
copied down

**BadlySpelledBuoy** · 09-16-2018, 02:16 AM

Great minds think alike, Ford

BSB

**maras_mak** · 09-16-2018, 02:17 AM

Hi, my suggestion is an array formula

Please Login or Register  to view this content.

Regards.

**kench** · 09-16-2018, 04:40 AM

Hi BadlySpelledBuoy & FDibbins

Thanks alot, it helps.

But can you spend a little bit more time to explain your formula,

=RANK(E2,$E$2:$E$6)+COUNTIF(E$2:E2,E2)-1

Thanks alot in advance

Best regards
Ken

**BadlySpelledBuoy** · 09-16-2018, 09:08 AM

Hi Ken,

The formula works by first ranking the scores using this part of the formula:

Formula:

Please Login or Register  to view this content.

And this part deals with tied scores:

Formula:

Please Login or Register  to view this content.

This second part is counting how many times a value appears in an expanding range. Note the use of a $ in the first part of the range but not the second.
So it's checking how many times does the value in E2 appear in the range E2:E2. Answer = 1.
By the time this formula gets down to row 6 it's checking how many times does the value in E6 appear in the range E2:E6. Andswer = 2.

If you add that to the RANK part of the formula you get unique rankings. However your rankings would start at 2 rather than 1. So the -1 at the end of the formula deals with that.

Have a look at the attached where I've broken it down into separate columns.
Col L shows the result of the RANK section.
Col M shows the result of the COUNTIF section.
Col N shows the COUNTIF -1.
Col O shows the result of adding L & N together (or the same as the full formula provided earlier).

Does that help explain it at all?

BSB

**kench** · 09-17-2018, 01:00 AM

Hi BadlySpelledBuoy,

Very nice and clear explaination.

Appreciate your great help.

It really helps me alot in my excel.

Thanks alot.

Best regards
Ken

**AliGW** · 09-17-2018, 01:02 AM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

**kench** · 09-17-2018, 02:19 AM

Hi AliGM,

Thanks for the guidance.....I have marked this thread as Resolved.

Best regards
Ken

**Banana Pajamas** · 07-15-2023, 08:24 AM

Hi. May I ask if there are other way to do it?

**AliGW** · 07-15-2023, 08:38 AM

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Display corresponding name for same number

LinkBack

Thread Tools

Rate This Thread

Display

Display corresponding name for same number

Re: Display corresponding name for same number

Re: Display corresponding name for same number

Re: Display corresponding name for same number

Re: Display corresponding name for same number

Re: Display corresponding name for same number

Re: Display corresponding name for same number

Re: Display corresponding name for same number

Re: Display corresponding name for same number

Re: Display corresponding name for same number

Re: Display corresponding name for same number

Re: Display corresponding name for same number

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How to correctly display the correct calculated number and mask away exceeding number

If a cell begins with a 5 display that number if not display nothing

Number format to Display 20 character long number.

If number is Positive display in D20, if Number is negative display in D19

Working out a way to display the number of times a name or number is used on the page

Count A Number Of Times A String Appears but display the number sequence

display total above certain number and display

Bookmarks

Bookmarks

Posting Permissions