Multiple Criteria From Differing Rows

psindelar · Mar 17th 2007

I need to return a value if multiple criterion are met from different rows. I tried an if and formule but will not get a value since information is not entered on the same row. My criteria is text and numbers.

Wigi · Mar 17th 2007

Re: Multiple Criterion From Differing Rows

Hi psindelar

welcome to Ozgrid

Can you be more precise as to the structure of the Excel sheet. Then we can give you the correct formula. (or at least try to ;-))

Wigi

psindelar · Mar 18th 2007

Re: Multiple Criterion From Differing Rows

Thank you for your help!

I work at a school and am getting a report ready for the state of Illinois. I have 2,438 rows of student information. What I am attempting to do is add a "3" if a student took both semesters of a specified course. Semester 1 is listed in one column and row by the number "1." Semester 2 is listed in a separate column and row by the number "2." The unique qualifier is the student ID. I need to have Excel match student ID with both the semester 1 and semester 2 as well as the course. I have attached a sampling of the file I am working on. In FY column, I need a "3" next to those matching student IDs that took the same course for both semester I and 2.

Bill Rockenbach · Mar 18th 2007

Re: Multiple Criterion From Differing Rows

Hi there psindelar,

This code will solve your sample

In your sample it is a given that Business/Tech I must be match to Business/Tech II. In your live file do you have other courses. If so how are they matched up?

Code

Option Explicit


Sub MatchCourseSemesters()
Dim i As Long
Dim CourseI As Range 'Students taking BusinessI
Dim CourseII As Range 'Students taking BusinessII
Dim BothCoursesTrue As Range
Dim FirstCourseRecord As Range
Dim Student As Range


'Find Students taking CourseI
Set FirstCourseRecord = Cells(2, 2)
For i = 3 To 65536
    If Cells(i, 2) <> FirstCourseRecord Then Exit For
Next i
Set CourseI = Range(FirstCourseRecord.Offset(, 6), Cells(i - 1, 8))


'Find Students taking CourseII
Set FirstCourseRecord = Cells(i, 2)
FirstCourseRecord.Offset(6).Select
For i = i + 1 To 65536
    If Cells(i, 2) <> FirstCourseRecord Then Exit For
Next i
Set CourseII = Range(FirstCourseRecord.Offset(, 6), Cells(i - 1, 8))


'Find students taking both courses
For Each Student In CourseI
    Set BothCoursesTrue = CourseII.Find(Student, LookIn:=xlValue, lookat:=xlWhole)
    If Not BothCoursesTrue Is Nothing Then Student.Offset(, 3) = 3
Next Student


'Find students taking both courses
For Each Student In CourseII
    Set BothCoursesTrue = CourseI.Find(Student, LookIn:=xlValue, lookat:=xlWhole)
    If Not BothCoursesTrue Is Nothing Then Student.Offset(, 3) = 3
Next Student


End Sub

Display More

Bill

psindelar · Mar 18th 2007

Re: Multiple Criterion From Differing Rows

Bill,
Thank you for your help! I am not sure how to start the VB. I have attached a list of the other course titles. Some do not have a I or II after them. If you explain to me how to adjust this VB, I will make the adjustments for each new class. I really appreaciate your help!

Bill Rockenbach · Mar 18th 2007

Re: Multiple Criterion From Differing Rows

I looked at the course list and noticed some course titles are repeated many times. eg. Marketing. Is there always 2 semesters for each course. Or do some of the courses have more than one semester. As an example, what is the relationship of the 5 Marketing courses in rows 11 to 15?

psindelar · Mar 18th 2007

Re: Multiple Criterion From Differing Rows

I wish I could send you the whole file but it is too big. All the courses listed have 2 semesters. Some courses do not have the exact same name, such as: marketing and retail marketing. Would it be easier if I took the time to separate each pair of courses? I tried putting the VBA in the original spreadsheet and I got a message that said that the name did not exist. What did I do wrong. I also got an error 9 message when I hit the run button.

Bill Rockenbach · Mar 18th 2007

Re: Multiple Criterion From Differing Rows

Error 9 is "subscript out of range". without looking at your data its hard to say.

For each pair of courses are all the first semesters listed first in column “D” and then the second semesters listed in column “E”. Are there always two courses? Are there any empty cells in column “E” or “D”?

The program a posted keyed on the name of the program. I’ll change it to key on the sequnce of numbers in columns “D” and “E”. post back tomorrow.

Anonymous · Mar 18th 2007

Re: Multiple Criterion From Differing Rows

Put this formula in cell K2 and fill down:

=IF(AND(I2=1,SUMPRODUCT((J$2:J$3000=2)*(H$2:H$3000=H2))),3,"")

Bill Rockenbach · Mar 18th 2007

Re: Multiple Criteria From Differing Rows

Cringe2,
Great formula, works perfect for the first set of courses, but fails on succeeding sets of courses. If you can get it to work for succeeding sets of courses it’s a better solution than mine. (Also does not course reference course 2 to course 1)

psindelar,

Assuming the courses have alternately a 1 in column “I” and a 2 in column “J” with no empty cells within the 1 and 2 sequences I think this will work.

Put the code in the sheet module that your data is in.

Code

Option Explicit


Sub MatchCourseSemesters2()
Dim i As Long
Dim CourseI As Range 'Students taking BusinessI
Dim CourseII As Range 'Students taking BusinessII
Dim BothCoursesTrue As Range
Dim FirstCourseRow As Long
Dim Student As Range


Sheets("Sheet1").Activate
Range("K:K").ClearContents
Application.ScreenUpdating = False
Application.Calculation = xlManual






i = 2 'start courses at row 2


StartNextCourse:


