Macro needed

[Avdberg]

Hi,
Would you please be so kind and assist me with a macro to extract specific data within a spreadsheet. The specs are. I have a list of students 30+ who rights exams every week and I want to extract the top 3 marks and names each week. Should there be more than 3 student with the same mark in the top 3 then I want to see all those students even if it's more that 3. Below is a example of how I did it manually.

Name	week 1			week 2			week 3
Anna	21			25			23
Carl	18			20			19
Jill	16			21			29
John	27			25			23
Liz	19			22			21
Peter	29			25			27
Sandra	25			25			22
Susan	20			23			19
Tim	24			25			20
	Peter	29		Anna	25		Jill	29
	John	27		John	25		Peter	27
	Sandra	25		Peter	25		Anna	23
				Sandra	25		John	23
Thank you				Tim	25

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col></colgroup>

 

[FormR]

a macro to extract specific data within a spreadsheet.

Hi, I wonder if you can be tempted with a formula based option.

The formulas in F2:G2 are copied down as far as might be required, the whole table in F:G is copied to I:J and L:M and only the week number needs changing to affect the updated results.

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	week 1	week 2	week 3		Week 1	3		Week 2	5		Week 3	4
2	Anna	21	25	23		Peter	29		Anna	25		Jill	29
3	Carl	18	20	19		John	27		John	25		Peter	27
4	Jill	16	21	29		Sandra	25		Peter	25		Anna	23
5	John	27	25	23					Sandra	25		John	23
6	Liz	19	22	21					Tim	25
7	Peter	29	25	27
8	Sandra	25	25	22
9	Susan	20	23	19
10	Tim	24	25	20
Sheet1

Cell Formulas
Range	Formula
G1	=COUNTIFS(INDEX($B$2:$D$50,0,MATCH(F1,$B$1:$D$1,0)),">="&LARGE(INDEX($B$2:$D$50,0,MATCH(F1,$B$1:$D$1,0)),3))
G2	=IF(ROWS(G$2:G2)>G$1,"",LARGE(INDEX($B$2:$D$50,0,MATCH(F$1,$B$1:$D$1,0)),ROWS(G$2:G2)))
F2	=IF(G2="","",INDEX($A$2:$A$50,AGGREGATE(15,6,(ROW($A$2:$A$50)-ROW($A$2)+1)/(INDEX($B$2:$D$50,0,MATCH(F$1,$B$1:$D$1,0))=G2),COUNTIFS(G$2:G2,G2))))

 

[Avdberg]

Thank you for your efforts, it's works 100%