Lookup? Match? pulling rows from one spreadsheet to match a text f  
Author Message
baum





PostPosted: Fri Jul 21 02:30:09 CDT 2006 Top

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.)

Excel158  
 
 
Dav





PostPosted: Fri Jul 21 02:30:09 CDT 2006 Top

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?action=getinfo&userid=27107
View this thread: http://www.excelforum.com/showthread.php?threadid=563462

 
 
cjax





PostPosted: Fri Jul 21 07:34:02 CDT 2006 Top

worksheet functions >> Lookup? Match? pulling rows from one spreadsheet to match a text f 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.


>
> 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?action=getinfo&userid=27107
> View this thread: http://www.excelforum.com/showthread.php?threadid=563462
>
>
 
 
Dav





PostPosted: Fri Jul 21 08:51:19 CDT 2006 Top

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 th
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 mai
sheet


then goto cell a1 choose data filter auto filter

Click on the arrow in cell A1 and just select the value 1

Regards

Da

--
Da
-----------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2710
View this thread: http://www.excelforum.com/showthread.php?threadid=56346