'Find Students taking CourseI
FirstCourseRow = i
For i = FirstCourseRow To 65536
    'test if end of courses
    If IsEmpty(Cells(FirstCourseRow, 9)) Then
        Application.Calculation = xlAutomatic
        Application.ScreenUpdating = False
        MsgBox "Process Finished"
        Exit Sub
    End If
    
    If IsEmpty(Cells(i, 9)) Then Exit For
Next i
Set CourseI = Range(Cells(FirstCourseRow, 8), Cells(i - 1, 8))


'Find Students taking CourseII
FirstCourseRow = i
For i = i + 1 To 65536
    If IsEmpty(Cells(i, 10)) Then Exit For
Next i
Set CourseII = Range(Cells(FirstCourseRow, 8), Cells(i - 1, 8))


'Find students taking both courses
For Each Student In CourseI
    Set BothCoursesTrue = CourseII.Find(Student, LookIn:=xlValue, lookat:=xlWhole)
    If Not BothCoursesTrue Is Nothing Then Student.Offset(, 3) = 3
Next Student


'Find students taking both courses
For Each Student In CourseII
    Set BothCoursesTrue = CourseI.Find(Student, LookIn:=xlValue, lookat:=xlWhole)
    If Not BothCoursesTrue Is Nothing Then Student.Offset(, 3) = 3
Next Student


GoTo StartNextCourse


End Sub

Display More

Bill Rockenbach · Mar 18th 2007

Re: Multiple Criteria From Differing Rows

Note: If your data is not in sheet1 then change this line of code to sheet name your data is in.

Code

Sheets("Sheet1").Activate

psindelar · Mar 18th 2007

Re: Multiple Criteria From Differing Rows

Bill,
Yes, semester 1 courses are listed in column I with a 1 and semester 2 courses are always right after listed in column J with a 2. There are no spaces. I do not know VBA very well. I use alt F11 to open VBA and insert a module. I cut and paste your function in the project area. When I put =MatchCourseSemesters2 () in K2, I get an error message that there is an error in my function. What am I doing wrong?

psindelar · Mar 18th 2007

Re: Multiple Criteria From Differing Rows

Bill,
This is a message I got

Invalid outside procedure

The statement must occur within a Sub or Function, or a property procedure (Property Get, Property Let, Property Set). This error has the following cause and solution:

An executable statement, Static or ReDim, appears at module level.
Static is unnecessary at module level, since all module-level variables are static. Use Dim instead of ReDim at module level. To create a dynamic array at module level, declare it with Dim using empty parentheses.

Note At module level, you can use only comments and declarative statements, such as Const, Declare, Deftype, Dim, Option Base, Option Compare, Option Explicit, Option Private, Private, Public, and Type. The Sub, Function, and Property statements occur outside the body of their procedures, but within the procedure declaration.

For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).

psindelar · Mar 18th 2007

Re: Multiple Criteria From Differing Rows

Bill,
This is the line with the error 9

Set BothCoursesTrue = CourseII.Find(Student, LookIn:=xlValue, lookat:=xlWhole)

bradles · Mar 18th 2007

Re: Multiple Criteria From Differing Rows

Quote from Bill Rockenbach

Cringe2,
Great formula, works perfect for the first set of courses, but fails on succeeding sets of courses.

For the formula to work on both student id's you would need to use the following formula:
=IF( SUMPRODUCT( ($H$2:$H$3000=H2) * ($I$2:$I$3000=1) ) * SUMPRODUCT( ($H$2:$H$3000=H2) * ($J$2:$J$3000=2) ),3,"")

Hope that helps.

Brad

Bill Rockenbach · Mar 18th 2007

Re: Multiple Criteria From Differing Rows

Brad,
Thanks for the updated formula. It too works great for the first set of courses. It references in both direction. However may fail for subsequent course sets. The student ID is not unique to one set of courses. The student can and there probably will be students that take more than one set of courses. Your formula sets ranges of the entire universe not just the set of courses being examined. Therefore, the formula looks outside one course set into another to find matches,

psindelar,

Alt F11 simply opens the VBA editor. You may or not be at the correct location to insert the code. Suggest you right click the worksheet tab of the worksheet your data is in. From the displayed menu, click on View Code. This will take you to the VBA editor at the correct module to enter your code. You should see the sheet number highlighted in the window to the left of the editor. Insert the code here. Then, with the curser anyplace within the code press the Function F5 key. Be sure to change the sheets activate statement to the sheet name your data is in.

Code

Sheets("Sheet1").Activate

Bill

psindelar · Mar 18th 2007

Re: Multiple Criteria From Differing Rows

Bill,
Thank you for sticking with me on this. I added it just as you said. I changed the sheet name to List_Frame_1. When I hit the F5 key, I got the same error 9 message on Ln 46, Col 9. I don't know how to fix that. What should I try?

Patty

Bill Rockenbach · Mar 18th 2007

Re: Multiple Criteria From Differing Rows

Quote

I got the same error 9 message on Ln 46, Col 9

Hmmmmmm. Can you attach a file with just the first 500 rows of sheet “List_Frame_1” and no code ? Maybe I can duplicate the error.

psindelar · Mar 18th 2007

Re: Multiple Criteria From Differing Rows

Bill,
Here are the first 500 rows. I wanted you to see all the headers. I cleared the contents of student name and staff name - I didn't think you needed that info; however, I needed to make the file smaller to send. Thank you so much for your tenacity.

Patty

I don't think the last one got sent - too big. I hope this one works.

Bill Rockenbach · Mar 18th 2007

Re: Multiple Criteria From Differing Rows

Patty,

I don't see a file attached?

Multiple Criteria From Differing Rows

Participate now!

Share

Users Viewing This Thread