tracking interviews vs open jobs/positions

[jodo3]

Hello all,

I was wondering if you guys could suggest for me the best way I can track interviews scheduled against a list of open positions. So everyday, interviews are scheduled for the positions that are open. I think Pivot Tables is a possible solution, but I'm having some trouble with updating it with new positions being added and a large number of positions.

Also, everyday I receive a list of open positions, each position has a unique identifier. Each position also states the number of days that it has been open. So my task is to keep a daily tally of interviews scheduled for the position.

So the first part of this task is I need to summarize:

Position, average number of days open, and the number of positions (so a count of positions with the same name)
and also summarize for each day, the interviews scheduled each position.

I know for the first part of it, I can use a pivot table, and get the average and the count, to get the number of positions. Beyond that though, I don't know how to add in the daily tracking in a way that is easy to read and summarizes the content. I could add more columns to the original data, with the dates and put the interview scheduled in there, and then show the whole thing in a pivot table, but when I tried that it didn't look too good, and needed tweaking.

Also the other issue is that everyday, positions are closed after they have been filled, and new positions are added. So as I mentioned, the interviews I'm tracking need to be done against positions available. So when a position has been filled, it will say "filled" next to it. What I don't want to do is lose track of the interviews scheduled for that position though, if I delete it and just use the new data, I lose that tracking. Any advice would be appreciated.

 

[Chihiro]

What version of Excel are you using? If 2013 or later version, you can use data model and relationships to build your summary report.

From the Sheet1 in sample, you are assigning unique identifier to each position (make sure this is unique and no duplicate exists in this table).
I'd also recommend tracking additional info in new column(s).
Date posted (start date), Date Close (Date Posted + Days Open), Status (Open/closed).

Use the same unique identifiers to track Interviews booked for each position in separate table (identifier can be repeated/duplicated in this table).
Columns like:
Unique Identifier; Position; Date Booked; Candidate Name etc.

Convert both data into Table format.

In the data tab, you should now see "Relationships" available (you may have to save and then re-open the workbook).

You can link 2 tables together to build single PivotTable, using Unique Identifier.

See links for details on data model and relationships.

http://chandoo.org/wp/2013/07/01/introduction-to-excel-2013-data-model-relationships/
http://www.techrepublic.com/blog/10-things/10-steps-to-using-excel-2013s-new-data-modeling-feature/

 

[jodo3]

Hi Chihiro, I am actually using Excel 2010, are all your tips based on Excel 2013? Can I use them in 2010, or would there be something else I should do?

 

[Chihiro]

Data Model and relationship, yes. That's only available 2013 and later versions.

However, you can still set up 2 table as mentioned.

You just have to use formulas in Interview table to pull data from the other table. Index,Match based on Unique Identifier should do the trick here.

Edit: This method, has few limitations compared to data model method. However, if you have good table/data structure, it will be much easier to set up later analysis/summary.

 

[jodo3]

Sorry, I'm not sure how to use index/match features too well, can you give me an example? Thanks!

 

[Chihiro]

See attached for mock up data.

You can add more field to table and adjust pivot as needed.

 

[jodo3]

Thanks, I really like that setup. I'm wondering, is it possible to add one thing to the first pivot table, that has:
position Average of Days open number of positions

Could I somehow link the interviews booked to that pivot table? So for example lets say I have booked a total of 10 production support interviews, can it also include that total on the first PT?

So the additional column would be something like "# of interviews booked for this position"

I really appreciate the help!

 

[Chihiro]

Added another column to first table "nInterview"
=COUNTIFS(tblIntview[UID],[@[unique identifier]])

Which counts # of interview that has same UID as Position.

FYI - Normally I'd recommend column header to be written without spaces. Just easier to deal with in the formula, coding etc.

 

[jodo3]

That's awesome! you are a wizard :D I like it personally, have to show my colleagues and see if they are okay with it.

final thing, is it possible to show show a summary of the report in a different format?

Attached a spreadsheet if it's possible to show like this.

Position Days, Open # of positions

 

[Chihiro]

Only thing you won't be able to put on same table is # of position.
However, you can show Unique ID under each Position.

Since pivot summarize data available in the source, only dates found in table are shown in summary (i.e. will not show dates where no interview was booked).

See attached.