Question about extracting data from a pivot - top 5 results

[MarkTheRed]

Hello everyone,

First time posting here, i am really hoping someone could help me with this, because i have no idea how to proceed further here.

I am trying to extract top 5 numbers from 2 categories in a pivot, i have named them grade 1 and grade 2 (please see screenshot attached, since i am unable to attach anything to the post). I am able to extract the top values using =large (if someone has a better idea i am open to suggestions).

My issue is, i simply cannot find a way to match the values to the names that go with them. The sort pivot options won't work because of the two categories.

Please pay in mind that i wish to avoid using any macros at all, so i am trying to find a solution via a simple formula. In the worst case, i can simply create 2 pivots but that's really not my goal here if i can avoid it.

Any help would be really appreciated in my learning curve in excel .

Thank you all in advance.

 

[jorismoerings]

Hi,

take a look at this:

Excel 2016 (Windows) 64 bit
	A	B	C	D	E	F
1	name	Type	Grade 1	Grade 2
2	Ana	1	72	51
3	Mark	1	34	66
4	Nicole	2	85	34
5	John	1	93	91
6	Marcus	2	85	32
7	Stephen	1	85	50
8	James	1	41	33
9	Jack	2	61	49
10	Phil	2	69	82
11	Sam	1	41	35
12	Jackie	2	62	86
13	Melisa	1	43	74
14	Johnson	2	88	36
15
16
17
18		Top 5	Grade 2 T1		Top 5	Grade 2 T2
19		John	91		Jackie	86
20		Melisa	74		Phil	82
21		Mark	66		Jack	49
22		Ana	51		Johnson	36
23		Stephen	50		Nicole	34
Sheet1

Cell Formulas
Range	Formula
B19	=INDEX($A$2:$A$14,MATCH(C19,$D$2:$D$14,0))
E19	=INDEX($A$2:$A$14,MATCH(F19,$D$2:$D$14,0))
C19	{=LARGE(IF($B$2:$B$14=1,$D$2:$D$14),ROW()-18)}
F19	{=LARGE(IF($B$2:$B$14=2,$D$2:$D$14),ROW()-18)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[MarkTheRed]

Hi,

take a look at this:

Excel 2016 (Windows) 64 bit

	A	B	C	D	E	F
1	name	Type	Grade 1	Grade 2
2	Ana	1	72	51
3	Mark	1	34	66
4	Nicole	2	85	34
5	John	1	93	91
6	Marcus	2	85	32
7	Stephen	1	85	50
8	James	1	41	33
9	Jack	2	61	49
10	Phil	2	69	82
11	Sam	1	41	35
12	Jackie	2	62	86
13	Melisa	1	43	74
14	Johnson	2	88	36
15
16
17
18		Top 5	Grade 2 T1		Top 5	Grade 2 T2
19		John	91		Jackie	86
20		Melisa	74		Phil	82
21		Mark	66		Jack	49
22		Ana	51		Johnson	36
23		Stephen	50		Nicole	34

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B19 =INDEX($A$2:$A$14,MATCH(C19,$D$2:$D$14,0)) E19 =INDEX($A$2:$A$14,MATCH(F19,$D$2:$D$14,0)) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula C19 {=LARGE(IF($B$2:$B$14=1,$D$2:$D$14),ROW()-18)} F19 {=LARGE(IF($B$2:$B$14=2,$D$2:$D$14),ROW()-18)} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Hello,

This is a marvelous solution, and thank you for that, although i am not sure it would work for me. From the looks of it, this one does not have any pivots, and the pivots are essential. Let me explain: The real data that i work with contains a lot, lot more data, and they are divided into weeks and months. The pivots are there to ensure that, for reporting purposes, we can filter the week/month that we need, and i need the formulas to match everything once the pivots are set, because they are originally intended to be used by people that have very little to no knowledge of Excel.
Apologies for omitting the week/month part, i did not think it through in detail at the time of writing the post. Also, if it means anything to you, i am using Excel 2010.

Thanks in advance, Filip

 

[MarkTheRed]

Hello,

This is a marvelous solution, and thank you for that, although i am not sure it would work for me. From the looks of it, this one does not have any pivots, and the pivots are essential. Let me explain: The real data that i work with contains a lot, lot more data, and they are divided into weeks and months. The pivots are there to ensure that, for reporting purposes, we can filter the week/month that we need, and i need the formulas to match everything once the pivots are set, because they are originally intended to be used by people that have very little to no knowledge of Excel.
Apologies for omitting the week/month part, i did not think it through in detail at the time of writing the post. Also, if it means anything to you, i am using Excel 2010.

Thanks in advance, Filip

After testing out a bit i see the lack of my knowledge, index match seems to be working for me, once again thank you very much for taking the time to help me out.

Kind regards, Filip