Returning Grade based on Class Code

[antinora]

Greetings:

I need to return a student's report card number grade for any class code which begins with letter "P".

For example, Zaref received a 90 for his period 4 class beginning with PQS.

Ultimately, I need a list of students whose "P" (phys ed) class grade is < = 55. Some students may have more than 1 "P" class.

Thanks,

Jon

This is the data:

First

Official

Grade

Period 1

Grade1

Grade2

Period 3

Period 4

Grade4

Grade5

Period 6

Period 7

Grade7

Grade8

Period 9

Period 10

Grade10

AMJAD

2LZ

12

SWS11QBW 01

50

PGS74 02

NS

ZL 01

nil

SDS11QPW 01

40

ESS22QQR 03

55

ESS22QQW 04

40

ZM2012 01

nil

MALEKE

4C4

12

EES88QQJ 01

85

EJS22 01

70

HLS22TJ 01

70

ZL 02

nil

MQS11QF 02

55

HES11QQJ 03

70

PPS74 11

90

ZAREEF

5Q3

11

MGS22QQE 01

40

RVS64 01

65

PQS74Q5B 03

90

SDS22QEE 01

55

EES84QQE 02

85

ZL 04

nil

HGS42QQE 03

75

ANA

4L4

12

SCS22 01

75

PPS74Q4X 05

85

SCS22QL 01

P

PHS11QQL 01

70

ZL 02

nil

ESS22QQR 03

90

ESS22QQW 04

85

ASHELY

7F1

9

EES82QCB 01

98

MGS22Q9B 01

80

HUS22Q9B 01

85

EWS22QCW 02

80

HQS11Q9F 02

90

ZLA 03

nil

SLS22Q9T 02

80

CHRISTOPHER

7L1

9

ESS84QQ1 01

55

ESS84QQ2 01

55

ZL 01

nil

AGS22QQL 01

55

MES42QCL 02

55

HGS42QQL 04

40

PGS72 07

40

EMELY

5S3

11

FSS44QQL 01

59

EES86QQB 02

85

PPS74 05

85

SLS22QLJ 02

90

SLS22QQJ 02

F

BAS11T 01

59

HGS44QQJ 03

70

JAVIER

4D4

12

HES11QQE 01

70

ELS22QHS 01

59

SHS66T 01

55

EES88QQC 02

65

PHS11 04

65

MRS22QQC 02

55

SPS22QL 02

F

MARLEN

5Q3

11

PQS74Q5V 01

80

HQS11QAH 01

55

MGS31QQT 02

55

FSS22XQL 01

85

HGS44QQE 02

65

ZL 04

nil

EES86QQT 03

75

NATALIE

5C3

11

HGS44QQJ 01

55

FSS62 15

95

SES22QLB 01

F

SES22QQB 01

59

ZL 02

nil

PQS74Q5V 05

80

HLS44T 02

80

OSCAR

5Q3

11

EES86QQB 01

80

UNS61 01

94

PGS74Q4X 10

85

SCS22QL 04

nil

SCS22 04

55

HGS44QQE 02

85

MRS22QQC 02

70

ABDULAZIZ

4LR

9

SLS42QLL 01

P

SLS42QQL 01

65

HGS42QQL 01

65

HSS42QQL 01

65

ZL 02

nil

MES22QQL 01

55

PGS74 06

55

JONATHAN

4C2

10

HUS22QQT 01

65

EES86QQB 02

70

ZL 01

nil

PQS74Q5B 04

65

FSS62 07

65

SLS22QLJ 03

65

SLS22QQJ 03

P

JOSHUA

6P2

10

EES84QQM 01

55

HGS42QQM 03

80

TWS22QQM 01

40

SLS22QLM 02

P

SLS22QQM 02

75

MES44QQM 03

55

PQS74Q5B 05

40

RUSKIN

6J2

10

MGS22QQE 01

65

EES84QQE 01

85

AYS22QD 01

70

PQS74Q5V 04

95

SES22QLE 02

F

SES22QQE 02

59

ZL 04

nil

GANESH

7F1

9

MES22QCB 01

55

EWS22QCW 01

70

SLS22Q9B 02

55

PPS72Q4A 06

90

SLS22QL9 05

F

TWS11Q9 04

70

HUS22Q9B 03

50

CARLOS

3J4

12

ELS22QHS 01

59

ZL 01

nil

MGS44QQE 01

65

PGS74 05

40

HQS11QAH 03

NS

ZM2013 01

nil

ZMFOR 01

nil

MANUEL

6J2

10

MGS22QQE 01

59

