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.
Multiple Criteria From Differing Rows



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

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.

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
Display MoreOption 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
Bill

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! 

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?

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.

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.

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,"")

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
Display MoreOption 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



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? 
Re: Multiple Criteria From Differing Rows
Bill,
This is a message I gotInvalid 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 modulelevel 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).

Re: Multiple Criteria From Differing Rows
Bill,
This is the line with the error 9Set BothCoursesTrue = CourseII.Find(Student, LookIn:=xlValue, lookat:=xlWhole)

Re: Multiple Criteria From Differing Rows
Quote from Bill RockenbachCringe2,
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


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.
Bill

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

Re: Multiple Criteria From Differing Rows
Quote
I got the same error 9 message on Ln 46, Col 9Hmmmmmm. 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.

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.

Re: Multiple Criteria From Differing Rows
Patty,
I don't see a file attached?


Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!