Comparing Rank vs Grouping to determine Outcome

Good Afternoon Team

Hope all is well.

I am hoping you can help me

Aim : Compare the ranking (Column C) for a given group id (Column B) and populate (Column c) with either ( master , Non Master, or Joint master)

How to define Column C:

Master = Is the rank for a particular group id Higher than the other Rank within the group id
Non Master = Is the rank for a particular group id Lower than the other Rank within the group id
Joint Master = If both ranks for a particular group id are the same

please note that my data set is approx 5000 Rows

I have attached example mock up example

Are you still using Excel 2007?

In Mock-up Examples sheet, in Cell C2, use the below formula and drag down:

Formula:

Please Login or Register  to view this content.

In cell D2, use the below formula and drag down:

Formula:

Please Login or Register  to view this content.

As for your answer to question asked by Olly - please update your profile to correctly state version. The answers depend on it (some functions were introduced later and were not available for Ex 2007 users)
You can do it here: https://www.excelforum.com/profile.php?do=editprofile

You could use Power Query to transform your data and calculate the Outcome per row. Format your source data as a table, then use:

Please Login or Register  to view this content.

There are multiple ways you can format your output, but I'd suggest loading to the data model, then using pivot tables.

See attached worked example.