Hi,,,, solve my headache

I have 2 sheets within excel. i need a formula that will check the
names from 2 sheets, if it has the same name then it will enter into
sheet2 column G the contact number ( Sheet 1 - column C)

How the spread sheet is setup at the min!!
Sheet 1! In column A I have Surname - column B I have first name -
column C contact Number.
Sheet 2! In column A first name - column B surname.


TIA
Much appreciate any help

Hi Tia,
I don't know about formula, but here a macro that solve your problem.
enjoy...
Rick

Option Explicit

Public Sub Contacts()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim s1Rc As Integer, s2Rc As Integer

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Application.ScreenUpdating = False '' stop screen update

s2Rc = 3 '' start row sheet 2
Do Until IsEmpty(ws2.Cells(s2Rc, "A"))
s1Rc = 3 '' start row sheet 1
Do Until IsEmpty(ws1.Cells(s1Rc, "A"))
'' compare names (First + Surnmame)
If (ws2.Cells(s2Rc, "A") & ws2.Cells(s2Rc, "B") = _
ws1.Cells(s1Rc, "B") & ws1.Cells(s1Rc, "A")) Then
'' copy contact from sheet1 to sheet2
ws1.Cells(s1Rc, "C").Copy ws2.Cells(s2Rc, "G")
Exit Do
End If
s1Rc = s1Rc + 1 '' next row sheet1
Loop
s2Rc = s2Rc + 1 '' next row sheet2
Loop

End Sub



"Andy" wrote in message
oups.com...
Hi,,,, solve my headache

I have 2 sheets within excel. i need a formula that will check the
names from 2 sheets, if it has the same name then it will enter into
sheet2 column G the contact number ( Sheet 1 - column C)

How the spread sheet is setup at the min!!
Sheet 1! In column A I have Surname - column B I have first name -
column C contact Number.
Sheet 2! In column A first name - column B surname.


TIA
Much appreciate any help

Andy Wrote:
Hi,,,, solve my headache

I have 2 sheets within excel. i need a formula that will check the
names from 2 sheets, if it has the same name then it will enter into
sheet2 column G the contact number ( Sheet 1 - column C)

How the spread sheet is setup at the min!!
Sheet 1! In column A I have Surname - column B I have first name -
column C contact Number.
Sheet 2! In column A first name - column B surname.


TIA
Much appreciate any help

If you can make do by checking only first name or only surname ( you
cannot match both name and surname ) and then want to fill up the
contact number in Col G then you could try Vlookup. Suppose you want to
match surname. Let us assume that your table on sheet 1 has 20 rows of
data ( 1 to 20 ) then your lookup range is A1:C20. Also assume that
your table on sheet 2 starts from row 1 then formula for G1 would be
VLOOKUP(A1,sheet1!A1:C20,3). You can autofill this formula to all the
cells in G down below.

And yes one more pointer. Data in sheet 1 (A1:C20) should be sorted on
A1( Surname) in ascending order.

A V Veerkar


--
avveerkar
------------------------------------------------------------------------
avveerkar's Profile: http://www.excelforum.com/member.php...o&userid=30338
View this thread: http://www.excelforum.com/showthread...hreadid=506533