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,349
Office Version
  1. 365
Platform
  1. Windows
You could also add this to col I, to find near matches. It looks at the surname & dob to see if there is a match

jayu89.xlsm
ABCDEFGHIJKLM
1TEACHER'S ENTRY LISTEXAM BOARD ENTRIES
2
3NAMEDoBDoBSetModuleArrangementsForecast GradeNAMEDoBEntry
4AshtonDavid07/04/19949P1HigherA*OK David ASHTON7.4.94A217H
5BatesZoe17/06/19949P1HigherAOK James ASHWIN8.7.94A217H
6BradleyAndrew05/07/19949P1HigherCOK Tabitha BAILEY19.11.93A217F
7ConwayEmma25/12/19939P1HigherBOK Zoe BATES17.6.94A217H
8EdwardsMichael24/10/19939P1HigherBOK Sophie BELL4.4.94A217I
9FordJoanna21/10/19939P1HigherCOK Charles BENNETT12.8.94A217I
10HallBeth27/08/19949P1HigherANot foundElizabeth HALLAndrew BRADLEY5.7.94A217H
11HillRichard26/10/19949P1HigherAOK James CARTER18.12.93A217I
12JonesMelanie16/04/19949P1HigherCOK Emma CONWAY25.12.93A217H
13MarshallGraham13/06/19949P1HigherAError Kathryn COOPER2.2.94A217I
14MurrayHannah02/10/19939P1HigherBOK Natasha DAVIS18.2.94A277F
15SaundersJames10/10/19939P1HigherA*OK Michael EDWARDS24.10.93A217H
16ThomasRobert11/01/19949P1HigherANot found#N/AMichelle EVANS6.5.94A217F
17VickersLily24/09/19939P1HigherBOK Joanna FORD21.10.93A217H
18AshwinJames08/07/19949P2HigherCOK Craig GIBSON28.9.93A217I
19BellSophie04/04/19949P2IntermediateCOK William GRIFFITHS6.2.94A217F
20CarterJim18/12/19939P2IntermediateDNot foundJames CARTERElizabeth HALL27.8.94A217H
21CooperKathryn02/02/19949P2Intermediate+25%DOK Samuel HARRIS16.3.94A217I
22EvansMichelle06/05/19949P2IntermediateCError Alexander HATCH14.3.94A217F
23GibsonCraig28/09/19939P2IntermediateCOK Richard HILL26.10.94A217H
24HarrisSam16/03/19949P2IntermediateDNot foundSamuel HARRISChristopher JACKSON25.7.94A217I
25LloydIan05/03/19949P2Intermediate+10%COK Melanie JONES16.4.94A217H
26MasonSamuel28/10/19939P2IntermediateCOK Ian LLOYD5.3.94A217I
27OsborneGary07/04/19949P2IntermediateDNot found#N/AGraham MARSHALL13.6.94A271H
28ScottLizzy07/08/19949P2IntermediateENot foundElizabeth SCOTTSamuel MASON28.10.93A217I
29TelforZack19/01/19949P2HigherCOK Murray MILLS23.1.94A217F
30WildingYvonne04/02/19949P2IntermediateCOK Hannah MURRAY2.10.93A217H
31BaileyTabs19/11/19939P3FoundationReaderENot foundTabitha BAILEYConnor PHILLIPS10.5.94A217F
32BennettChas12/08/19949P3FoundationFNot foundCharles BENNETTRichard ROBERTS15.8.94A217F
33DavisNatasha18/02/19949P3FoundationFError James SAUNDERS10.10.93A217H
34FieldLaura28/03/19949P3FoundationDNot found#N/AElizabeth SCOTT7.8.94A217I
35HaynesWilliam06/02/19949P3FoundationDNot found#N/ASimon SMITH2.2.94A217I
36HatchAlexander14/03/19949P3Foundation+10%FOK Fiona TAYLOR30.3.94A217H
37JacksonChristopher25/07/19949P3IntermediateDOK Zack TELFOR19.1.94A217H
38MillsMurray23/01/19949P3FoundationFOK Lily VICKERS24.9.93A217H
39PhillipsConnor10/05/19949P3FoundationReaderGOK Yvonne WILDING4.2.94A217I
40RobertsRich15/08/19949P3Foundation+25%ENot foundRichard ROBERTSTheresa WOODS30.9.94A217F
41AndersonSimon02/02/19949P3IntermediateDNot found#N/A
42TaylorFiona30/03/19949P3FoundationDError 
43WoodsTerry30/09/19949P3Foundation+25%ENot foundTheresa WOODS
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")
I4:I43I4=IF(H4="not found",INDEX($K$4:$K$40,MATCH("*"&A4&"|"&C4,$K$4:$K$40&"|"&$L$4:$L$40,0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,234
Office Version
  1. 365
Platform
  1. Windows
Jay

Are you going to be teaching the formulas?
 

jayu89

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

Are you going to be teaching the formulas?
I hope not, but its been a task that requires a simple excel exercise. I am sure on the job, its a different case.

How comes you ask?
Thanks
 

jayu89

New Member
Joined
Jul 15, 2020
Messages
17
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Hey fluff
copied the formula into column I.
For those with error etc, the cell comes back with #VALUE. Is there something wrong on my end?
 

Fluff

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

ADVERTISEMENT

Did you confirm the formula with Ctrl Shift Enter.
 

jayu89

New Member
Joined
Jul 15, 2020
Messages
17
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Did you confirm the formula with Ctrl Shift Enter.

Thanks Fluff - that is what I didn't do. It had worked now.
What does ctrl shift and enter do? As opposed to me copying, pasting and pressing enter?
I really want to know how this works
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
What does ctrl shift and enter do? As opposed to me copying, pasting and pressing enter?
I really want to know how this works
See the link below (doesn't apply to Microsoft/Office 365).

 

Watch MrExcel Video

Forum statistics

Threads
1,123,456
Messages
5,601,772
Members
414,472
Latest member
Chris_1990

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