Tech Support Forum banner
Status
Not open for further replies.
1 - 10 of 10 Posts

·
Registered
Joined
·
12 Posts
Discussion Starter · #1 ·
I am developing a course management spreadsheet. I have one worksheet that includes every course offered and a sheet that a user sees and uses to "search" for courses based on TERM (e.g., FA2010, WI2011, etc.), COURSE ID (e.g., BUS, BUS 150, HOS, etc.), or PROGRAM (e.g., business, hospitality, etc.).

The third sheet displays the results of all of the courses that meet the criteria specified by the user. It is hidden from the user. Then, on the search worksheet, the cells are filled with the content of the cells in the hidden results page.

The search/filter works fine. However, users want the results to be sorted in order of TERM, and then alphabetized by course ID. (Note: If the user selects a PROGRAM, such as business, it will automatically display results for courses that start with BUS, MGT, and MRK, which is business, economics, management, and marketing.)

For example, the current results page might display the following, if the user wants to filter courses by

PROGRAM: Business

Currently, an abridged display looks like this (since the courses are listed on the third sheet in alphabetical and sequential order, it displays them in alphebetical order):

B: COURSE ID C: COURSE TITLE D: TERM
BUS 110 Theory of the Firm SP2009
BUS 126 Professional Development SP2008
BUS 140 Sales and Sales Management SU2010
ECO 310 The Global Economy FA2009
MGT 322 Small Business Management SP2008
MGT 421 Retail Management SU2008
MGT 425 Succession Planning WI2009
MKG 341 Marketing Management SP2009

Instead, the user would like to see the courses like this:

B: COURSE ID C: COURSE TITLE D: TERM
BUS 140 Sales and Sales Management SU2010
MGT 425 Succession Planning WI2009
BUS 110 Theory of the Firm SP2009
MKG 341 Marketing Management SP2009
ECO 310 The Global Economy FA2009
BUS 126 Professional Development SP2008
MGT 322 Small Business Management SP2008
MGT 421 Retail Management SU2008

As you can see, the courses are sorted first sequentially by TERM (column D), and then sorted alphabetically by COURSE ID (column B). Specifically, in column B, the BUS courses appear first, in sequential order, followed by ECO courses, and tehn MGT and MKG. (So, BUS 110 appears before BUS 126, and both appear before ECO 110, if all three courses were offered in the same term, such as SU2010.)

Now, we have several programs, some of which also have multiple COURSE IDs within a single program. Other programs just have one COURSE ID (e.g., all classes in our hospitality program start with HOS).

I read quite a bit online about sorting macros but do not understand the nuances of the code. I would like to either have the user click a button or preferably run the macro automatically after completing the search and displaying the results for the user to see.

Any suggestions???
 

·
Moderator , Microsoft Support, MS Office Pro
Joined
·
2,540 Posts
Re: Excel---Sort

Hi ats,

Having the terms indicated by 'WI,SP,SU,FA' militates against any form of conventional sort. What you really need for the sort to work is to have terms specified like '20114,20113,20112,20111', combined with the numbers from the course IDs. These need to 'appear' on the master sheet and on the unit selection sheet, but they can be in a hidden column on the latter.

The attached workbook contains three sheets:
. Sheet1 has a 'master' sheet of courses with the course codes as indicated above;
. Sheet 2 has the unsorted subjects, per your example; and
. Sheet 3, which has the sorted subjects, based on some forumulae that do some sorting.

No macros required - which could be important given the contexts in which the workbooks might be used.

Sheet 1 has the course code calculation formulae, which appear unhidden on all worksheets. On Sheet 2, this would be retrieved by whatever filtering process you're already using. On Sheet 3, the course codes are retrieved in order from Sheet 2 and then used in a set of INDEX/MATCH formula to retrieve the corresponding subject details from Sheet 2.

I hope this helps.
 

Attachments

·
Registered
Joined
·
12 Posts
Discussion Starter · #3 ·
Re: Excel---Sort

Thanks---I am trying it out now. But, quick question:

In some cases, we do not know when a course ran the first time, so TERM = N/A.

If I use your awesome formula, I get a #VALUE! error. Is there a way to easily replace that with a 0, without messing up the sort? (In other words, all those with 0 would be grouped together, much like those with SP2012.)

Thanks so much for your help!!!


Art
 

·
Registered
Joined
·
12 Posts
Discussion Starter · #4 ·
Re: Excel---Sort

Worked great! HOWEVER, there is one problem. There happens to be instances when the course codes are the same:

PSY 360 Diversity in the Workplace, developed WI2009 --> Course Code: 20094360
BUS 360 Small Business Loans, developed WI2009 --> Course Code: 20094360

So, when the Sheet 3 (per your example) is created/sorted, it displayed PSY 360 twice, I assume, rather than each course.

Should the COURSE CODE take into account somehow that it may be different courses?
 

·
Registered
Joined
·
12 Posts
Discussion Starter · #6 ·
Re: Excel---Sort

Aside from teh above, works great... but now I am noticing that the sorted page (your Sheet3) did not include any of the courses where COURSECODE was 0; in other words, there was no term specified (e.g., SP2010) and was left blank. I replaced the #VALUE! errors with 0... and now the courses appear in the Sheet 2 (per your example) but do not appear on the sorted sheet.
 

·
Moderator , Microsoft Support, MS Office Pro
Joined
·
2,540 Posts
Re: Excel---Sort

Hi ats,

Try the attached update. Essentially, all that's changed is the formula used to calculate the course codes. They're now 15 digits long, and cater for courses for which the year and/or term is missing and for courses with the same subject ID #s. The latter have been accommodated by representing the three characters from the courses as their ASCII values in the course codes.
 

Attachments

·
Registered
Joined
·
12 Posts
Discussion Starter · #8 ·
Re: Excel---Sort

Wow! Great idea...worked great. However, now I'm not getting any courses with 2010. I noticed in your demo that the sorted sheet (sheet 3) does not contain any of the duplicate courses, even if they were offered in different terms.

When I applied your new formula, none of the courses with 2010 appeared on the sort page (your sheet 3) yet they appear on the filtered page (your sheet 2).

I attached my spreadsheet.

Your Sheet 1 is my "Courses" sheet.
Your Sheet 2 is my "LookUp" sheet (normally hidden from users).
Your Sheet 3 is my "Search---Courses" sheet.

There are other sheets, but they are linked, so I figured I would send the whole thing.
 

Attachments

·
Moderator , Microsoft Support, MS Office Pro
Joined
·
2,540 Posts
Re: Excel---Sort

Hi ats,

Re:
I noticed in your demo that the sorted sheet (sheet 3) does not contain any of the duplicate courses, even if they were offered in different terms.
I think you'll find i have 'BUS 110 Theory of the Firm' listed twice on Sheets 2 & 3, with different terms and that 'MGT 110' also appears.

Re:
When I applied your new formula, none of the courses with 2010 appeared on the sort page (your sheet 3) yet they appear on the filtered page (your sheet 2).
The updated workbook has 'BUS 140 Sales and Sales Management SU2010 20102668583140' on Sheets 2 & 3.

There is an error in your formula in 'Search---Courses' Q9 and following (you didn't update the row offsets to match your worksheet structure). It should be:
=IF(COUNTA(LookupResults!B:B)<ROW()+8,"",LARGE(LookupResults!R:R,ROW()-8))
With that change, the 2010 & 2011 data will appear.
 
1 - 10 of 10 Posts
Status
Not open for further replies.
Top