list missing people from a list

[njenkins]

I have a group of 10 people doing 5 roles in a spreadsheet. I would like a cell to contain the five people NOT doing a role. So the formula would check the five cells, compare with a reference containing all ten initials, and list the initials of people not in a role.

 

[James006]

Hello,

If you need to insert 5 names in a single cell .. you will have to concatenate the results of your formula ..

Hope this will help

 

[jolivanes]

Not a formula.
The 10 names are in D1:D10
The 5 names of the participants are in E1:E5
Result is in F1
Change references as required.

Sub Maybe()
Dim c As Range
    For Each c In Range("D1:D10")
        If WorksheetFunction.CountIf(Range("E1:E5"), c.Value) = 0 Then Range("F1").Value = Range("F1").Value & " " & c.Value & ","
    Next c
Range("F1").Value = Mid(Range("F1").Value, 2, Len(Range("F1")) - 2)
End Sub

 

[AhoyNC]

If you have a later ver. of Excel with the TEXTJOIN function, you could use something like this.

This is an array function and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C
1	Names	Roll
2	JJ	Yes	JJ, ST, ET, YR, KL
3	DR
4	SAH
5	ST	Yes
6	FR
7	ET	Yes
8	YR	Yes
9	WQ
10	KL	Yes
11	GH
Sheet

 

[jolivanes]

@njenkins
Did any of the solutions help?