Excel 2021 - Dynamic TOP 10 table based on biggest values and two criteria

Hello to everyone,

I'm having a headache with this task, hopefully someone can help me. I'm using Excel 2016, and I'm an absolutely beginner user (basic formulas-tables).
Without further ado, let's cut to the chase.

NAME(COL A) POSITION (COL.B) STATS (COL.C)
Schweinsteiger* LWB 2666
Schweinsteiger* LB 2691
Schweinsteiger* CB 2701
Schweinsteiger* RWB 2666
Schweinsteiger* RB 2691
Schweinsteiger* CDM 2711
Schweinsteiger* CM 2701
Schweinsteiger* LM 2666
Schweinsteiger* LW 2666
Schweinsteiger* RM 2666
Schweinsteiger* RW 2666
Schweinsteiger* CAM 2666
Schweinsteiger* LF 2666
Schweinsteiger* RF 2666
Schweinsteiger* CF 2666
Schweinsteiger* ST 2666
Matthaus LWB 2642
Matthaus LB 2642
Matthaus CB 2642
Matthaus RWB 2642
Matthaus RB 2642
Matthaus CDM 2672
Matthaus CM 2682
Matthaus LM 2662
Matthaus LW 2642
Matthaus RM 2662
Matthaus RW 2642
Matthaus CAM 2672
Matthaus LF 2642
Matthaus RF 2642
Matthaus CF 2642
Matthaus ST 2642
etc. etc.same pattern etc. (400+ rows)

Some explanation of the table: Column A contains NAMES, that repeat based on POSITIONS (Column B) along with their corresponding STATS (Column C).
Column B has certain 16 values that repeat themselves (LWB, LB, CB, RWB, RB, CDM, CM, LM, LW, RM, RW, CAM, LF, RF, CF, ST - I have also created a helper column with this values). Table is dynamic, getting updates all the time.

All I want to do is this: Right next to it I want to create a new top 10 table like this:

RANK (COL.E) NAME (COL.F) POSITION (COL.G) STATS (COL.H)
1 data from COL A data from COL B data from COL C
2 data from COL A data from COL B data from COL C
3 data from COL A data from COL B data from COL C
etc. until 10 etc. etc. etc.

