Can someone help with this please........

[Stuart]

Hi there,

I have two sheets within a spreadsheet,


One sheet contains a list of names and the other contains/should
contain data relating to those names.


Within sheet 1 I have a column with not only the word "Name" in it as
there is data below that.


In sheet 2 I have a list of names.


I want excel to check if sheet one column F contains the word Name then



it will check if the name next to that cell in Column G matches the
control sheet.


The name of the person will appear more than once in the sheet hence I
want it to search down a column.


If it is found place a tick next to the name in sheet 2 and if not
place a cross next to the name.


Let me know if anyone can help.


Many thanks for your help in advance.


Stuart

 

[acampbell012]

Stuart,

Got your email. I will take a look at this and see what I can come up
with for you. I may post back with a few questions.

Alan

 

[Stuart]

Thank you Alan,

Hear from you soon,

Thanks

Stuart

 

[acampbell012]

Stuart,

"Name" is in column F of sheet 1. Person's name is beside that in G. On
sheet one, routine first looks for "Name" in F. When found, it searches
sheet 2 for the offsetting name in found in G. If the name is found on
sheet 2, Column H is marked with "Y" otherwise it is marked with "N".

Based on your post, I made no accomodation for the name being found
already. E.G. "John" on sheet 1 twice and sheet 2 once is going to mark
both occurences on sheet one with a "Y"

Give this a go and let me know how it works.

Alan


Sub Name_Match()
Dim MyRange As Range
Dim MyRange2 As Range
Dim MyCell As Range
Dim Endrow As Long
Dim Endrow2 As Long
Dim Myfind
Endrow = Cells(Rows.Count, 6).End(xlUp).Row
Set MyRange = Range("F1:F" & Endrow)
With Sheets(2)
Endrow2 = .Cells(Rows.Count, 1).End(xlUp).Row
Set MyRange2 = .Range("A1:A" & Endrow2)
End With
For Each MyCell In MyRange
If MyCell.Value <> "Name" Then
GoTo MoveOn:
Else: Set Myfind = MyRange2.Find(What:=MyCell.Offset(0, 1).Value,
LookIn _
:=xlValues, lookat:=xlWhole)
If Myfind Is Nothing Then
MyCell.Offset(0, 2).Value = "N"
GoTo MoveOn:
Else
MyCell.Offset(0, 2).Value = "Y"
End If
End If
MoveOn:
Next
End Sub

 

[tina]

and btw, Stuart, this newsgroup is focused on MS Access relational database
software, not MS Excel. suggest you post future Excel questions to an Excel
newsgroup; you're likely to get a lot more help there than you will here.

 

[Stuart]

Hi Alan,

One of my control checks that needs to be ticked off is that we wont be
able to duplicate any of the names.

Is there a way in which we can have that macro search down a certain
way and if a "Y" has been marked before, then place the word
"duplicate" next to the name?

Hope this is possible,

Cheers

Stuart

 

[acampbell012]

Stuart,

Tina is correct, we need to go over to the Excel group. I will take a
look at your request and post a response there.

Alan