Create conflicts between items in a list using a fromula

[Moe12345]

Hi, i am working on a project right now and need some help. To describe my problem I have horizontal list in excel with items which are user roles. Then I have a vertical list with the names of the each user. Under the horizontal list I have X matching the users each userroles based on an extract. Then also have a cell where I gather all the X's for each individual user and then also have another cell where I want to have a formula to check wether there is a conflict between the user roles. Because the idea is that a user can have multiple userroles attributed to him/her but some of these roles are conflicting with eachother and do not go together. Asuming you yourself decide which ones are allowed together which ones are not, how could you arrange this? How do I make this cell formula knowing which roles can not be together(i.e the X's). Also notice if one only 1 of the "X's" conflicts with another x then the entire Range of X's is a conlict and this should be highlighted in the "user right check cell" with a formula. This is the part where I need most help with.

Please feel free to ask questions If it is not clear and for additional information. Thanks in advance!

 

[JGordon11]

Can be done with a VBA user defined function

Function ConflictCheck(Xs As Range, Conflicts As Range) As String
    Dim j As Long, ubx As Long, aConflicts, aXs, C, conflict
    aConflicts = Conflicts
    aXs = Xs
    ubx = Round(UBound(aXs, 2) / 2 + 0.1)
    For j = 1 To ubx
        If Len(aConflicts(1, j)) > 0 Then
            If aXs(1, j) = "X" Then
                C = Split(aConflicts(1, j) & "", ",")
                For Each conflict In C
                    If aXs(1, conflict) = "X" Then
                        ConflictCheck = "Conflict"
                        Exit Function
                    End If
                Next
            End If
        End If
    Next
End Function

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Role Table												Conflict Table
2
3	Name	Role1	Role2	Role3	Role4	Role5	Role6	Role7	Role8	Role9	ConflictCheck		Role1	Role2	Role3	Role4	Role5	Role6	Role7	Role8	Role9
4	Person1		X		X			X	X		Conflict		2	1,4		2,6		4	9		2,7
5	Person2	X			X	X	X	X	X		Conflict
6	Person3			X		X	X		X
7	Person4		X	X		X	X	X
8	Person5		X	X	X			X			Conflict
9	Person6	X	X	X		X	X	X		X	Conflict
10	Person7		X	X			X	X	X
11	Person8	X		X	X	X
12	Person9	X	X						X		Conflict
13	Person10	X			X				X	X
14	Person11		X	X	X				X	X	Conflict
Sheet1

Cell Formulas
Range	Formula
K4:K14	K4	=ConflictCheck(B4:J4,$M$4:$U$4)

 

[Moe12345]

[

Can be done with a VBA user defined function

Function ConflictCheck(Xs As Range, Conflicts As Range) As String
    Dim j As Long, ubx As Long, aConflicts, aXs, C, conflict
    aConflicts = Conflicts
    aXs = Xs
    ubx = Round(UBound(aXs, 2) / 2 + 0.1)
    For j = 1 To ubx
        If Len(aConflicts(1, j)) > 0 Then
            If aXs(1, j) = "X" Then
                C = Split(aConflicts(1, j) & "", ",")
                For Each conflict In C
                    If aXs(1, conflict) = "X" Then
                        ConflictCheck = "Conflict"
                        Exit Function
                    End If
                Next
            End If
        End If
    Next
End Function

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Role Table												Conflict Table
2
3	Name	Role1	Role2	Role3	Role4	Role5	Role6	Role7	Role8	Role9	ConflictCheck		Role1	Role2	Role3	Role4	Role5	Role6	Role7	Role8	Role9
4	Person1		X		X			X	X		Conflict		2	1,4		2,6		4	9		2,7
5	Person2	X			X	X	X	X	X		Conflict
6	Person3			X		X	X		X
7	Person4		X	X		X	X	X
8	Person5		X	X	X			X			Conflict
9	Person6	X	X	X		X	X	X		X	Conflict
10	Person7		X	X			X	X	X
11	Person8	X		X	X	X
12	Person9	X	X						X		Conflict
13	Person10	X			X				X	X
14	Person11		X	X	X				X	X	Conflict
Sheet1

Cell Formulas
Range	Formula
K4:K14	K4	=ConflictCheck(B4:J4,$M$4:$U$4)

Thanks, this works perfectly.