finding records
 

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.

finding records
 

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.

finding records
 

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.

finding records
 

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.

finding records
 

Tom-

I will try to clarify my question....


Worksheet 1 has 119 records that have equip ids in column A sorted in
ascending order.

Worksheet 2 has 50,000 records that have equip ids in column A but not sorted.

I'm looking for a way to compare to lookup all occurrences of the records in
worksheet 1 that are matched(equip ids) in worksheet 2 and when I find a
match I want to copy columns b thru d from worksheet 2 to a new worksheet.

Can you offer any advice on how to accomplish this. Thanks

Wayne

finding records
 

I gave you a link to Ron de Bruin's site that should fix you up.


--
Regards,
Tom Ogilvy


"wayne" wrote in message
...


Tom-

I will try to clarify my question....


Worksheet 1 has 119 records that have equip ids in column A sorted in
ascending order.

Worksheet 2 has 50,000 records that have equip ids in column A but not
sorted.

I'm looking for a way to compare to lookup all occurrences of the records
in
worksheet 1 that are matched(equip ids) in worksheet 2 and when I find a
match I want to copy columns b thru d from worksheet 2 to a new worksheet.

Can you offer any advice on how to accomplish this. Thanks

Wayne

finding records
 

Tom - I lloked at that link but it is my understanding that the autofilter
has a limit of 1000 records.- am I not understanding what you are saying???
thanks

Wayne

"Tom Ogilvy" wrote:

I gave you a link to Ron de Bruin's site that should fix you up.


--
Regards,
Tom Ogilvy


"wayne" wrote in message
...


Tom-

I will try to clarify my question....


Worksheet 1 has 119 records that have equip ids in column A sorted in
ascending order.

Worksheet 2 has 50,000 records that have equip ids in column A but not
sorted.

I'm looking for a way to compare to lookup all occurrences of the records
in
worksheet 1 that are matched(equip ids) in worksheet 2 and when I find a
match I want to copy columns b thru d from worksheet 2 to a new worksheet.

Can you offer any advice on how to accomplish this. Thanks

Wayne

finding records
 

I think the 1,000 limit has to do with the count of the items appearing
in the "dropdown" box of an auto-filtered column header -

"wayne" wrote in message
:

Tom - I lloked at that link but it is my understanding that the autofilter
has a limit of 1000 records.- am I not understanding what you are saying???
thanks

Wayne

"Tom Ogilvy" wrote:

I gave you a link to Ron de Bruin's site that should fix you up.


--
Regards,
Tom Ogilvy


"wayne" wrote in message
...


Tom-

I will try to clarify my question....


Worksheet 1 has 119 records that have equip ids in column A sorted in
ascending order.

Worksheet 2 has 50,000 records that have equip ids in column A but not
sorted.

I'm looking for a way to compare to lookup all occurrences of the records
in
worksheet 1 that are matched(equip ids) in worksheet 2 and when I find a
match I want to copy columns b thru d from worksheet 2 to a new worksheet.

Can you offer any advice on how to accomplish this. Thanks

Wayne

finding records
 

I guess you are not very conversant in excel. What Debra told you was that
the dropdown only lists 1000 unique record ID's. You asked about manually
apply the filter. The 1000 unique IDs could cover all 65535 rows. You
could also have more than 1000 unique IDs - you just can not select them
from the dropdown and Debra showed you how to work around that.

Since Ron's code never uses the dropdown, but essentially uses Debra's
approach and supplies the ID value to use in the filter, there is no
problem.

--
Regards,
Tom Ogilvy



"wayne" wrote in message
...
Tom - I lloked at that link but it is my understanding that the autofilter
has a limit of 1000 records.- am I not understanding what you are
saying???
thanks

Wayne

"Tom Ogilvy" wrote:

I gave you a link to Ron de Bruin's site that should fix you up.


--
Regards,
Tom Ogilvy


"wayne" wrote in message
...


Tom-

I will try to clarify my question....


Worksheet 1 has 119 records that have equip ids in column A sorted in
ascending order.

Worksheet 2 has 50,000 records that have equip ids in column A but not
sorted.

I'm looking for a way to compare to lookup all occurrences of the
records
in
worksheet 1 that are matched(equip ids) in worksheet 2 and when I find
a
match I want to copy columns b thru d from worksheet 2 to a new
worksheet.

Can you offer any advice on how to accomplish this. Thanks

Wayne

finding records
 

About an hour and a half ago, Debra Dalgleish told Wayne this in much more
detail:

An AutoFilter dropdown list will only show 1000 entries. If your column
has more than 1000 unique items, they can be filtered, but they can't
all be displayed in the dropdown list.

To AutoFilter for an item that doesn't appear in the dropdown list, you
can choose Custom from the dropdown list, and type the criteria. Also,
there are a couple of workarounds he

http://www.contextures.com/xlautofilter02.html#Limits

-------------------------------------------

Apparently that was not sufficient to prevent this question or your less
detailed response.

Hopefully I have explained more clearly.

Anyway, It might be more efficient to copy all the data at once using an
Advance filter and using his sheet with the 119 values as a criteria range.
then it would only take a single command to copy the actual data. I will
let you give him that code.



--
Regards,
Tom Ogilvy


"JMay" wrote in message
...
I think the 1,000 limit has to do with the count of the items appearing in
the "dropdown" box of an auto-filtered column header -

"wayne" wrote in message
:

Tom - I lloked at that link but it is my understanding that the
autofilter
has a limit of 1000 records.- am I not understanding what you are
saying???
thanks

Wayne

"Tom Ogilvy" wrote:

I gave you a link to Ron de Bruin's site that should fix you up.


--
Regards,
Tom Ogilvy


"wayne" wrote in message
...


Tom-

I will try to clarify my question....


Worksheet 1 has 119 records that have equip ids in column A sorted in
ascending order.

Worksheet 2 has 50,000 records that have equip ids in column A but
not
sorted.

I'm looking for a way to compare to lookup all occurrences of the
records
in
worksheet 1 that are matched(equip ids) in worksheet 2 and when I
find a
match I want to copy columns b thru d from worksheet 2 to a new
worksheet.

Can you offer any advice on how to accomplish this. Thanks

Wayne