Make a list of recurring names in different sheets

[edge37]

Hello again
I'd like to ask for your kind help on the following: I have a Gradebook in Excel. It is divided in 4 Periods, each in a different sheet (in the sample attached, there is the 1st Period in Sheet 1, 2nd Period would be in Sheet 2... and so on). Each period of the class includes a table that says EXEMPTION CANDIDATES. I use formulas to extract those students that have more than 95% in their grades.

These tables are in all 4 periods (meaning: 4 different sheets), but in period 4 I need also a new table (let's say in cells G4 and down) that checks all these exemption tables and can make a list of all students that appeared in all four tables of each period with at least 95%, so they could opt for an exam exemption. With the provided worksheet, the cell range I’m looking to analyze is the same throughout the whole workbook - E4:E10

For example, if the student Giuliana Melgar (student #9) appears in all 4 tables having a grade more than 95%, then she will be exempted from the exam and her name would appear in the new list. If Juan Carlos Gamero (student #13) appeared only three or less times in the tables then he can't be exempted and his name will not appear in thelist.

Can you help me, please?

Thanks

PRUEBA2.xlsm
	A	B	C	D	E	F
1	1st PERIOD		GRADE
2	#	STUDENT	100%
3	1	Alyson Paola Gómez Laínez	80		EXEMPTION CANDIDATES
4	2	Any Camila Maltez Ubeda	74		Ariela Pacheco Rosales	97
5	3	Ariela Pacheco Rosales	97		Giuliana Melgar Castro	95
6	4	Bei-lin Arauz Estrada	62		Juan Carlos Gamero Jensen	100
7	5	Danely Mariela Gutierrez López	64
8	6	Evelyn Rubí Castro Martínez	5
9	7	Fernanda Gisele Aguilar Caballero	53
10	8	Génesis Alejandra Irula Rodríguez	58
11	9	Giuliana Melgar Castro	95
12	10	Gloria Marcela Canales Cañadas	70
13	11	Grace Marie Avila Guzmán	54
14	12	Hector Arturo García Alvarado	87
15	13	Juan Carlos Gamero Jensen	100
SHEET 1

Cell Formulas
Range	Formula
E4:E10	E4	=IFERROR(INDEX(B:B,AGGREGATE(15,3,ROW(B$3:B$15)/($C$3:$C$15>=95),ROWS(E$13:E13))),"")
F4:F10	F4	=IFERROR(INDEX(C:C,AGGREGATE(15,7,ROW(C$3:C$15)/($C$3:$C$15>=95),ROWS(D$4:D4))),"")

 

[Eric W]

With Excel 2021, you can use FILTER instead of the AGGREGATE structure you have now for your E4 formula. And the new G4 formula can also use FILTER. With only 4 sheets to consider, it's not too onerous to just list the sheets in the formula:

Book2
	A	B	C	D	E	F	G
1	4th PERIOD		GRADE
2	#	STUDENT	1
3	1	Alyson Paola Gómez Laínez	80		EXEMPTION CANDIDATES		EXEMPTION LIST
4	2	Any Camila Maltez Ubeda	74		Ariela Pacheco Rosales	97	Juan Carlos Gamero Jensen
5	3	Ariela Pacheco Rosales	97		Giuliana Melgar Castro	95
6	4	Bei-lin Arauz Estrada	62		Juan Carlos Gamero Jensen	100
7	5	Danely Mariela Gutierrez López	64
8	6	Evelyn Rubí Castro Martínez	5
9	7	Fernanda Gisele Aguilar Caballero	53
10	8	Génesis Alejandra Irula Rodríguez	58
11	9	Giuliana Melgar Castro	95
12	10	Gloria Marcela Canales Cañadas	70
13	11	Grace Marie Avila Guzmán	54
14	12	Hector Arturo García Alvarado	87
15	13	Juan Carlos Gamero Jensen	100
Sheet4

Cell Formulas
Range	Formula
E4:F6	E4	=FILTER(B3:C50,C3:C50>=95,"")
G4	G4	=LET(a,INDEX(E4#,0,1),FILTER(a,COUNTIF(Sheet1!E4:E10,a)*COUNTIF(Sheet2!E4:E10,a)*COUNTIF(Sheet3!E4:E10,a),""))
Dynamic array formulas.

 

[edge37]

Thank you very much for your answer, I'm still battling to make it work on my (more extended workbook). I wonder if you could help me introduce those formulas correctly in my extended gradebook example that I'm sending you. I stripped all the unnecessay info from it (unfortunately there's no apparent way to send you the complete file with all 4 sheets), all the cell ranges are the same for each period (sheet), the names of the sheets are 10A (1), 10A (2), 10A (3), and 10A (4). I could send the whole file if it was possible.

I appreciate your help. Thank you

PRUEBA 3.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
2
3
4
5
6
7	period 4																	GRADE
8	#	STUDENTS	8	7	5	5	8	7	5				10	10	X	65%	35%	100%
9	1	Alyson Paola Gómez Laínez	9	7	5	4	4	4	7	5	0	0	8	10		63	17	80														EXEMPTION CANDIDATES			EXEMPTED FROM EXAM
10	2	Any Camila Maltez Ubeda	9	7	5	4	4	4	6	5	0	0	6	10		60	14	74														Giuliana Melgar Castro	95
11	3	Ariela Pacheco Rosales	9	7	5	4	4	4	7	5	0	0	8	10		63	17	80														Juan Carlos Gamero Jensen	100
12	4	Bei-lin Arauz Estrada	9	7	5	2	0	4	7	5	0	0	5	10		54	8	62
13	5	Danely Mariela Gutierrez López	0	7	4	3	4	4	7	5	0	0	4	8		46	18	64
14	6	Evelyn Rubí Castro Martínez	0	0	0	0	0	0	0	0	0	0	0	0
15	7	Fernanda Gisele Aguilar Caballero	9	5	5	0	4	4	7	0	0	0	3	9		46	7	53
16	8	Génesis Alejandra Irula Rodríguez	8	7	4	0	2	4	7	4	0	0	4	9		49	9	58
17	9	Giuliana Melgar Castro	9	7	5	4	4	4	7	5	0	0	10	10		65	30	95
18	10	Gloria Marcela Canales Cañadas	8	7	5	3	3	4	7	5	0	0	6	10		58	12	70
19	11	Grace Marie Avila Guzmán	8	7	4	2	0	4	7	0	0	0	7	8		47	7	54
20	12	Kiara Michell Luna Molina	4	7	4	4	0	4	7	0	0	0	4	10		44	13	57
21	13	Laura Balvin Ruiz	9	7	5	4	4	4	7	5	0	0	7	10		62	15	77
22	14	Maria Fernanda Avelar Rapalo	9	7	5	4	4	4	7	5	0	0	7	10		62	11	73
23	15	Allan Ramón Morales Erazo	9	0	5	2	2	4	7	5	0	0	4	8		46	7	53
24	16	Angel Eduardo Vásquez Orellana	9	7	5	4	4	4	7	5	0	0	10	10		65	20	85
25	17	Carlos Manuel Trochez Cruz	0	7	5	4	4	4	7	4	0	0	5	9		49	15	64
26	18	Daniel Eduardo García Aguiluz	8	7	4	4	2	4	7	5	0	0	9	10		60	13	73
27	19	Daniel Fernando Morel Paredes	9	7	4	4	4	4	7	5	0	0	10	9		63	20	83
28	20	Diego Fernando Banegas Pineda	9	7	5	4	4	4	7	5	0	0	6	10		61	17	78
29	21	Gabriel Isaac Meléndez Fernández	8	5	4	4	4	4	7	5	0	0	7	9		57	13	70
30	22	Hector Arturo García Alvarado	9	7	5	4	4	4	7	5	0	0	9	10		64	23	87
31	23	Juan Carlos Gamero Jensen	9	7	5	4	4	4	7	5	0	0	10	10		65	35	100
32	24	Kenneth Said Ascencio Rodriguez	8	7	5	3	0	4	7	5	0	0	6	9		54	7	61
33	25	Kevin Eduardo Miranda Baca	3	7	4	2	0	4	7	0	0	0	2	9		38	8	46
34	26	Marco Antonio Guzman Sabillon	9	7	5	4	4	4	7	5	0	0	9	10		64	8	72
35	27	Nelson Omar Mejia Alvarado	8	7	5	4	4	4	7	0	0	0	9	9		57	23	80
36	28	Richard Alest Caceres Mejia	9	7	5	4	4	4	7	5	0	0	7	10		62	12	74
37	29	Wilmer David García Alcántara	9	7	5	4	0	4	5	0	0	0	7	9		50	21	71
38
10A (4)

Cell Formulas
Range	Formula
AF10:AF16	AF10	=IFERROR(INDEX(B:B,AGGREGATE(15,3,ROW(B$9:B$38)/($R$9:$R$38>=95),ROWS(AF$19:AF19))),"")
AG10:AG16	AG10	=IFERROR(INDEX(R:R,AGGREGATE(15,7,ROW(R$9:R$37)/($R$9:$R$37>=95),ROWS(AE$10:AE10))),"")

 

[edge37]

https://drive.google.com/file/d/1K35QsLjy2MAuRJnPKrFqVFBD1LZMxbkt/view?usp=drive_link

Sending you a more complete workbook with my Bradebook, only for 10A class, if you can use it too. Thanks again

 

[Eric W]

I can't download files, but I took your sheet from post 3 and copied it 4 times, and I changed a few grades for testing. Here's what I came up with:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R													AE	AF	AG	AH	AI
1
2
3
4
5
6	period 4																	GRADE
7	#	STUDENTS	8	7	5	5	8	7	5				10	10	X	0.65	0.35	1
8	1	Alyson Paola Gómez Laínez	9	7	5	4	4	4	7	5	0	0	8	10		63	17	66														EXEMPTION CANDIDATES			EXEMPTED FROM EXAM
9	2	Any Camila Maltez Ubeda	9	7	5	4	4	4	6	5	0	0	6	10		60	14	74														Giuliana Melgar Castro	95		Giuliana Melgar Castro
10	3	Ariela Pacheco Rosales	9	7	5	4	4	4	7	5	0	0	8	10		63	17	80														Juan Carlos Gamero Jensen	100		Juan Carlos Gamero Jensen
11	4	Bei-lin Arauz Estrada	9	7	5	2	0	4	7	5	0	0	5	10		54	8	62														Wilmer David García Alcántara	97
12	5	Danely Mariela Gutierrez López	0	7	4	3	4	4	7	5	0	0	4	8		46	18	64
13	6	Evelyn Rubí Castro Martínez	0	0	0	0	0	0	0	0	0	0	0	0		0		0
14	7	Fernanda Gisele Aguilar Caballero	9	5	5	0	4	4	7	0	0	0	3	9		46	7	53
15	8	Génesis Alejandra Irula Rodríguez	8	7	4	0	2	4	7	4	0	0	4	9		49	9	58
16	9	Giuliana Melgar Castro	9	7	5	4	4	4	7	5	0	0	10	10		65	30	95
17	10	Gloria Marcela Canales Cañadas	8	7	5	3	3	4	7	5	0	0	6	10		58	12	70
18	11	Grace Marie Avila Guzmán	8	7	4	2	0	4	7	0	0	0	7	8		47	7	14
19	12	Kiara Michell Luna Molina	4	7	4	4	0	4	7	0	0	0	4	10		44	13	57
20	13	Laura Balvin Ruiz	9	7	5	4	4	4	7	5	0	0	7	10		62	15	77
21	14	Maria Fernanda Avelar Rapalo	9	7	5	4	4	4	7	5	0	0	7	10		62	11	73
22	15	Allan Ramón Morales Erazo	9	0	5	2	2	4	7	5	0	0	4	8		46	7	53
23	16	Angel Eduardo Vásquez Orellana	9	7	5	4	4	4	7	5	0	0	10	10		65	20	85
24	17	Carlos Manuel Trochez Cruz	0	7	5	4	4	4	7	4	0	0	5	9		49	15	64
25	18	Daniel Eduardo García Aguiluz	8	7	4	4	2	4	7	5	0	0	9	10		60	13	73
26	19	Daniel Fernando Morel Paredes	9	7	4	4	4	4	7	5	0	0	10	9		63	20	88
27	20	Diego Fernando Banegas Pineda	9	7	5	4	4	4	7	5	0	0	6	10		61	17	78
28	21	Gabriel Isaac Meléndez Fernández	8	5	4	4	4	4	7	5	0	0	7	9		57	13	70
29	22	Hector Arturo García Alvarado	9	7	5	4	4	4	7	5	0	0	9	10		64	23	87
30	23	Juan Carlos Gamero Jensen	9	7	5	4	4	4	7	5	0	0	10	10		65	35	100
31	24	Kenneth Said Ascencio Rodriguez	8	7	5	3	0	4	7	5	0	0	6	9		54	7	61
32	25	Kevin Eduardo Miranda Baca	3	7	4	2	0	4	7	0	0	0	2	9		38	8	46
33	26	Marco Antonio Guzman Sabillon	9	7	5	4	4	4	7	5	0	0	9	10		64	8	72
34	27	Nelson Omar Mejia Alvarado	8	7	5	4	4	4	7	0	0	0	9	9		57	23	80
35	28	Richard Alest Caceres Mejia	9	7	5	4	4	4	7	5	0	0	7	10		62	12	74
36	29	Wilmer David García Alcántara	9	7	5	4	0	4	5	0	0	0	7	9		50	21	97
37
10A (4)

Cell Formulas
Range	Formula
AF9:AG11	AF9	=FILTER(CHOOSE({1,2},B8:B50,R8:R50),R8:R50>=95,"")
AI9:AI10	AI9	=LET(a,INDEX(AF9#,0,1),FILTER(a,COUNTIF('10A (1)'!AF8:AF20,a)*COUNTIF('10A (2)'!AF8:AF20,a)*COUNTIF('10A (3)'!AF8:AF20,a),""))
Dynamic array formulas.

I had to tweak the AF9 formula a bit to account for the name and grade columns not being adjacent, but it's still pretty basic. The AI9 formula is almost unchanged, except for the sheet name changes and the range changes. I have another version of it where you list the sheet names in some adjacent cells, and the formula uses that instead of hardcoding the sheet names in the formula. The formula is slightly shorter, but more complicated.

Hope this helps!

 

[edge37]

Hi Eric W, I finally made it work, thank you very much for your help. Now I have a cool gradebook thanks to you.

 

[Eric W]

Glad it works for you!