Excel Exercise for School Based Job

jayu89

New Member
Joined
Jul 15, 2020
Messages
17
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Hi All,

Just joined the forum and the wealth of knowledge among the members, is extraordinary!
Which brings me onto a thread of my own to ask for some help:

I have been trying to apply for jobs in school as I worked in an Admin assistant and exams officer at a sixth form. However, the last 10 years I have been in retail management and now want to return back into the education sector. Anyway, over the lock down, I have applied for a number of roles and went as far as being shortlisted and progressed to interview. However, prior to the interview, the school sends out a few tasks to complete prior to interview and in almost all cases, there is an excel exercise. Recently, I did an exercise, but was so lost as to what functions to use, that I had to do the exercise manually and did not finish. After, I realised it was to do with vlookup and IF functions (or correct me if I'm wrong). Anyway, a few more applications that I had applied to, have got back to me yesterday and today for interview, and all 3 have said that there is an exercise, and I want to be ready if it is an excel exercise.

So in preparation, I was trying to do the excel task recently sent, it's a simple table with what the science department of a school entered for students and then another table beside, on the exam entries made to exam board. The exercise is:

On the excel spreadsheet there is an entry sheet received from the Science department asking you to confirm the pupils have been entered for the correct exam.


"As you will see, there are three possible tiers for this exam:

• Higher – Exam code – A217H.
• Intermediate – Exam code – A217I
• Foundation – Exam code – A217F

There is also a report printed from the Academy’s exam system confirming the actual entries sent to the exam board.

Please cross-check the two documents and identify whether pupils have been entered in accordance with the Science department’s wishes. Highlight in yellow any mistake or queries you might wish to discuss, and leave additional comments that explain what your queries are."


Here is a screenshot of the worksheet:

LH9CFf3.jpg


I attempted to do the exercise today. This is what I did:

1. In Column H, entered the following formula: =VLOOKUP(E4, examcodes,2,FALSE), examcodes is the small table in columns Q and R. My thinking was that this will help check whether the students were entered for the correct module. See below:

voKRu26.jpg


2. To check students name from Science dept list with exam board entries, I merged the first and last name to match the exam board entries name list, by using the following formula: =CONCATENATE(B4, " ",A4), in column I. See below:

BC7eOfi.jpg


3. In column J, I then applied the formula: =VLOOKUP(I4,$L$4:$L$40, 1, FALSE), to cross-check the names from Science dept list against exam board entries. This produced NA for non-matching records. See below:

xLn65jc.jpg


My questions are the following:
1. Is there a way of checking between the 2 tables in one go? And highlighting the records of name that do not appear in either table? For example, some records in exam entries table, use short hand names, like Jim instead of James, or Beth instead of Elizabeth.
2. How to check whether each student has been entered into the correct tier paper by name? I cannot figure this bit out and it is frustrating me, and feel like this is where the IF function comes into play.
3. Forgive me, but is there any better way to extract the data as required by the task?

I have not used excel in over 15 years and do not know where to start!
Thank you in advance ALL.

Jay
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,027
Office Version
  1. 365
Platform
  1. Windows
You can also use col H for conditional formatting to highlight the problem rows.

excelforum.xlsm
ABCDEFGHIJKLMNOPQ
1TEACHER'S ENTRY LISTEXAM BOARD ENTRIES
2
3NAMEDoBDoBSetModuleArrangementsForecast GradeNAMEDoBEntry
4AshtonDavid07/04/19949P1HigherA*OKDavid ASHTON7.4.94A217HFoundationA217F
5BatesZoe17/06/19949P1HigherAOKJames ASHWIN8.7.94A217HIntermediateA217I
6BradleyAndrew05/07/19949P1HigherCOKTabitha BAILEY19.11.93A217FHigherA217H
7ConwayEmma25/12/19939P1HigherBOKZoe BATES17.6.94A217H
8EdwardsMichael24/10/19939P1HigherBOKSophie BELL4.4.94A217I
9FordJoanna21/10/19939P1HigherCOKCharles BENNETT12.8.94A217I
10HallBeth27/08/19949P1HigherANot foundAndrew BRADLEY5.7.94A217H
11HillRichard26/10/19949P1HigherAOKJames CARTER18.12.93A217I
12JonesMelanie16/04/19949P1HigherCOKEmma CONWAY25.12.93A217H
13MarshallGraham13/06/19949P1HigherAErrorKathryn COOPER2.2.94A217I
14MurrayHannah02/10/19939P1HigherBOKNatasha DAVIS18.2.94A277F
15SaundersJames10/10/19939P1HigherA*OKMichael EDWARDS24.10.93A217H
16ThomasRobert11/01/19949P1HigherANot foundMichelle EVANS6.5.94A217F
17VickersLily24/09/19939P1HigherBOKJoanna FORD21.10.93A217H
18AshwinJames08/07/19949P2HigherCOKCraig GIBSON28.9.93A217I
19BellSophie04/04/19949P2IntermediateCOKWilliam GRIFFITHS6.2.94A217F
20CarterJim18/12/19939P2IntermediateDNot foundElizabeth HALL27.8.94A217H
21CooperKathryn02/02/19949P2Intermediate+25%DOKSamuel HARRIS16.3.94A217I
22EvansMichelle06/05/19949P2IntermediateCErrorAlexander HATCH14.3.94A217F
23GibsonCraig28/09/19939P2IntermediateCOKRichard HILL26.10.94A217H
24HarrisSam16/03/19949P2IntermediateDNot foundChristopher JACKSON25.7.94A217I
25LloydIan05/03/19949P2Intermediate+10%COKMelanie JONES16.4.94A217H
26MasonSamuel28/10/19939P2IntermediateCOKIan LLOYD5.3.94A217I
27OsborneGary07/04/19949P2IntermediateDNot foundGraham MARSHALL13.6.94A271H
28ScottLizzy07/08/19949P2IntermediateENot foundSamuel MASON28.10.93A217I
29TelforZack19/01/19949P2HigherCOKMurray MILLS23.1.94A217F
30WildingYvonne04/02/19949P2IntermediateCOKHannah MURRAY2.10.93A217H
31BaileyTabs19/11/19939P3FoundationReaderENot foundConnor PHILLIPS10.5.94A217F
32BennettChas12/08/19949P3FoundationFNot foundRichard ROBERTS15.8.94A217F
33DavisNatasha18/02/19949P3FoundationFErrorJames SAUNDERS10.10.93A217H
34FieldLaura28/03/19949P3FoundationDNot foundElizabeth SCOTT7.8.94A217I
35HaynesWilliam06/02/19949P3FoundationDNot foundSimon SMITH2.2.94A217I
36HatchAlexander14/03/19949P3Foundation+10%FOKFiona TAYLOR30.3.94A217H
37JacksonChristopher25/07/19949P3IntermediateDOKZack TELFOR19.1.94A217H
38MillsMurray23/01/19949P3FoundationFOKLily VICKERS24.9.93A217H
39PhillipsConnor10/05/19949P3FoundationReaderGOKYvonne WILDING4.2.94A217I
40RobertsRich15/08/19949P3Foundation+25%ENot foundTheresa WOODS30.9.94A217F
41AndersonSimon02/02/19949P3IntermediateDNot found
42TaylorFiona30/03/19949P3FoundationDError
43WoodsTerry30/09/19949P3Foundation+25%ENot found
Task
Cell Formulas
RangeFormula
H4:H43H4=IFERROR(IF(INDEX($M$4:$M$40,MATCH(B4&" "&A4,$K$4:$K$40,0))=INDEX($Q$4:$Q$6,MATCH(E4,$P$4:$P$6,0)),"OK", "Error"),"Not found")
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

jayu89

New Member
Joined
Jul 15, 2020
Messages
17
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Ok, I forgot to mention the formula in post#2 needs to be confirmed with Ctrl Shift Enter, not just Enter, then copied down.
Never mind, I figured out at which point I should enter the above formula, it returned "OK"
You can also use col H for conditional formatting to highlight the problem rows.

excelforum.xlsm
ABCDEFGHIJKLMNOPQ
1TEACHER'S ENTRY LISTEXAM BOARD ENTRIES
2
3NAMEDoBDoBSetModuleArrangementsForecast GradeNAMEDoBEntry
4AshtonDavid07/04/19949P1HigherA*OKDavid ASHTON7.4.94A217HFoundationA217F
5BatesZoe17/06/19949P1HigherAOKJames ASHWIN8.7.94A217HIntermediateA217I
6BradleyAndrew05/07/19949P1HigherCOKTabitha BAILEY19.11.93A217FHigherA217H
7ConwayEmma25/12/19939P1HigherBOKZoe BATES17.6.94A217H
8EdwardsMichael24/10/19939P1HigherBOKSophie BELL4.4.94A217I
9FordJoanna21/10/19939P1HigherCOKCharles BENNETT12.8.94A217I
10HallBeth27/08/19949P1HigherANot foundAndrew BRADLEY5.7.94A217H
11HillRichard26/10/19949P1HigherAOKJames CARTER18.12.93A217I
12JonesMelanie16/04/19949P1HigherCOKEmma CONWAY25.12.93A217H
13MarshallGraham13/06/19949P1HigherAErrorKathryn COOPER2.2.94A217I
14MurrayHannah02/10/19939P1HigherBOKNatasha DAVIS18.2.94A277F
15SaundersJames10/10/19939P1HigherA*OKMichael EDWARDS24.10.93A217H
16ThomasRobert11/01/19949P1HigherANot foundMichelle EVANS6.5.94A217F
17VickersLily24/09/19939P1HigherBOKJoanna FORD21.10.93A217H
18AshwinJames08/07/19949P2HigherCOKCraig GIBSON28.9.93A217I
19BellSophie04/04/19949P2IntermediateCOKWilliam GRIFFITHS6.2.94A217F
20CarterJim18/12/19939P2IntermediateDNot foundElizabeth HALL27.8.94A217H
21CooperKathryn02/02/19949P2Intermediate+25%DOKSamuel HARRIS16.3.94A217I
22EvansMichelle06/05/19949P2IntermediateCErrorAlexander HATCH14.3.94A217F
23GibsonCraig28/09/19939P2IntermediateCOKRichard HILL26.10.94A217H
24HarrisSam16/03/19949P2IntermediateDNot foundChristopher JACKSON25.7.94A217I
25LloydIan05/03/19949P2Intermediate+10%COKMelanie JONES16.4.94A217H
26MasonSamuel28/10/19939P2IntermediateCOKIan LLOYD5.3.94A217I
27OsborneGary07/04/19949P2IntermediateDNot foundGraham MARSHALL13.6.94A271H
28ScottLizzy07/08/19949P2IntermediateENot foundSamuel MASON28.10.93A217I
29TelforZack19/01/19949P2HigherCOKMurray MILLS23.1.94A217F
30WildingYvonne04/02/19949P2IntermediateCOKHannah MURRAY2.10.93A217H
31BaileyTabs19/11/19939P3FoundationReaderENot foundConnor PHILLIPS10.5.94A217F
32BennettChas12/08/19949P3FoundationFNot foundRichard ROBERTS15.8.94A217F
33DavisNatasha18/02/19949P3FoundationFErrorJames SAUNDERS10.10.93A217H
34FieldLaura28/03/19949P3FoundationDNot foundElizabeth SCOTT7.8.94A217I
35HaynesWilliam06/02/19949P3FoundationDNot foundSimon SMITH2.2.94A217I
36HatchAlexander14/03/19949P3Foundation+10%FOKFiona TAYLOR30.3.94A217H
37JacksonChristopher25/07/19949P3IntermediateDOKZack TELFOR19.1.94A217H
38MillsMurray23/01/19949P3FoundationFOKLily VICKERS24.9.93A217H
39PhillipsConnor10/05/19949P3FoundationReaderGOKYvonne WILDING4.2.94A217I
40RobertsRich15/08/19949P3Foundation+25%ENot foundTheresa WOODS30.9.94A217F
41AndersonSimon02/02/19949P3IntermediateDNot found
42TaylorFiona30/03/19949P3FoundationDError
43WoodsTerry30/09/19949P3Foundation+25%ENot found
Task
Cell Formulas
RangeFormula
H4:H43H4=IFERROR(IF(INDEX($M$4:$M$40,MATCH(B4&" "&A4,$K$4:$K$40,0))=INDEX($Q$4:$Q$6,MATCH(E4,$P$4:$P$6,0)),"OK", "Error"),"Not found")
Press CTRL+SHIFT+ENTER to enter array formulas.

Thanks Fluff - it worked perfectly!!!!!
How do I go about doing the conditional formatting? I vaguely remember I have to copy and paste the formula but unsure of how to select what color for what probem.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,027
Office Version
  1. 365
Platform
  1. Windows
Oops, forgot to include the CF rules
excelforum.xlsm
ABCDEFGHIJKLMNOPQ
1TEACHER'S ENTRY LISTEXAM BOARD ENTRIES
2
3NAMEDoBDoBSetModuleArrangementsForecast GradeNAMEDoBEntry
4AshtonDavid07/04/19949P1HigherA*OKDavid ASHTON7.4.94A217HFoundationA217F
5BatesZoe17/06/19949P1HigherAOKJames ASHWIN8.7.94A217HIntermediateA217I
6BradleyAndrew05/07/19949P1HigherCOKTabitha BAILEY19.11.93A217FHigherA217H
7ConwayEmma25/12/19939P1HigherBOKZoe BATES17.6.94A217H
8EdwardsMichael24/10/19939P1HigherBOKSophie BELL4.4.94A217I
9FordJoanna21/10/19939P1HigherCOKCharles BENNETT12.8.94A217I
10HallBeth27/08/19949P1HigherANot foundAndrew BRADLEY5.7.94A217H
11HillRichard26/10/19949P1HigherAOKJames CARTER18.12.93A217I
12JonesMelanie16/04/19949P1HigherCOKEmma CONWAY25.12.93A217H
13MarshallGraham13/06/19949P1HigherAErrorKathryn COOPER2.2.94A217I
14MurrayHannah02/10/19939P1HigherBOKNatasha DAVIS18.2.94A277F
15SaundersJames10/10/19939P1HigherA*OKMichael EDWARDS24.10.93A217H
16ThomasRobert11/01/19949P1HigherANot foundMichelle EVANS6.5.94A217F
17VickersLily24/09/19939P1HigherBOKJoanna FORD21.10.93A217H
18AshwinJames08/07/19949P2HigherCOKCraig GIBSON28.9.93A217I
19BellSophie04/04/19949P2IntermediateCOKWilliam GRIFFITHS6.2.94A217F
20CarterJim18/12/19939P2IntermediateDNot foundElizabeth HALL27.8.94A217H
21CooperKathryn02/02/19949P2Intermediate+25%DOKSamuel HARRIS16.3.94A217I
22EvansMichelle06/05/19949P2IntermediateCErrorAlexander HATCH14.3.94A217F
23GibsonCraig28/09/19939P2IntermediateCOKRichard HILL26.10.94A217H
24HarrisSam16/03/19949P2IntermediateDNot foundChristopher JACKSON25.7.94A217I
25LloydIan05/03/19949P2Intermediate+10%COKMelanie JONES16.4.94A217H
26MasonSamuel28/10/19939P2IntermediateCOKIan LLOYD5.3.94A217I
27OsborneGary07/04/19949P2IntermediateDNot foundGraham MARSHALL13.6.94A271H
28ScottLizzy07/08/19949P2IntermediateENot foundSamuel MASON28.10.93A217I
29TelforZack19/01/19949P2HigherCOKMurray MILLS23.1.94A217F
30WildingYvonne04/02/19949P2IntermediateCOKHannah MURRAY2.10.93A217H
31BaileyTabs19/11/19939P3FoundationReaderENot foundConnor PHILLIPS10.5.94A217F
32BennettChas12/08/19949P3FoundationFNot foundRichard ROBERTS15.8.94A217F
33DavisNatasha18/02/19949P3FoundationFErrorJames SAUNDERS10.10.93A217H
34FieldLaura28/03/19949P3FoundationDNot foundElizabeth SCOTT7.8.94A217I
35HaynesWilliam06/02/19949P3FoundationDNot foundSimon SMITH2.2.94A217I
36HatchAlexander14/03/19949P3Foundation+10%FOKFiona TAYLOR30.3.94A217H
37JacksonChristopher25/07/19949P3IntermediateDOKZack TELFOR19.1.94A217H
38MillsMurray23/01/19949P3FoundationFOKLily VICKERS24.9.93A217H
39PhillipsConnor10/05/19949P3FoundationReaderGOKYvonne WILDING4.2.94A217I
40RobertsRich15/08/19949P3Foundation+25%ENot foundTheresa WOODS30.9.94A217F
41AndersonSimon02/02/19949P3IntermediateDNot found
42TaylorFiona30/03/19949P3FoundationDError
43WoodsTerry30/09/19949P3Foundation+25%ENot found
Task
Cell Formulas
RangeFormula
H4:H43H4=IFERROR(IF(INDEX($M$4:$M$40,MATCH(B4&" "&A4,$K$4:$K$40,0))=INDEX($Q$4:$Q$6,MATCH(E4,$P$4:$P$6,0)),"OK", "Error"),"Not found")
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A4:G43Expression=$H4="Not found"textNO
A4:G43Expression=$H4="Error"textNO


Select A4:H43, then in conditional format select new rule, then use a formula & enter the above formula, select the format you want & OK
 

jayu89

New Member
Joined
Jul 15, 2020
Messages
17
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Hey Fluff,

I tried to do the conditional formatting.
What am I doing wrong:

rhp7clC.jpg


velIxrs.jpg


Please help!!!

Thanks,
Jay
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,027
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The formulae should be as shown in post#13 under "Cells with Conditional Formatting"
 

jayu89

New Member
Joined
Jul 15, 2020
Messages
17
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
The formulae should be as shown in post#13 under "Cells with Conditional Formatting"

hey fluff
literally copying and pasting, but with no luck. Selecting a color and then pressing OK, but it is not highlighting anything.
Am I doing it wrong? :/
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,027
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Which formula are you copy/pasting?
 

jayu89

New Member
Joined
Jul 15, 2020
Messages
17
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Which formula are you copy/pasting?

=IFERROR(IF(INDEX($M$4:$M$40,MATCH(B4&" "&A4,$K$4:$K$40,0))=INDEX($Q$4:$Q$6,MATCH(E4,$P$4:$P$6,0)),"OK", "Error"),"Not found")

From post 13, under cell formula rule...
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,027
Office Version
  1. 365
Platform
  1. Windows
No it's the two formulae below that in the Conditional formatting section.
 

jayu89

New Member
Joined
Jul 15, 2020
Messages
17
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
No it's the two formulae below that in the Conditional formatting section.

Thank you Fluff, worked now. Thank you!!!
 
Last edited by a moderator:

Watch MrExcel Video

Forum statistics

Threads
1,122,752
Messages
5,597,921
Members
414,190
Latest member
PuzzlerUK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top