UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Check For Error In Training Classes Sequencing, Office 2013    
 
   
momcaro
post Apr 12 2017, 02:07 PM
Post#1



Posts: 286
Joined: 25-March 11
From: Wish I was in Colorado


Hello,
I have been handed a report that list students taking different classes, some online, some in person. They are assigned to those classes and some of them have to happen in sequence. For example, there is a prereq class, then the main class for a certain topic. Using the name of the person, course title and the status of each course they're assigned to (not yet enrolled, enrolled, completed), I want Excel to tell me which people have enrolled in a class before completing the prereq. So the formula would be something like "If Student A has not completed prereq ("no" next in the prereq row), look to see if they are enrolled in the main course". And if they are, of course, alert me... I'm not sure which function to use and how to combine them.
Thank you!

--------------------
Caroline
Mooing Sheep
Go to the top of the page
 
dflak
post Apr 12 2017, 04:05 PM
Post#2


Utter Access VIP
Posts: 5,981
Joined: 22-June 04
From: North Carolina


This sounds more like a database issue than an Excel issue, but let's take a look at it anyway.

It's difficult to see a solution without seeing how the data is organized. Please attach a sample workbook with non-sensitive data. Change student names if you have to. Also provide a mocked up sample of what you would like the output to look like. Zip up the workbook and attach it to the post.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
MadPiet
post Apr 12 2017, 04:29 PM
Post#3



Posts: 2,039
Joined: 27-February 09



I did this a long time ago in Access. The gist of the answer is to compare the prerequisites for a class to the classes the student has passed, and flag if there are any "missing" prereqs. (
Go to the top of the page
 
momcaro
post Apr 13 2017, 09:34 AM
Post#4



Posts: 286
Joined: 25-March 11
From: Wish I was in Colorado


The report has an output in Excel, so trying to keep it in there. I would like to know if it's possible in Excel also for future reference. Please see attached.
Thanks.
This post has been edited by momcaro: Apr 13 2017, 10:08 AM
Attached File(s)
Attached File  Book1.zip ( 7.74K )Number of downloads: 5
 

--------------------
Caroline
Mooing Sheep
Go to the top of the page
 
Daniel_Stokley
post Apr 13 2017, 10:15 AM
Post#5



Posts: 226
Joined: 22-December 14
From: Grand Junction, CO, USA


Yes, it can be done in Excel. My solution assumes that the data rows are sorted in such a fashion that all rows for a student are together and also that a Prerequisite course always precedes a Main course.
Attached File  CourseStatus.gif ( 8.43K )Number of downloads: 1

I used a "Helper" formula in column D. The formula in cell D2 is: =IF(ISNUMBER(FIND("Prereq",B2)),"Prereq",IF(ISNUMBER(FIND("Main",B2)),"Main","ERROR"))
That helper formula just returns "Prereq" or "Main". This assumes that your Course Title has one of those words.
The formula in cell E2 is: =IF(AND(D2="Main",C1<>"Complete"),"Enrolled in Main without completing Prereq","OK")
Go to the top of the page
 
momcaro
post Apr 13 2017, 11:51 AM
Post#6



Posts: 286
Joined: 25-March 11
From: Wish I was in Colorado


Sorry, that was my bad, I simplified the file way too much. Students have more than 2 courses each, and all have different numbers of courses assigned to them. I was looking for something more dynamic.
Right now, the way it's been done is with the use of conditional formatting and eye balling it (blue can't be next to red for the same student). Not too crazy at this point, it takes a few seconds to look through the list, but was wondering if there was something really cool Excel could do. It would have to look at the student's name, their courses and the completion status. Maybe and index/match, but I couldn't get far enough to solve it... Not a huge deal if it's not solved.
Thank you,

--------------------
Caroline
Mooing Sheep
Go to the top of the page
 
dflak
post Apr 13 2017, 12:23 PM
Post#7


Utter Access VIP
Posts: 5,981
Joined: 22-June 04
From: North Carolina


I thought the definition was a might "schoche" which is why I didn't respond. If you can provide is with some more representative data including course numbers and which courses are prerequisite for other courses and in the actual format in which you have the data, then we'll be in a better position to help.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
MadPiet
post Apr 14 2017, 06:02 PM
Post#8



Posts: 2,039
Joined: 27-February 09



If you can figure out how to do EXISTS and NOT EXISTS in Excel, then knock yourself out. In a nutshell, you outer join the Prerequisites table to the set of courses the student has already passed and look for missing values. (Prereqs LEFT JOIN CoursesTaken). Any missing Prereqs will be left if you filter for NULL on the CoursesTaken side.

Good luck with that.
Go to the top of the page
 
momcaro
post Apr 21 2017, 08:05 AM
Post#9



Posts: 286
Joined: 25-March 11
From: Wish I was in Colorado


Haha, thank you all. I have abandoned the project (pros vs cons). What we have works for now smile.gif

--------------------
Caroline
Mooing Sheep
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    27th July 2017 - 01:37 AM