Sub ABC()
Dim rng1 as Range, rng2 as Range
Dim cell as Range, num as Long
With Worksheets(1)
set rng1 = .Range(.cells(2,1),.cells(2,1).End(xldown))
end with
With Worksheets(2)
set rng2 = .Range(.cells(2,1),.cells(2,1).End(xldown))
end with

for each cell in rng1
num = application.Countif(rng2,cell)
if num 0 then
cell.offset(0,1).Resize(1,num).Value = cell
end if
Next
End Sub


or look here http://www.rondebruin.nl/copy5.htm
--
Regards,
Tom Ogilvy



"wayne" wrote in message
...
Thanks Tom, but I'm still not quite sure on how to accomplish this.

worksheet 1 has the 199 rows with equip id's
worksheet 2 has the 50,000 rows with equip ids also.

I want to find all occurances of the equip ids in worksheet 1 that may be
in
worksheet 2 and when I do find them write them out.

Maybe I'm not explaining my problem very clearly...sorry.

thanks again.

"Tom Ogilvy" wrote:

use the vlookup worksheet function in the one worksheet to retreive the
data
related to the value in column A in the 119 rows.

Assume both worksheets have the same values in column A.

in B2

=if(countif(Data!A:A,$A2)0,Vlookup($A2,Data!$A$2: $M$50001,Column(),False),"")

then drag across to column M (in the example) and down to row 119

since this is programming, in code
Sub GetData()
with Worksheets("Sheet1").Range("B2:M120")
.Formula =
"=Vlookup($A2,Data!$A$2:$M$50001,Column(),Fals e)"
on Error Resume Next
.SpecialCells(xlFormulas,xlErrors).ClearContents
on Error goto 0
.Formula = .Value
end with
End sub

--
Regards,
Tom Ogilvy

"wayne" wrote in message
...
I have one worksheet with about 119 rows of data. I have the data in
column
A that I want to lookup in another worksheet(about 50,000 rows) and
when I
find the matching record I want to write out that row of data for the
match.

Thank you for your help.