Comparing two lists with criteria in multiple rows

[dgock]

Hello,

I have worksheet with two columns. One has names of students and the other one has school course codes the students are requesting. I would like to know how many students are requesting two courses which would be a conflict should those courses occur at the same time. Put another way, how many students have requested both 'MCH--11' and 'MEN--11'. I've attached a sample of the table below. Thank you in advance for all of your advice and experience.

[TABLE="width: 246"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]CrsNo[/TD]
[/TR]
[TR]
[TD]Nathan[/TD]
[TD]MCH--11[/TD]
[/TR]
[TR]
[TD]Nathan[/TD]
[TD]MEN--11[/TD]
[/TR]
[TR]
[TD]Nathan[/TD]
[TD]MIMG-12[/TD]
[/TR]
[TR]
[TD]Nathan[/TD]
[TD]MPE--11[/TD]
[/TR]
[TR]
[TD]Nathan[/TD]
[TD]MPREC11[/TD]
[/TR]
[TR]
[TD]Nathan[/TD]
[TD]MSP--11[/TD]
[/TR]
[TR]
[TD]Nathan[/TD]
[TD]MSS--11[/TD]
[/TR]
[TR]
[TD]Nathan[/TD]
[TD]MTPA-11[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]MEN--10[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]MFMP-10[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]MINT-10[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]MPE--10[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]MPLAN10CO1[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]MSC--10[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]MSS--10[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]XLDCD10[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]MBEG-10[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]MEN--10[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]MFMP-10[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]MJA--10[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]MPE--10[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]MPLAN10CO1[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]MSC--10[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]MSS--10[/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD]MAWM-11[/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD]MDFT-11[/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD]MEN--11ENR[/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD]MESC-11[/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD]MFR--11[/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD]MIMCB11CO1[/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD]MPE--11[/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD]MSS--11[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]MEN--10[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]MFMP-10[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]MPE--10[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]MPLAN10CO2[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]MSC--10[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]MSP--10[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]MSS--10[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]MVAG-10[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]MADEM08[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]MADFS08[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]MADW-08[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]MEN--08[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]MFR--08[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]MMA--08[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]MMU--08BA3[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]MPHE-08[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]MSC--08[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]MSS--08[/TD]
[/TR]
[TR]
[TD]Carol[/TD]
[TD]MAC--11[/TD]
[/TR]
[TR]
[TD]Carol[/TD]
[TD]MCH--11[/TD]
[/TR]
[TR]
[TD]Carol[/TD]
[TD]MEN--11[/TD]
[/TR]
[TR]
[TD]Carol[/TD]
[TD]MFM--11[/TD]
[/TR]
[TR]
[TD]Carol[/TD]
[TD]MFR--11[/TD]
[/TR]
[TR]
[TD]Carol[/TD]
[TD]MPREC11[/TD]
[/TR]
[TR]
[TD]Carol[/TD]
[TD]MSS--11[/TD]
[/TR]
[TR]
[TD]Carol[/TD]
[TD]XAT--11WEX[/TD]
[/TR]
[TR]
[TD]Carol[/TD]
[TD]YHRA-1A[/TD]
[/TR]
[TR]
[TD]Rick[/TD]
[TD]MBI--11[/TD]
[/TR]
[TR]
[TD]Rick[/TD]
[TD]MEN--11[/TD]
[/TR]
[TR]
[TD]Rick[/TD]
[TD]MFM--11[/TD]
[/TR]
[TR]
[TD]Rick[/TD]
[TD]MPE--11[/TD]
[/TR]
[TR]
[TD]Rick[/TD]
[TD]MPREC11[/TD]
[/TR]
[TR]
[TD]Rick[/TD]
[TD]MSP--11[/TD]
[/TR]
[TR]
[TD]Rick[/TD]
[TD]MSS--11[/TD]
[/TR]
[TR]
[TD]Rick[/TD]
[TD]XAT--11[/TD]
[/TR]
[TR]
[TD]Maggie[/TD]
[TD]MCH--11[/TD]
[/TR]
[TR]
[TD]Maggie[/TD]
[TD]MEN--11[/TD]
[/TR]
[TR]
[TD]Maggie[/TD]
[TD]MICTP11[/TD]
[/TR]
[TR]
[TD]Maggie[/TD]
[TD]MPH--11[/TD]
[/TR]
[TR]
[TD]Maggie[/TD]
[TD]MPREC11[/TD]
[/TR]
[TR]
[TD]Maggie[/TD]
[TD]MSP--11[/TD]
[/TR]
[TR]
[TD]Maggie[/TD]
[TD]MTPA-11[/TD]
[/TR]
[TR]
[TD]Maggie[/TD]
[TD]XAT--11[/TD]
[/TR]
[TR]
[TD]Maggie[/TD]
[TD]XDPA-11[/TD]
[/TR]
[TR]
[TD]Glen[/TD]
[TD]APSY-12[/TD]
[/TR]
[TR]
[TD]Glen[/TD]
[TD]MBI--12[/TD]
[/TR]
[TR]
[TD]Glen[/TD]
[TD]MCMCC12[/TD]
[/TR]
[TR]
[TD]Glen[/TD]
[TD]MDNP-12[/TD]
[/TR]
[TR]
[TD]Glen[/TD]
[TD]MEN--12[/TD]
[/TR]
[TR]
[TD]Glen[/TD]
[TD]MGT----[/TD]
[/TR]
[TR]
[TD]Glen[/TD]
[TD]MIDS-2C[/TD]
[/TR]
[TR]
[TD]Glen[/TD]
[TD]MSACS12[/TD]
[/TR]
[TR]
[TD]Glen[/TD]
[TD]XAT--12[/TD]
[/TR]
[TR]
[TD]Glen[/TD]
[TD]YVPA-2C[/TD]
[/TR]
</tbody>[/TABLE]

 

