how to concatenate AND show ranking formulas

[Tony0731]

Hi,
I feel sure that someone can give me an example to solve my problem:
I want to rank as per example AND also concatenate the names.

I have made up a small example of what I mean, please someone give me an example as I am really struggling here.

Thanks in advance

[AliGW]

Excel 2019 or something newer?

[Tony0731]

office 365 I believe

[AliGW]

OK - please check and then update your forum profile.

[Tony0731]

Done, Thanks Ali

[AliGW]

Try this:

=LET(s,SORTBY(A2:E5,E2:E5,-1),r,SEQUENCE(ROWS(s),,1,1),"Rank "&r&" = "&INDEX(s,,1)&" "&INDEX(s,,2)&" "&INDEX(s,,5))

[Fluff13]

Another option

Please Login or Register  to view this content.

[Tony0731]

WOW, Ali, that just blew me away, been trying for days to do what you have done in minutes.
Would you be able to put this into col F for me as per the example?

Thanks so much.

[AliGW]

What do you mean?

In F2 copied down:

=RANK(E2,$E$2:$E$5)

???

Or:

="Rank "&RANK(E2,$E$2:$E$5)&" = "&A2&" "&B2&" "&E2

[AliGW]

Or use Fluff's without the SORT:

=BYROW(CHOOSECOLS(A2:E5,1,2,5),LAMBDA(br,TEXTJOIN(" ",,"Rank",RANK(TAKE(br,,-1),E2:E5),"=",br)))

[Fluff13]

If you want it sorted as you originally showed, a slight tweak to my suggestion if you have more than 9 rows

Please Login or Register  to view this content.

[AliGW]

Or:

=LET(s,A2:E5,r,BYROW(s,LAMBDA(r,RANK(TAKE(r,,-1),E2:E5))),"Rank "&r&" = "&INDEX(s,,1)&" "&INDEX(s,,2)&" "&INDEX(s,,5))

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

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[HansDouwe]

This formula does exactly the same as the previous formulas in post 11 & post 12:

Please Login or Register  to view this content.

[HansDouwe]

Or a little bit shorter:

Please Login or Register  to view this content.

[AliGW]

This formula does exactly the same as the previous formulas in post 11 & post 12:

No, it doesn't, Hans - they do the same as posts #6 and #7. The sorting aspect is no longer required - the OP's workbook was misleading.

[HansDouwe]

I see no difference in the result between the formulas in posts 6 and 7 and in posts 11 and 12.
All formulas put rank 1 at the top.

The formulas in posts 11 and 12 - contrary to what is said - do not show the result in the original order.
[If you need the original order in that case only this suffice: =LET(v,E2:E13,"Rank "&RANK(v,v)&" = "&A2:A13&" "&B2:B13&" "&v)]

[AliGW]

Sorry - not post #11, but post#12 DOES show the result in the original order.

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	F	G
1	Ranking
2	Rank 2 = Elvis Presley 23	=LET(s,A2:E5,r,BYROW(s,LAMBDA(r,RANK(TAKE(r,,-1),E2:E5))),"Rank "&r&" = "&INDEX(s,,1)&" "&INDEX(s,,2)&" "&INDEX(s,,5))
3	Rank 3 = Elton John 18
4	Rank 4 = Clint Eastwood 4
5	Rank 1 = John Wayne 66

Sheet: Sheet1

[HansDouwe]

In that case there is wrong Excel file attached in Post #12.

Attached Excel file Post #12:

[AliGW]

You're right about that! I've just changed it. Thanks.

[Tony0731]

Hi all,
Been at work and only just had chance to look at the replies, Thanks to all for offering a solution they are all great.
Can someone please look at my example shaded in yellow on the attached SS. This is ideally how I want the result to look.
Not sure how to deal with the issue of having more than one answer showing the same visits though.
Sure you guys know how to solve this, that`s why this is the No site for Excel

Thanks in advance

Tony

[Fluff13]

How about

Please Login or Register  to view this content.

[AliGW]

Why did you ask for the convoluted concatenation when in fact you wanted the results in a table???

Is this it now, or are you going to change the requirements again?

[HansDouwe]

This formula takes into account that the rank number can be equal:

Please try

Please Login or Register  to view this content.

[Tony0731]

Sorry, Ali, my bad.
I should have been more clearer with my problem
I do really appreciate any help given

[AliGW]

Check out HD's offerings above.

[Fluff13]

I do really appreciate any help given

Did you try my suggestion in post#21?

[HansDouwe]

@Fluff your formula in Post 21 returns always the numbers 1,2,3,4 and 5.
Also when the rank is equal.

My formula in post 23 returns the real rank number.

@Tony Is this what you want?

If so, please consider adding reputation to all helpers you think they deserve.
In that case please click * Add Reputation left below their answers.

[Fluff13]

@Fluff your formula in Post 21 returns always the numbers 1,2,3,4 and 5.
Also when the rank is equal.

Very good point, didn't think of that.
How about

Please Login or Register  to view this content.

[Tony0731]

If I wanted to slightly change the results to show the top 10 instead of the top 5 what would I need to change? Would it be the 5 at the end of the formula to 10?

Thanks again all for the fantastic help I have received

[Fluff13]

Would it be the 5 at the end of the formula to 10?

That's right.

[HansDouwe]

You are Welcome!

Thanks for the feedback and rep . Glad to have helped.