I'm trying to help my coworker and build him a spreadsheet that reduces his workload. He tracks the success/failure rate in various classes within a school. He pulls a report weekly and then creates various charts and stuff based off of the constantly updating information. The data is as follows:
-Column A is a code that is unique to a given course, there are hundreds of these codes in no specific order.
-Column B is a code for a specific class within a course. The code is formatted as the fiscal year (which changes on October 1st) and then a three digit code for the class. Ex: 2017001 (first class of FY 2017).
-Column C-F: Arbitrary numerical information about how many students graduated, failed, etc.
-Column G: The ending date of the class.
Some issues with the data:
-The number of rows changes, but seems to hover around 10,000 or so.
-The codes in column B are formatted as text, and I would like to prevent my coworker from having to convert those to numbers. I don't really plan on using the dates, but I did think they could be useful for sorting the information because they have a numerical value?
-There can be (but not always) more than one row for a course/class due to different student types. There are 2 student types, so every course/class has 1 or 2 rows.
- The information comes sorted as column B, then column A.
What I'm looking for is the ability to type a course code (a value from column A) into a specified cell, and have it pull out all of the information that is associated with that course, and consolidate it by class. Since there is an unspecified number of classes associated with any given course over a time frame, I would like it to be able to populate an list with an unknown amount of columns? Another issue is sometimes we need to look at more than 1 course code (for example a specific math class may have 3 course codes that are all "different courses" because they're taught by different people or something, but are the same information, so they are looked at together). So if I could have the user be able to type all course codes into a column or something, and then have the corresponding information pulled out for all of the 3 or 4 courses pulled into a new table, that would be ideal.
I will insert a sample table of the data below. I've been trying to do this stuff with named ranges, sumproduct, small, etc. but have had no luck. Any help with a creative solution here is appreciated!
-Column A is a code that is unique to a given course, there are hundreds of these codes in no specific order.
-Column B is a code for a specific class within a course. The code is formatted as the fiscal year (which changes on October 1st) and then a three digit code for the class. Ex: 2017001 (first class of FY 2017).
-Column C-F: Arbitrary numerical information about how many students graduated, failed, etc.
-Column G: The ending date of the class.
Some issues with the data:
-The number of rows changes, but seems to hover around 10,000 or so.
-The codes in column B are formatted as text, and I would like to prevent my coworker from having to convert those to numbers. I don't really plan on using the dates, but I did think they could be useful for sorting the information because they have a numerical value?
-There can be (but not always) more than one row for a course/class due to different student types. There are 2 student types, so every course/class has 1 or 2 rows.
- The information comes sorted as column B, then column A.
What I'm looking for is the ability to type a course code (a value from column A) into a specified cell, and have it pull out all of the information that is associated with that course, and consolidate it by class. Since there is an unspecified number of classes associated with any given course over a time frame, I would like it to be able to populate an list with an unknown amount of columns? Another issue is sometimes we need to look at more than 1 course code (for example a specific math class may have 3 course codes that are all "different courses" because they're taught by different people or something, but are the same information, so they are looked at together). So if I could have the user be able to type all course codes into a column or something, and then have the corresponding information pulled out for all of the 3 or 4 courses pulled into a new table, that would be ideal.
I will insert a sample table of the data below. I've been trying to do this stuff with named ranges, sumproduct, small, etc. but have had no luck. Any help with a creative solution here is appreciated!
