I am a job seeker completing a hiring exercise and I have run into a problem - PLEASE HELP!!!!!!

[cschmid3]

Hello all.

Because of budget cuts I am a teacher who was recently laid off from the School District of Philadelphia.

I am applying for a job that works with schools to offer them support in analyzing test data.

Below are the directions for a hiring exercise I am working on:

Context: At the beginning of the school year, analysts receive their schools' roster information containing information that includes student names, state IDs, birthdays, and ELA/Math teachers and class period assignments. Analysts need to upload this information into ANet’s reporting tool using a roster template to format the roster so that the information can be uploaded correctly.

Scenario: One of your schools submitted their roster information (see attached "Part 2 - School X Roster"), but the information needs to be formatted in order to be uploaded.

Task: Using the attached “Part 2 - School X Roster," please transfer and format the roster information into the next tab in this file (labeled "ANet Roster Template") according to the formatting steps below. Please also list the steps you took when formatting in the 3rd tab of this file so that we can gain an understanding of the excel tools/functions you used. Submit the formatted roster information and steps you took as an attachment along with your responses to parts 1 and 3.

The problem I have run into is that the raw data gives you information about the students' English (in this case called ELA) AND Math class periods and teacher names.

However, the template that they want us to use has only ONE column for teacher name. Also one of the columns in the chart says "SUBJECT" and the drop down menu indicates that you are to enter ELA OR Math in the fields in that column. But there is ELA AND Math information for each student (each row).

This makes me think that there must be a way to organize the spreadsheet so that you can select ELA or Math and see different information based on which subject you have selected.

If someone could help me figure this out I would be so SO thankful!

I really want this job and I have hit a dead end with this assignment.

PLEASE HELP! Thank you!

 

[BrandonWLH]

So let me get this straight, you were a teacher, and now you are being given a hiring exercise to see if you are fit to do a job and you are asking us to help you pass the test?


Am I the only one that sees the irony in this?

Wouldn't you send a student to the office with a referral or give them a zero for doing the same thing on a test by asking their neighbor for help?

 

[cschmid3]

Yes, while the employers in this case want to see how you complete a sample task, they have made it clear that they offer extensive training to all new hires. For that reason I feel that it is not dishonest to seek technical assistance on the hiring exercise, because they are not asking everyone to be Excel experts, they just want you to do your best.

Also, I think of this more as an assignment than a test.

If this was a test the employer would have me come in to complete the exercise in a limited time frame and without access to resources.

I am sure they would expect that I would use the information gathering resources that the Internet provides to work through any questions that arise during the exercise.

I am just trying to put my best foot forward.

I wanted to see if there was an advanced function in Excel that could solve my problem or if I should just change the template to include more columns for both the Math and ELA data.

I am not asking anyone to do the work for me, I just wanted to see if there was something I was missing in my assessment of the situation or a tool that I could use that I was not aware of.

Hopefully someone else is willing/able to provide some insight.

 

[Smitty]

Welcome to the Board!

Seems to me that you don't have much choice on the template design:

upload this information into ANet’s reporting tool using a roster template to format the roster so that the information can be uploaded correctly.

But without seeing what data you were given for input and the expected output it's kind of hard to make any recommendations. Check the link beneath my sig for how to post a shot of your sheet.

 

[cschmid3]

Private Sub Worksheet_Activate()

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

End Sub



Did I post the correct thing?

 

[xenou]

RE your question, my "quick and dirty" approach would be to copy the data so you have it in two blocks on top of each other - then just take column 1 from the first block and column 2 from the second block. This is easy and works well if you only have a small number of columns.


Below I actually use a formula to pull out the data I want. But you could easily copy/paste to do the same.
Formula in D2; =A2
Formula in E2: =B2
Formula in E7: =C2 (start of second block)

That is, if I understand the problem correctly.

<img alt="worksheet data" src="http://northernocean.net/etc/mrexcel/20110713_img.png"/>

Edit: I'm afraid your attempt to post a shot of your data really didn't work ... somehow you've managed to post the htmlmaker source code rather than using it to generate html.

Edit2: In Cell E2 the heading should be "SUBJECT"

 

[cschmid3]

Thank you for the suggestion!

Any tips on how to generate the code correctly?

Thanks again!

 

[xenou]

Well, you are supposed to download the addin, then just use it. Google around on using Excel addins (add your Excel version number to your google search) - I'm too foggy right now to try to come up with detailed instructions (though the basic idea is just to put the file in your addins folder, then load the addin, and it should be available as part of your menu options and on your right click menu).

 

[Smitty]

Did I post the correct thing?

Not quite, the HTML Maker is an add-in that let's you post shots of your sheet. You posted the actual code for the HTML Maker itself (note that I deleted those posts). There's a Word doc on that SkyDrive site where you downloaded the HTML Maker that will walk you through installing it and showing a shot of your sheet.

Once you get the hang of it, it's a breeze.