[mayur7393]

Do you have the criteria's / list of courses that conflicts. If so please share.

Per my understanding you can make use of "Countifs"

To make it in the nature of control sheet, prepare a table with name of the courses on the X Axis and Name of the Students on Y Axis.

You can use =COUNTIFS($B:$B,J$1,$A:$A,$I2) (Where Column A is list of all candidates and B is list of the courses requested - the format from your query above)

Row 1 starting from Column J has list of all unique courses and Column I has list of unique students names. Based on the formula above you will get values of the courses requested by the students.

Now, You can use this table as a source table for your List of Courses that conflict. For example,

In Column J you will get 1 for students requesting MCH-11 and in Column K students requesting MEN-11, you can define criteria between those courses and identify the duplicates. You can also use conditional formatting for highlighting these duplicate instances out.

In case my understanding is not correct. Please provide more details so that we can help you out.

 

[dgock]

I apologize, but I think I need to provide more clarification of the situation and try to provide a more simplified spreadsheet for illustration:

- The Name and CrsNo fields are extracted from another sources so I would prefer to not have to reorder or format it
- The Test Course is entered by the user
- The Course Location is entered by the user
- The Course List is generated based upon course that are being offered in the Course Location
- The Potential Conflict column is what I need assistance on
- I would like to be able to scan the Name and CrsNo columns and count which students have both the Test Course code and the Course List code in their requests
- I have put the results of what the values should be:
- e.g.s
- For the Course List code MSC--11, the students John and Mary have this request and also have MCH-11 course request. Therefore if I were to place MCH--11 course in the 1(2) location it would conflict with 2 students
- For the Course List code MEN--11, the students Nathan, John, and Jill have this and MCH--11 as course requests. Therefore if I were to place a MCH--11 course in the 1(2) location it would be a conflict with all 4 students


[TABLE="width: 703"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]CrsNo[/TD]
[TD][/TD]
[TD]Test Course[/TD]
[TD]Course Location[/TD]
[TD]Course List[/TD]
[TD]Potential Conflict[/TD]
[/TR]
[TR]
[TD]Nathan[/TD]
[TD]MCH--11[/TD]
[TD] [/TD]
[TD]MCH--11[/TD]
[TD]1(2)[/TD]
[TD]MSC--10[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Nathan[/TD]
[TD]MEN--11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]MEN--11[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Nathan[/TD]
[TD]MIMG-12[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]MIMG-12[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Nathan[/TD]
[TD]MPE--11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]MPE--11[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Nathan[/TD]
[TD]MPREC11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]MPREC11[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Nathan[/TD]
[TD]MSP--11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]MSP--11[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Nathan[/TD]
[TD]MSS--11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]MSS--11[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Nathan[/TD]
[TD]MTPA-11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]MTPA-11[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]MEN--11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]MCH--11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]MINT-10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]MPE--11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]MPLAN10CO1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]MSC--10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]MSS--10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]XLDCD10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]MBEG-10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]MEN--11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]MBI--11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]MJA--10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]MPE--11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]MPLAN10CO1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]MSC--10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]MSS--11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD]MAWM-11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD]MDFT-11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD]MEN--11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD]MESC-11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD]MFR--11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD]MCH--11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD]MPE--11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD]MSS--11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Thanks for all your time and consideration of this scenario!