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.

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

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[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

Name	CrsNo		Test Course	Course Location	Course List	Potential Conflict
Nathan	MCH--11		MCH--11	1(2)	MSC--10	2
Nathan	MEN--11				MEN--11	3
Nathan	MIMG-12				MIMG-12	1
Nathan	MPE--11				MPE--11	3
Nathan	MPREC11				MPREC11	1
Nathan	MSP--11				MSP--11	1
Nathan	MSS--11				MSS--11	3
Nathan	MTPA-11				MTPA-11	1
John	MEN--11
John	MCH--11
John	MINT-10
John	MPE--11
John	MPLAN10CO1
John	MSC--10
John	MSS--10
John	XLDCD10
Mary	MBEG-10
Mary	MEN--11
Mary	MBI--11
Mary	MJA--10
Mary	MPE--11
Mary	MPLAN10CO1
Mary	MSC--10
Mary	MSS--11
Jill	MAWM-11
Jill	MDFT-11
Jill	MEN--11
Jill	MESC-11
Jill	MFR--11
Jill	MCH--11
Jill	MPE--11
Jill	MSS--11

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Thanks for all your time and consideration of this scenario!