Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Unique records | Excel Discussion (Misc queries) | |||
Finding Unmatched Records | New Users to Excel | |||
Finding duplicate records | Excel Worksheet Functions | |||
Code not finding records | Excel Programming | |||
Finding unique records from a list. | Excel Discussion (Misc queries) |