index-match gives duplicate results, how to resolve this?

[rewmel]

Hi all, back again with another request.
I am looking for a simpler/better/more complete way to achieve what I have done (in a rather convoluted way) to get the listing in cols P & S, rows 183:360, on the 'team all-time calcs' sheet.

I came up with my solution many years ago but it is limited to finding the first 7 names that have the same corresponding value. Eventually the formula in col P will not accept any more permutations and there are often situations where many more 'teams' have the same value, so I want to find a more complete solution.

I am revisiting this issue to get a list with no duplicates, but with no success so far, so here I am!

Attached is a very cut down xlsx file, where you can see the teams data.

I would greatly appreciate your expert help. Thanks.

[6StringJazzer]

I'm not following your question. The formula in column P (starting in row 183) is just

Please Login or Register  to view this content.

I can't imagine that's the thing you are trying to simplify. The formula in S is even simpler.

the first 7 names that have the same corresponding value

First 7 names where? Which value? Corresponding to what?

Can you clarify:

What is the source data
What result do you want
Where do you want the result to go

?

[rewmel]

Sorry for not being clearer, I guess I fell into the trap of 'it makes sense to me, so it should to others'.

The data is in cols B&C. Col B = number of starts, col C = team name.
I want this data sorted into a table of most starts to least starts and match which team has made the starts eg Ferrari 1095, McLaren 967 etc.
A simple index/match gives only the first team that has that value eg Virgin 38 and for subsequent values of 38 it will also only give 'Virgin'.
My calculations in cols I-N & X-AC are my way of determining the other teams that also scored that value, but it is limited to 7 different names. It then duplicates the 7th name.
Formula in cols P&S are my 'simple' way of choosing which name should go in which cell.

What I am after is a formula, index/match with some wizardry I am guessing, that will address the duplicates eg, there are 13 teams that started 2 times and 27 teams started only once. (see row 313)

The data in cols B&C is currently 'correctly' sorted however this is because I have just obtained the data from another source, already sorted. As time goes on this will cease to be sorted correctly.
I want to also use the solution I hope 'you' can provide for the drivers' data also (which is not in the sample xlsx). This driver data (also in cols B&C in my complete xlsx) is no longer correctly sorted as it has been collected by me over the past couple of decades.

Hopefully this helps and doesn't confuse further.

[AliGW]

Before we go any further with this, please update your forum profile. There is no Excel 2022 product, so which one do you really have? Newer versions are 2021, 2024 and 365. This is important. Thanks.

[rewmel]

ok, done. my bad.

[JeteMc]

Try the following in cells P183 and down:

Please Login or Register  to view this content.

Let us know if you have any questions.

[rewmel]

Brilliant, exactly what I was after, thanks.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.