Match column contents on 2 worksheets and input text when match is found

bcselect

Board Regular
Joined
May 13, 2021
Messages
80
Office Version
  1. 365
Platform
  1. Windows
I have Sheet 1 and Sheet 2. They both contain the same list of names. Names are shown in 2 columns, Last name and First Name.
Sheet 1 also has a column ("A") for separate input. When an "M" is put in Sheet 1 cell A2, it should get the name in B2 & C2, look for a match
in Sheet 2, and when found place a "1" in Sheet 2 cell C2. It should continue until all rows have been cfhecked. I've been at it for hours but no luck.
 
Last edited:

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
I have Sheet 1 and Sheet 2. They both contain the same list of names. Names are shown in 2 columns, Last name and First Name.
Sheet 1 also has a column ("A") for separate input. When an "M" is put in Sheet 1 cell A2, it should get the name in B2 & C2, look for a match
in Sheet 2, and when found place a "1" in Sheet 2 cell C2. It should continue until all rows have been cfhecked. I've been at it for hours but no luck.
I see several views but no replies. Did I not explain it well or is this not possible? This is as far as I got before I realized that I had no clue where to go.
Sub Match()

Dim Cl As Range
Dim Dic As Object

Set Dic = CreateObject("scripting.dictionary")
With Sheets("Sheet2")
For Each Cl In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
Dic(Cl.Value) = Cl.Offset(, 3).Value
Next Cl
End With
With Sheets("Sheet1")
For Each Cl In .Range("M1", .Range("M" & Rows.Count).End(xlUp))
If Dic.exists(Cl.Value) Then Cl.Offset(, 3).Value = Dic(Cl.Value)
Next Cl
End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,214,834
Messages
6,121,876
Members
449,056
Latest member
ruhulaminappu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top