Mentoring request

[Walkon]

Request

Part of the work I do is receiving and collating post-secondary supplemental application materials.

As this year’s applications (please see Context below) have all been processed, I am hoping to work on improving the system over the next few weeks in order to be ready for next year’s applications.

While the current process (please see Process below) works, it is slow and manual.

I am wondering if there may be a way, using Pivot Tables and/or Power Queries to automate the process.

My current level Excel knowledge isn’t sufficient to know. I am not looking for someone to create the solution for me.

Ideally, I am looking for someone to provide guidance as I learn how to develop the process myself.


Context

Part of the work I do is receiving and collating post-secondary supplemental application materials (SAM).

These materials consist of a supplemental application (SA) and two independent references.

The common identifier for these three separate forms is the applicant’s student number.

The applicant completes and submits the SA online.

The applicant also provides a link to their references who also each complete and submit a reference form online.

The applicant is also required to submit an application for admission to the university, again using the same student number as an identifier.

This application is received and processed by the Admissions department.

A component of my work is to produce a list of student numbers for applicants from whom all three pieces of their supplemental materials have been received. These applicants are identified as

Complete (C).

This list is combined with a list, produced by Admissions, of applicants who meet the admissions criteria.

This master list is then used to identify those applicants who SAM will be evaluated to rank the applicants as part of the process to determine which applicants will be offered a seat in the program.

Competition with other universities for the top students is strong, so evaluating applications and making offers to the top applicants starts prior to the deadline for application submission.

This means that, prior to the application submission deadline, I am required to regularly download the SAs and the reference forms and collate them in order to update the master list and allow the evaluation to start.

Since there is no required order of SAM submission, SAs and reference forms can come in at any time.

Therefore, with each download, I need to complete the following tasks (not necessarily in this order):

• Compile a list of SAs
• Compile a list of references
• Sort and collate these lists to create a list of Cs*
• Compare that list to its previous iteration, and remove those Cs who have previously been identified
• *During this process, the following data is deleted:
• SAs with less that two references
• References with no SA

Current process

The two sets of data (SAs and references) are downloaded separately and each data set is sorted separately by student number

An XLOOKUP formula within Conditional Formatting (colours) is then used on the reference data to sort for duplicate numbers. The result is then sorted by cell colour to identify the single references. Those are deleted, leaving a list of pairs of references.

VLOOKUP is then used with the SA data to determine which of the pairs of references match with an SA. Those references without an SA are deleted.

VLOOKUP is again used to determine which SAs don’t have two references. Those SAs are deleted, leaving a list of Cs.

VLOOKUP is then used to compare this list of Cs with its previous iteration and delete all the matches. This leaves a list of new Cs.

Interested?
I am hoping this is not an inappropriate ask for this site. I am quite hopeful this project may be of interest to someone.

Thanks for considering this request.

Rob

 

[Joe4]

This site is really set-up for specific (and pointed/singular) questions/answers, where it looks like you are looking more for "continual" help/guidance to see you through the whole process.
Something like that is really more suited for Consulting, where you can hire a single guide to help you and see you through the whole process.
MrExcel itself does not do Consulting, but we do have recommended consultants listed here: Consulting Services

What I have seen other people do is break their project down into singular task/questions, and post those one-at-a-time.
You are more likely to get responses to those sort of questions that one really long post with many questions.

 

[Walkon]

This site is really set-up for specific (and pointed/singular) questions/answers, where it looks like you are looking more for "continual" help/guidance to see you through the whole process.
Something like that is really more suited for Consulting, where you can hire a single guide to help you and see you through the whole process.
MrExcel itself does not do Consulting, but we do have recommended consultants listed here: Consulting Services

What I have seen other people do is break their project down into singular task/questions, and post those one-at-a-time.
You are more likely to get responses to those sort of questions that one really long post with many questions.

Good morning,

Thanks for this. It makes good sense and I will give it a try.