Log In
Register
Home
August 19, 2016
Offline
Yep, strange title but not sure how to describe this one.
I have a list (much larger than contained in the image) of students attending courses. The data course is actually Cognos but what I am trying to accomplish cannot be done directly in Cognos so want to try Excel VBA.
Description: list of students who attend a course and "wash back" or move to another class. The first class in the list is where they started, then the next one is the first move and so on to the last one where they actually complete or graduate the course. Data will actually contain anywhere from 0 to >3K entries. If there are no "wash backs" that data is filtered out in the report but students can have one or more wash backs, I chose this particular one because it was a good indicator of the movement.
Need: Not the solution but the methodology or approach to get the first washback class to go in the ClsTo column and so on as indicated in the image. I have toyed with arrays but not sure if this or a combination of approaches is necessary. Any ideas or thoughts on direction is greatly appreciated.
Doug M.
See Answer
July 16, 2010
Offline
Hi Doug,
Please upload a sample Excel file. Be sure to include a few students' data so we can see the layout of the table. I think you can use a formula for this, but we'll see when you share your file.
Mynda
August 19, 2016
Offline
Mynda,
Thank you for taking some time to look at this issue. I have attached the sample data as requested and added some additional rows to get a sense of how the data is structured.
Thank you,
Doug M.
July 16, 2010
Offline
Hi Doug,
You can use INDEX & MATCH with COUNTIF for this. See file attached.
Mynda
Answers Post
August 19, 2016
Offline
Mynda,
Thank you for the direction, that helps very much. Next step is to see if I can model this in VBA but in the mean time, this answers the mail.
Doug
July 16, 2010
Offline
Hi Doug,
Why would you want to model it in VBA when a formula does the job? I recommend avoiding VBA unless absolutely necessary. Typically built-in functions are more efficient for Excel than anything we can write ourselves, plus you don't run into any security issues in .xlsx workbooks.
Mynda
August 19, 2016
Offline
Mynda,
My apologies for not seeing this question sooner. What I should have said is that I was using VBA to apply the formula to entire column (fill down) to make it easier on the user. I created a small app using VBA to search and load updated data files that cleans and formats the data, applies the formulas and updates pivot tables that feed a chart. This one file replaced at least 7 separate spreadsheets updated manually and then queried to consolidate the data so that the slide could be built. Work can be done by one person instead of 7 or 8 so not quite a better mousetrap but slightly more efficient.
Doug