PGS74 02

65

FSS42QQL 02

80

HGS42QQE 02

75

EES84QQE 02

75

ZL 04

nil

RVS62 02

75

PAOLA

5Q3

11

EES86QQB 01

95

PPS74Q4X 06

75

SCS22QL 03

P

SCS22 03

85

MRS22QQE 02

75

UNS61 03

93

HUS22QQB 02

90

BAYRON

7F1

9

HUS22Q9D 01

55

MES22QCD 02

55

PPS72Q4A 04

70

SLS22QL9 08

F

SLS22Q9D 01

55

ZLA 02

nil

EWS22QCW 04

70

RAFAEL

7W1

9

TWS11Q9 01

55

EES82QCS 02

65

HUS22Q9S 01

55

MES22QCS 01

65

EWS22QCW 06

59

ZLA 03

nil

SLS22Q9S 02

55

MATTHEW

5R2

10

SDS22QES 01

55

FSS62 03

40

EES86QQS 01

80

ZL 02

nil

HGS44QQS 02

85

PQS74Q5B 05

90

MQS11QF 03

55

MISBAH

6L2

10

PPS74 01

90

MGS22QQL 01

75

ZL 01

nil

HGS42QQL 02

80

SLS44QEL 02

75

SLS44QLL 02

P

HSS42QQL 02

90

NICOLE

6J2

10

HGS42QQC 01

95

FSS44QQI 01

90

PQS74Q5V 03

100

EES84QQC 01

85

AES21 03

100

ZL 04

nil

MGS22QQC 02

70

CARLOS

6M2

10

ESS42QBL 01

55

PPS74 02

55

HGS42QQF 02

85

SLS42QLF 02

P

SLS42QQF 02

75

MES44QQF 01

59

FSS42QQI 01

85

<colgroup><col width="133" style="width: 100pt; mso-width-source: userset; mso-width-alt: 4864;"> <col width="52" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1901;"> <col width="44" style="width: 33pt; mso-width-source: userset; mso-width-alt: 1609;"> <col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="51" style="width: 38pt; mso-width-source: userset; mso-width-alt: 1865;"> <col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="58" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2121;"> <col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="51" style="width: 38pt; mso-width-source: userset; mso-width-alt: 1865;"> <col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="58" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2121;"> <col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="51" style="width: 38pt; mso-width-source: userset; mso-width-alt: 1865;"> <col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="58" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2121;"> <col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="58" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2121;"> <tbody> </tbody>

 

[marious]

Use helper columns. I build one for you. Modify ranges accordingly for the rest of the columns.


<html><head><title>Excel Jeanie HTML</title></head><body>
<!-- ######### Start Created Html Code To Copy ########## -->

Excel Workbook
	A	B	C	D	E	F
1	First	Official	Grade	Period 1	Grade1	P1Score
2	AMJAD	2LZ	12	SWS11QBW 01	50
3	MALEKE	4C4	12	EES88QQJ 01	85
4	ZAREEF	5Q3	11	MGS22QQE 01	40
5	ANA	4L4	12	SCS22 01	75
6	ASHELY	7F1	9	EES82QCB 01	98
7	CHRISTOPHER	7L1	9	ESS84QQ1 01	55
8	EMELY	5S3	11	FSS44QQL 01	59
9	JAVIER	4D4	12	HES11QQE 01	70
10	MARLEN	5Q3	11	PQS74Q5V 01	80	80
11	NATALIE	5C3	11	HGS44QQJ 01	55
12	OSCAR	5Q3	11	EES86QQB 01	80
13	ABDULAZIZ	4LR	9	SLS42QLL 01	P
14	JONATHAN	4C2	10	HUS22QQT 01	65
15	JOSHUA	6P2	10	EES84QQM 01	55
16	RUSKIN	6J2	10	MGS22QQE 01	65
17	GANESH	7F1	9	MES22QCB 01	55
18	CARLOS	3J4	12	ELS22QHS 01	59
19	MANUEL	6J2	10	MGS22QQE 01	59
20	PAOLA	5Q3	11	EES86QQB 01	95
21	BAYRON	7F1	9	HUS22Q9D 01	55
22	RAFAEL	7W1	9	TWS11Q9 01	55
23	MATTHEW	5R2	10	SDS22QES 01	55
24	MISBAH	6L2	10	PPS74 01	90	90
25	NICOLE	6J2	10	HGS42QQC 01	95
26	CARLOS	6M2	10	ESS42QBL 01	55
Sheet3

?
?
<!-- ######### End Created Html Code To Copy ########## -->
</body></html>