I'm going to assume that the first list you provided is located in Sheet1, and the second list that you provided is located in Sheet2. I am going to output results in Sheet3. And since you gave no means of how to input data, I'm going to improvise.
Code:
Sub myMacro()
sht1 = "Sheet1" 'Clients and restriction sheet
sht2 = "Sheet2" 'Food and ingrediant sheet
sht3 = "Sheet3" 'Output sheet
lastRow = Sheets(sht3).Range("A" & Rows.Count).End(xlUp).Row
If lastRow > 1 Then
Sheets(sht3).Range("A2:A" & lastRow).ClearContents
End If
lastRow = Sheets(sht3).Range("B" & Rows.Count).End(xlUp).Row
If lastRow > 1 Then
Sheets(sht3).Range("B2:B" & lastRow).ClearContents
End If
myInputValue = InputBox("You want to output the names of all the people who can and cannot eat the food you type in this box") 'type food1 into this box for example.
lastRow = Sheets(sht2).Range("A" & Rows.Count).End(xlUp).Row
firstRow = 2 'First row of data in sht2
i = firstRow
foundValueBool = FALSE
Do Until i > lastRow
If Sheets(sht2).Range("A" & i).Value = TRIM(myInputValue) Then
foundValueBool = TRUE
Exit Do
End If
i = i + 1
Loop
If foundValueBool = FALSE Then
MsgBox "Could not find " & myInputValue & " in the list of foods"
Exit Sub
End If
lastColumn = Sheets(sht2).Cells(i, Columns.Count).End(xlToLeft).Column
firstColumn = 2 'foods begin in column B
c = firstColumn
Dim arrayFoods as Variant
a = -1
Do Until c > lastColumn
a = a + 1
ReDim Preserve arrayFoods(a)
arrayFoods(a) = Sheets(sht2).Cells(i, c).Value
c = c + 1
Loop
a = 2 'output of sht3 will begin on row 2. If they can eat the food
b = 2 'output of sht3 will begin on row 2. If they can NOT eat the food
lastRow = Sheets(sht1).Range("A" & Rows.Count).End(xlUp).Row
firstRow = 2 'First row of data in sht1 starts on row 2
i = firstRow
Do Until i > lastRow
CanEatBoolean = TRUE
lastColumn = Sheets(sht1).Cells(i, Columns.Count).End(xlToLeft).Column
If lastColumn > 1 Then
firstColumn = 2 'First column of food data in sht1 is in column 2
c = firstColumn
Do Until c > lastColumn
For Each item in arrayFoods
If item = Sheets(sht1).Cells(i, c).Value Then
CanEatBoolean = FALSE
Exit Do
End If
Next item
c = c + 1
Loop
End If
If CanEatBoolean = TRUE Then
Sheets(sht3).Range("A" & a).Value = Sheets(sht1).Range("A" & i).Value
a = a + 1
Else
Sheets(sht3).Range("B" & b).Value = Sheets(sht1).Range("B" & i).Value
b = b + 1
End If
i = i + 1
Loop
End Sub
If they can eat the food you designate, it will output their name in sht3 column A.
If they can NOT eat the food you designate, it will output their name in sht3 column B.
So after you run the code, you can count how many can eat the food or how many can NOT eat the food by using a countIF function. If you need help with that easy function then let us know.
