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

  • Thread starter Thread starter Stuart
  • Start date Start date
S

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
 
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,

"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
 
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.
 
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
 
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
 
Back
Top