Filter on multiple columns without excluding any relevant data

**aureus30** · 07-10-2010, 11:58 AM

Hi,

Recently went for a job interview in a school and had to do a test on excel. Completely messed it up but amazingly got the job. I've been trying and trying to work out what I needed to do but I'm still stuck. Please HELP!!

Was given a spreadsheet with one pupil name per row, followed by 12 subjects and the corresponding exam result. All this data was contained in 1 row. There were hundreds of rows, one for each pupil.

The task was to create 1 certificate per child to show the pupil name plus each subject name that the pupil had achieved 'above' or 'inline' in. If the result achieved was 'below', the subject would not be printed on the certificate. If the pupil got 'below' for every subject then they would not get a certificate.

Have attached spreadsheet to demonstrate the data.

We were told that we could possibly import the data into Access but I've tried this and still can't figure it out. Please HELP!!

**scottwhittaker2333** · 07-10-2010, 01:14 PM

Im answering this only because its seems like an easy one.
However please change the name of your tread title as it is way to vauge and anyone who wants to use the forum to learn things would have no way of finding this thread as it is poorly named.

Anyway I think the file that I attached should point you in the right direction. Unfortunatly the data needs to be in the format that I have put it in. You will see that the cert lookup you can click on the surname and choose from the list and the vlookup formulas return the fest of the info from the data tab.

Don't forget please please please change the thread name. If you don't know how read the forum rules (you really should do this anyway, it only takes about 5 to 10 minutes)

**scottwhittaker2333** · 07-10-2010, 01:16 PM

Nevermind I see you already did. Thanks

**aureus30** · 07-10-2010, 02:06 PM

Thanks Scott. I prob didn't explain very well, but the certificate that needed to be produced was in a Word doc so I'm presuming some kind of mail merge would need to be done. Also, I don't think changing the surname in order to produce each certificate would be very practical as there could be hundreds, possibly even over 1000 to produce. The test was only half an hour long yet we were supposed to have generated each certificate.

If you or anyone else has any other suggestions that would be great, thanks again