automatic lookup and sort?

[nca77]

a snapshot of the first 9 rows data i have in A2:B6000 on sheet calle
"Data"

mnb 009806AE0
Cgx 014032DB9
Cgx 015554AT5
CLB 018033CX6
CLB 021289AB9
Cgf 048069CD8
Cgf 073177BV8
Cgf 090221Q88
mnb 091632AG4

then on another sheet called "Summary" i have "mnb" in cell A1

is there a formula that will lookup the huge list of data i have on th
"Data" sheet and spit out the corresponding CUSIP number (columnB)
only if the value in columnA matches cell A1 in summary sheet?

i was using =if(Data!A2=A1,Data!B2,"")

but that leaves a bunch of blank cells where Data!A2 doesn't = A1.

Is there a way to get them to list in order down on the "Summary" shee
in columnA?

Or is manually sorting the data the only way?

Thank

 

[Frank Kabel]

Hi
use
=VLOOKUP(A1,'Data!'$A1:$B$100,2,0)

 

[Anders S]

You may try an Advanced Filter. There are some good instructions here:

http://www.contextures.com/xladvfilter01.html

HTH
Anders Silven

 

[Dave Peterson]

I'd apply Data|filter|autofilter, then filter by the value I want. Then I could
copy the visible rows to the other worksheet.

The may not be close, but Debra Dalgleish has some sample workbooks that split
lists of data into separate workbooks (each worksheet is dedicated to one
"key"). If that's what you're doing, you may want to steal some code from Deb's
site:
http://www.contextures.com/excelfiles.html

Look for:

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- creates a list of unique items,
creates a sheet for each item, then replaces old data with current.
AdvFilterCity.xls 46 kb

and

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[nca77]

Thanks for the response.. just another question:

Is there a formula that works like an Advanced Filter bu
automatically?

So I can just change cell A1 on the Summary sheet from mnb to Cgx.. th
rows beneath will automatically switch to the "Cgx" data?

I dont think selcecting all the data and running an Advanced Filte
will be a bit too much work for my analysis.

This probably cant be done but you never know.

Maybe a drop down list would help?

I'm trying to stay away from Macros.

Thanks
Nic

 

[Dave Peterson]

There's no formula I know that would do this (but there are some really smart
formula people across the street in .worksheet.functions). You may want to
explain your problem and ask for suggestions there.

But there are event macros that could maybe automate the task when you changed
values.