Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup? Match? pulling rows from one spreadsheet to match a text f
Ok. I have a master worksheet that has rows containing lots of data I need.
In another worksheet, I have a short list of items I would like to select from the master. (Master has about 5k rows, short list is approximately 90 items) I have about 25 workbooks to sort, so I need a function to help me. Ok, more detailed description. Short List columns: Name, Description Master Columns: Name, Description, about 35 detail columns... What I want to do it maybe add a row as a key at the beginning of the Master and use the Name (Distinct field) from the short list to match against the Master. I have tried using lookup, but I think I have my parameters wrong because I get hundreds of rows returned when I know I have less that 100 rows in the short list. Any help appreciated! (questions also if I'm confusing.) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup? Match? pulling rows from one spreadsheet to match a text f
It is still not clear what you require In the small sheet you are trying to make a match with the master sheet Firstly are all the rows unique in the master sheet eg each name only appears once and you wish to return this information to the small sheet or do you wish to return all the rows matching the row in the small sheet? Give a small example say 3lines in the small sheet and 10 lines in the Master sheet (you do not need to list all the columns!) and describe what you wish to happen Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=563462 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup? Match? pulling rows from one spreadsheet to match a te
Small Table:
Name Description P3586PR Pump 3586 Pressure TCV3586EU Temp Control Valve 3586 Engineering Units P3587PR Pump 3586 Pressure Main Table: Name Description Misc Data Columns.... P3586HS Pump 3586 Handshake P3586PR Pump 3586 Pressure P3586Alm_Ack Pump 3586 Alarm Acknowledge TCV3586EU Temp Control Valve 3586 Engineering Units TCV3586SP Temp Control Valve 3586 Setpoint P3587PR Pump 3586 Pressure What I want to do is be able to use the small table as the criteria for a search of the main table. If I can search by 'Name' (unique field on both) and either just highlight the desired row or add a column to the main table to specify which are (1=yes, 0=no), or create a new worksheet with the data, whatever it takes. Thanks again in advance. "Dav" wrote: It is still not clear what you require In the small sheet you are trying to make a match with the master sheet Firstly are all the rows unique in the master sheet eg each name only appears once and you wish to return this information to the small sheet or do you wish to return all the rows matching the row in the small sheet? Give a small example say 3lines in the small sheet and 10 lines in the Master sheet (you do not need to list all the columns!) and describe what you wish to happen Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=563462 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup? Match? pulling rows from one spreadsheet to match a text f
Ok thats a bit clearer Insert a column in your main sheet, lets say column A If the field you want to match on is Name Which is in column B of the main sheet now, and column A of the small sheet Type selection in cell A1 of the main table then in a2 write =IF(ISNA(MATCH(b2,small!$A$1:$A$100,0)),"",1) then copy this helper colum all the way down to the bottom of your main sheet then goto cell a1 choose data filter auto filter Click on the arrow in cell A1 and just select the value 1 Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=563462 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Rows Automatically using a Text File List | Excel Discussion (Misc queries) | |||
Import only certain rows of a huge text file | Excel Discussion (Misc queries) | |||
advanced filter - can't match a long text cell | Excel Discussion (Misc queries) | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
double lookup, nest, or macro? | Excel Worksheet Functions |