which will find biggest values from STATS - Column C based on biggest sum possible, given two NECESSARY criteria:
1. Only distinct-unique values from Column A (so as NAMES in TOP 10 table will be different-no duplicates)
2. Be able to control the range of Column B at will (let's say in example I want only to use 1 "LB"-2 "CB"-1 "RB"-2 "CDM"-1 "LM"-1 "RM"-2 "ST" to create my list).

Second non-necessary task:
Ideally, if possible, when the conditions are met and the problem solved I would like also to match the values from STATS (COL.H) with corresponding NAME (COL. F) and POSITION (COL.G)

I think that a use of a big LARGE IF function would do the trick, but after endless efforts I gave up and started looking for help online. For the second task I have already a suitable INDEX/MATCH formula but it's based on my calculations, so maybe it's going to need an update. I note again that I'm a newbie in Excel and I would like to be guided step by step if anybody has a solution to the problem(s).

Thanks a lot in advance, and sorry for my bad English!

With a pivot table to analyse the data.

I sorted it on column B (CAM).

See the attached file.

I'm not sure did I understand correctly.
If you don't mind about helper cells. Please try

Inser new field for Helper with formula
=IF(AND(A2<>"",A2<>OFFSET(A2,-1,0,1,1)),SUMIFS([STATS],[NAME],A2),"")

and follow with these formula (criteria changed)

for NAME
=INDEX(Table1[NAME],MATCH(LARGE(Table1[Helper],$F3),Table1[Helper],0))

for POSITION (array formula)
=INDEX(OFFSET($B$1,MATCH($G3,$A:$A,0)-CELL("ROW",$A$1),0,COUNTIF($A:$A,$G3),1),
MATCH(1,(OFFSET($A$1,MATCH($G3,$A:$A,0)-CELL("ROW",$A$1),0,COUNTIF($A:$A,$G3),1)=$G3)*
(OFFSET($C$1,MATCH($G3,$A:$A,0)-CELL("ROW",$A$1),0,COUNTIF($A:$A,$G3),1)=$I3),0))

for STATS
=AGGREGATE(14,6,
OFFSET($C$1,MATCH($G3,$A:$A,0)-CELL("ROW",$A$1),0,COUNTIF($A:$A,$G3),1)/
(1/(MATCH(OFFSET($B$1,MATCH($G3,$A:$A,0)-CELL("ROW",$A$1),0,COUNTIF($A:$A,$G3),1),$K$3:$K$9,0)>0)),1)

Regards.

YOU ARE A FREAKING BEAST menem, thanks a lot. You've understood what I'm after and you're close to the solution. There's a thing though; The second criteria is still not applied. I want the top 10 list to be made up of these values (and to be able to change later at will), 1 LB-2 CB-1 RB-2 CDM-1 LM-1 RM-2 ST, and in the attached file you posted there are errors to the top 10 table based on that criteria. Can you modify that correctly?

Kudos to you also wk9128 for having a try! This function though is just not working, the desired solution is something like menem suggested.

UPDATE: In addition I upload also an hand drawn explanatory diagram that may help you understand better.
Screenshot_1.png

Could you please show your desired result ?
Btw, 2nd condition can be changed at will by change data in range K3:K9 (or resize them if needed) - not sure did it met your condition.

Regards.

Your approach was good in the file you uploaded, but as you can see there are still wrong values in the POSITIONS column in the TOP 10 table. Probably a part(s) in your formulas need editing.

I upload here how the criteria set must be (don't care about the order as long as they're correct), filled them with red color.
Stats are randomly put in this upload (so that you understand what I'm after), I want the maximum values possible from COLUMN C AND the 2 criteria met. I suggest maybe a big LARGE IF formula in the STATS column may do the trick.
Thanks again for your time you're very kind.

Not sure did I met your requirement.

I've change some logical of STATs to lookup from vertical to horizontal
and modify criteria to be 'used' after found matching data.

Please check in the file.

Regards.

Hello menem, unfortunately I found a statistical error in the table (Kohr with bigger STAT 2522 in position CB could be used instead of Ballack who has 2508 in the CB position). But I think you're closing in to the solution (other 2 criteria were correctly applied).
Maybe it's my fault I didn't explain well- for the creation of the TOP 10 table 1st and foremost criteria is the maximum sum of the biggest values in STATS Column. This though has to be performed through under these two conditions, a)unique NAMES b)predetermined desired POSITIONS. Table A (the database table which we're getting the data from) is compact and the values correspond horizontally one to other, so in result we want 10 rows from it to form the TOP 10 table.

Also please explain to me how to use your table, it seems too complex for a newbie user like me. Thanks again for your time and patience.

Please verify again,

Kohr total value of STATS is 39957
but Ballak is 40288, as your explanation, Ballak should be choosed instead of Kohr.

or did you mean total is not all total , just only the biggest of them (each NAME) to be included ?


Regards.

Do you accept the auxiliary (helper) column? Attachments are only limited to adding heiper (auxiliary) columns to complete tasks







OFFICE 2016 formula is too complicated to write. OFFICE 365 is much simpler

Please watch this animation teaching file FILE name as SV1.gif
After the file is downloaded and decompressed, you can use the left mouse button to click twice to watch the teaching file (video) or open it with explorer.

O365 is better than Excel 2016. ^_^

PS., My English is not good , so I might not understand all you try to explain >_<'

Regards.

Hello friends, back here again.
I upgraded to Office 2021, but still can't solve the problem.

wk9128 Your formula has functions of Office Insiders beta, which is not available in my edition (for example VSTACK-HSTACK, TAKE, DROP, REDUCE).
However I did find online the Microsoft Office for Web (which has all these new functions), copied and applied your formula there, but it still didn't work (to make things worse it must've been sloppy because when I changed one of the values from desired POSITIONS tab the results went statistically wrong).

I don't give up hope though. To help you a little I reorganized my data in the file that I'm uploading (I want only the STATS and NAMES columns autofilled now according to these POSITIONS, the rest criteria same as described before. You can add helper columns and delete the RANK column if you want, or use the second sheet where data is more categorized divided into multiple tables).
And also now I can use dynamic arrays and all the new functions of Office 2021 (such as XLOOKUP, SORT, UNIQUE etc.), except for these of Office Insiders Beta.

I thank you everyone again and again for your time and patience.

Hello to everyone, had a very busy week and didn't have the time to keep up with the forum and the thread.

wk9128 your latest formula is the closest one that may drive the problem to its solution. Every criteria and conditions I set are met, except for one last thing (sorry for my bad English, I will upload screenshots so that you understand better):
In your suggested formula the top 10 list is indeed made of DESIRED POSITIONS that are available, but the thing is I also want use of these DESIRED POSITIONS absolutely exactly as I set them(my fault I didn't explain that earlier).
Screenshot_2.png
These are the results according to your formula and as you can see the top 10 list -given the DESIRED POSITIONS I set as criterion- is made of 1 LB,1 RB,3 CDM,1 LM,1 RM,3 ST (meaning that CB wasn't used).

So to conclude could you please make a little modification to your formula so that the results look like this screenshot below?
Screenshot_3.png
Only thing needed now is a little setting so that the DESIRED POSITIONS are used 100% same in the top 10 table, both the values and their quantities as showed in the screenshot. (of course I don't mind for helper columns or for the order of positions to be the same).
Thanks a lot in advance!!!

** bump **

Please fill in the picture to appear " ? "
That way I know what formula to use next to achieve your goal