Using conditional formatting or any other method to find the top 3 of a data set and then the next 3 in the remainder plus extra data

[Taz5678]

I am working on a spreadsheet that needs to identify the top 3 scores in a set number of columns. I have done this using conditional formatting but it is not essential if there is a better way to solve the following problem:

I need to use the remaining scores from the info above plus some additional ones to create the next top 3 but these cannot be the original ones. I need these clearly identified which is why I started with conditional formatting.

Whatever the solution, the formulas etc need to be set up so that changes to the scores can be inputted and therefore the colours change (or however it is identified).

Hope someone can help because I have spent too much time today trying to work this out!

 

[kevin9999]

Thank you for providing your actual data, it makes the issue much clearer. I note that the range to be calculated includes both text as well as numerical values. Unfortunately, this would require a level of VBA expertise beyond my capacity to provide. Hopefully, someone else on the forum will be able to assist. If you don't get a response in 24 hours, you can always "bump" your question by adding a comment. Good luck

 

[DanteAmor]

I need to workout the top 3 from columns T-AM (1) and then the top 3 from columns P, T-AM,AO-BJ (2). I need these highlighted in two different colours and then a total of (1) in column AN and a total of (2) in BK.

Top 3 of (1) takes priority over top 3 of (2) and there can be no duplicates.

Now imagine that I am 4 years old.

You can explain in these data, which are for you the top 3.
Not only point out which ones are the top 3, you must explain why those are the top 3 for you.

Top 3 of (1) takes priority over top 3 of (2) and there can be no duplicates.

Then, continuing with the data in your file, explain a scenario where there are duplicates and what would be the result you want.
If you want, you can explain everything on the sheet of your file and share your file on google again.
--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[Taz5678]

Thank you for providing your actual data, it makes the issue much clearer. I note that the range to be calculated includes both text as well as numerical values. Unfortunately, this would require a level of VBA expertise beyond my capacity to provide. Hopefully, someone else on the forum will be able to assist. If you don't get a response in 24 hours, you can always "bump" your question by adding a comment. Good luck

Now imagine that I am 4 years old.

You can explain in these data, which are for you the top 3.
Not only point out which ones are the top 3, you must explain why those are the top 3 for you.
View attachment 91901


Then, continuing with the data in your file, explain a scenario where there are duplicates and what would be the result you want.
If you want, you can explain everything on the sheet of your file and share your file on google again.
--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

Thanks Dante. I have re-uploaded the file with some manually added colours to try and explain better. The yellow columns are the ones that the top 3 score should come from. The green are the ones that the second top 3 scores come from plus any remaining yellow ones that haven't been included in the previous top 3. I have also manually highlighted a couple of cells to illustrate what makes them the top 3 scores. And if there are duplicates it doesn't matter which one as long as it is the high score so if a student had the following results:
Science 6
Geography 5
Computer Science 5
History 5
DT 5
PE 6
Art 7

Then the top 3 in yellow would be science, Geography and Computer Science/History

And the top 3 in green would be Art, PE...

and either PE or the leftover from Computer Science and History as these all had scores of 5.


Hope this gives more clarity and thank you for trying to help me!

 

[DanteAmor]

Hope this gives more clarity

No, it's not clearer.
You did not share the new link to the file.

As I said in the post above, it's not enough to paint color, you must clearly explain why the top 3 are blue, why the top 3 are green.

I told you that I am 4 years old, without the explanation and without the file, and I am also out.