Using Excel to match columns according to business requirement

[kravi88]

Hello Everybody,

I have a business requirement to come up with a list of conflicting duties for any person who has duties assigned.
I am not able to get the logic to do this on Excel and would need some help.

Tables I have (Inputs):
1) Name and Duty
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Duty[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]duty1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]duty2[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]duty3[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]duty4[/TD]
[/TR]
[TR]
[TD]Megan[/TD]
[TD]duty5[/TD]
[/TR]
[TR]
[TD]Megan[/TD]
[TD]duty6[/TD]
[/TR]
[TR]
[TD]Megan[/TD]
[TD]duty7[/TD]
[/TR]
[TR]
[TD]Vince[/TD]
[TD]duty8[/TD]
[/TR]
[TR]
[TD]Vince[/TD]
[TD]duty9[/TD]
[/TR]
</tbody>[/TABLE]

2) Duty and Conflict

[TABLE="width: 500"]
<tbody>[TR]
[TD]Duty[/TD]
[TD]Conflict[/TD]
[/TR]
[TR]
[TD]duty1[/TD]
[TD]duty3[/TD]
[/TR]
[TR]
[TD]duty3[/TD]
[TD]duty1[/TD]
[/TR]
[TR]
[TD]duty5[/TD]
[TD]duty6[/TD]
[/TR]
[TR]
[TD]duty6[/TD]
[TD]duty5[/TD]
[/TR]
</tbody>[/TABLE]

Output required:
1) Name, Duty, Conflict
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Duty[/TD]
[TD]Conflict[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]duty1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]duty3[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]duty3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]duty1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Megan[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]duty5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]duty6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Megan[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]duty6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]duty5[/TD]
[/TR]
</tbody>[/TABLE]


I have attached a sample of the requirement. It has the input tables and the required output.

Please advice.
Warm regards.

 

[cyrilbrd]

Hi, just curious, why don't you just index the name of your #1 to match the Duty in your #2?

 

[kravi88]

Hi, just curious, why don't you just index the name of your #1 to match the Duty in your #2?

Hey !

Well, I want conflicts to be listed only if both the duty and the conflicting duty is assigned to the person.
And I need this to be done individually for each person. That's the reason I did not use indexing.

Any ideas ?

 

[cyrilbrd]

With a different presentation and a specific reference number rather than just the name (although you can change that to show only the name if you prefer).
Given in A1:

Excel 2010
	A	B	C	D	E	F
1	Name	Duty	ref_#		cdt	ref_#
2	Tom	duty1	1		Megan	5
3	Tom	duty2	2		duty1	6
4	Tom	duty3	3		duty3
5	Tom	duty4	4		duty5
6	Megan	duty5	5		duty6
7	Megan	duty6	6
8	Megan	duty7	7
9	Vince	duty8	8
10	Vince	duty9	9
kravi88

Formula in F2 is =IFERROR(SMALL(IF(MMULT(COUNTIF($E$2:$E$6,$A$2:$B$10),{1;1})=2,$C$2:$C$10),ROWS($F$1:F1)),"") copied down till needed.
Note range G2 to G6 could be a table then the reference COUNTIF($E$2:$E$6 could be changed to make reference to said table such as COUNTIF(Table1[cdt], , this would make it dynamic, hence you could add or remove conflict and names on the go...

 

[kravi88]

Hi cyrilbrd,

I'm not sure where you have to keep the "Duties,Conflict" table when I use your solution. I'm kind of very new to this.
And I noticed that in your result, you have Megan but not Tom.

Regards and Happy Friday.

 

[cyrilbrd]

You need not keep the conflict table, as the data are redundant, you need only a list as shown in post#4 cell E1 to E6.
This range being a table you may add names and conditions as you see it fit. Please look at the same below:

Excel 2010
	A	B	C	D	E	F	G
1	Name	Duty	ref_#		cdt	ref_#	name
2	Tom	duty1	1		tom	1	Tom
3	Tom	duty2	2		duty1	3	Tom
4	Tom	duty3	3		duty3	5	Megan
5	Tom	duty4	4		duty5	6	Megan
6	Megan	duty5	5		duty6
7	Megan	duty6	6		megan
8	Megan	duty7	7		vince
9	Vince	duty8	8
10	Vince	duty9	9
kravi88

As you can see I added megan and vince to the table and the query returned the names and reference of the conflicted schedule.
To create a table, highlight the range and go to Insert / Table , a pop up windows may appear asking you to confirm range and indicate if the table has headers (yes).
Once this done the formula will dynamically computed based on whatever conditions are stated in said table.

Let me know if you require further assistance.