I have been given a excel sheet of 50+ columns 2000+ rows and all over the
place are the some 2500 names and telephone numbers and lots of rubbish -
fortunately the telephone number that belongs to each name is 2 rows down in
the same column. (On a row with other names and lots of rubbish) If you Look
for X, Y, and H names and numbers below you can see what I mean.

X-name rubbish rubbish H-name
rubbish Y-name rubbish rubbish
X-number rubbish rubbish H-number
rubbish Y-number J-name

I have a separate sheet of 400+ names I want the telephone numbers for. All
of my 400 names and numbers are in the 50x2000 row sheet

How can I search for X-name (out of my list) and put in the next column the
number that relates to it from the 50x2000 sheet (two rows down in the same
column)
So I end up with;

X-name X-number
Y-name Y-number
H-name H-number

Regards
Leon

Whenever I've had to do stuff like this, there are always differences
(misspellings, extra spaces) that cause trouble. But this may help you get
started with the bulk of them:

Option Explicit
Sub testme()

Dim ListWks As Worksheet
Dim RubWks As Worksheet
Dim myCell As Range
Dim FoundCell As Range

Set ListWks = Worksheets("sheet1")
Set RubWks = Worksheets("sheet2")

With ListWks
For Each myCell In .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
With myCell
If Application.CountIf(RubWks.UsedRange, .Value) 1 Then
.Offset(0, 1).Value = "Multiple matches"
Else
Set FoundCell = RubWks.Cells.Find(what:=.Value, _
after:=.Cells(1), lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
.Offset(0, 1).Value = "No match"
Else
.Offset(0, 1).Value = FoundCell.Offset(2, 0).Value
End If
End If
End With
Next myCell
End With
End Sub

Each time you run this macro, it plops something into cell to the right.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Leon Jaeggi wrote:

I have been given a excel sheet of 50+ columns 2000+ rows and all over the
place are the some 2500 names and telephone numbers and lots of rubbish -
fortunately the telephone number that belongs to each name is 2 rows down in
the same column. (On a row with other names and lots of rubbish) If you Look
for X, Y, and H names and numbers below you can see what I mean.

X-name rubbish rubbish H-name
rubbish Y-name rubbish rubbish
X-number rubbish rubbish H-number
rubbish Y-number J-name

I have a separate sheet of 400+ names I want the telephone numbers for. All
of my 400 names and numbers are in the 50x2000 row sheet

How can I search for X-name (out of my list) and put in the next column the
number that relates to it from the 50x2000 sheet (two rows down in the same
column)
So I end up with;

X-name X-number
Y-name Y-number
H-name H-number

Regards
Leon

--

Dave Peterson

This can be done by macro by using your names in the target sheet as
search criteria on your rubish sheet.

The following macro should work, you will need to change the sheet
names and ranges to the ones that you actually have.

Sub GetData()
Sheets("Target sheet").Select
Set DataRange = Range(Cells(1, 1), Cells(1, 400))
For Each Cell In DataRange
Cell.Offset(0, 1) = Sheets("Rubbish").Cells.Find(Cell, , xlValues,
xlWhole).Offset(2, 0)
Next Cell
End Sub


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=546150