Find Match based on list

[DBaker7777]

I have a list of products on Sheet 1 column A. On sheet 2 I have data and in column A are various products. What I need is to look up each product on Sheet 1 and then for each instance it appears in Sheet 2 I need to update 9 columns over to a Yes each time it finds the name. Any help is appreciated.

 

[kweaver]

If you would provide (a) the version of Excel you are running and (b) update your profile to reflect the Excel version and (c) use XL2BB to show some of your data/sheets and expected results, it would help those trying to provide a solution.

 

[DBaker7777]

I am working with Office 365 currently. I cannot use XL2BB currently. I am posting this from my phone.

 

[kweaver]

Which columns become YES? What happens in those columns when the product name isn't found?

 

[DBaker7777]

So it is only looking up the values in Sheet 1 column A and trying to find all instances of that in column A of Sheet 2. When it finds an instance it will place a “Yes” in the 9th Column. All the other values are marked “No” currently. So as the user enters more values to Sheet 1 column A and run the macro it will then mark those as “Yes”.

 

[DBaker7777]

So I have searched and modified the following code, but it is only replacing the first instance when I need it to replace all if it finds 7 matches for the same name:

Sub Macro4()
Dim cell as Range, rngFind as Range, counter As Long
With Sheets("Sheet1")
Set rngFind =.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
End With
For each cell in rngFind
Set Found = Sheets("Sheet2").Range("A:A").Find(What:=cell.value, _
LookIn:=xlvalues, LookAt:=xlPart, MatchCase:=False)
If Not Found Is Nothing Then
Found.Offset(, 8).Value = "Yes"
counter = counter +1
End If
Next Cell
MsgBox "Replacements made: " &  counter,,"Replacements Complete"
End Sub

This is an example of what I need.

Sheet 1
Column A

Employee
John
Mary

Sheet 2

Employee	Data1	Data2	Data3	Data4	Data5	Data6	Data7	Data8
John								No
Mike								No
John								No
Bob								No
Mary								No
Mary								No
Mary								No

 

[kweaver]

In your example above for John and Mary, what and where do you expect your "yes" and "no" results?

 

[DBaker7777]

Sheet 1 is the list to look up on Sheet 2. So John and Mary are the values I am searching for on Sheet 2. I need it to find each instance and replace “No” with a “Yes” in the 8th column.

 

[kweaver]

That didn't answer my question. Should all occurrences of John and Mary on the 2nd sheet be YES and the others NO?

 

[DBaker7777]

That didn't answer my question. Should all occurrences of John and Mary on the 2nd sheet be YES and the others NO?

Correct the others should remain no. And all the others should change to Yes