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.

[TABLE="width: 272"]
<tbody>[TR]
[TD]Name[/TD]
[TD]week 1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]week 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]week 3[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Anna[/TD]
[TD="align: right"]21[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]25[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD="align: right"]18[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]20[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]19[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD="align: right"]16[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]21[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]29[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]27[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]25[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Liz[/TD]
[TD="align: right"]19[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]22[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]21[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD="align: right"]29[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]25[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]27[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sandra[/TD]
[TD="align: right"]25[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]25[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]22[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Susan[/TD]
[TD="align: right"]20[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]23[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]19[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD="align: right"]24[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]25[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]20[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Peter[/TD]
[TD="align: right"]29[/TD]
[TD] [/TD]
[TD]Anna[/TD]
[TD="align: right"]25[/TD]
[TD] [/TD]
[TD]Jill[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]John[/TD]
[TD="align: right"]27[/TD]
[TD] [/TD]
[TD]John[/TD]
[TD="align: right"]25[/TD]
[TD] [/TD]
[TD]Peter[/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Sandra[/TD]
[TD="align: right"]25[/TD]
[TD] [/TD]
[TD]Peter[/TD]
[TD="align: right"]25[/TD]
[TD] [/TD]
[TD]Anna[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD]Sandra[/TD]
[TD="align: right"]25[/TD]
[TD] [/TD]
[TD]John[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]
Thank you [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD]Tim[/TD]
[TD="align: right"]25[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]

 

[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%