lookup value and date from a list with restrictions

[antonio]

Hello,

I have the following problem.

From a Data list that has a large amount of information registrated,

such as:

Stock Date Value Operation

A 20-jan-07 100 buy
B 25-jan-07 90 sell
A 5-feb-07 150 buy
C 10-feb-07 200 sell
A 5-Mar-07 200 buy

I need to recover from the Data list all information available about
Stock A.

What do you recommend?

If I use the formula {INDEX (date, MATCH (A, stock, 0))} then the
date 20-jan-07 would appear
If I use the formula {INDEX (value, MATCH (A, stock, 0))} then tha
value 100 would appear.

The problem is, how do I write a formula that finds the rest of the
information availlable of Stock A.

Previously I posted a similar question with two results.
One suggested using Autofilter. For a manual solution thats ok, but
this must be automatic. The Data list has a large amount of registers
that grow on a daily basis.
The second suggested using = LOOKUP (2, 1/(stock="A"), Value).
I don't understand how this works, allow it does find the last value
for A, but not the second. Please consider that more than three
registers for A might exist. This is only an example.

Thank you for the help,

avializq

 

[Guest]

Try Auto Filter

Hello,

I have the following problem.

such as:

Stock Date Value Operation

A 20-jan-07 100 buy
B 25-jan-07 90 sell
A 5-feb-07 150 buy
C 10-feb-07 200 sell
A 5-Mar-07 200 buy

I need to recover from the Data list all information available about
Stock A.

What do you recommend?

If I use the formula {INDEX (date, MATCH (A, stock, 0))} then the
date 20-jan-07 would appear
If I use the formula {INDEX (value, MATCH (A, stock, 0))} then tha
value 100 would appear.

The problem is, how do I write a formula that finds the rest of the
information availlable of Stock A.

Previously I posted a similar question with two results.
One suggested using Autofilter. For a manual solution thats ok, but
this must be automatic. The Data list has a large amount of registers
that grow on a daily basis.
The second suggested using = LOOKUP (2, 1/(stock="A"), Value).
I don't understand how this works, allow it does find the last value
for A, but not the second. Please consider that more than three
registers for A might exist. This is only an example.

Thank you for the help,

avializq

 

[antonio]

Try Auto Filter

TM,
I understand that Auto Filter helps, but I'm looking for a formula. My
Data list has too many records, and I need to look up many different
field matches.
avializq

 

[T. Valko]

How many rows of data are there? If there are 1000's then Autofilter is your
best option.

Biff

 

[antonio]

How many rows of data are there? If there are 1000's then Autofilter is your
best option.

Biff

Biff,
The amount of rows grow daily. Eventually, there will be 1000 rows,
but the amount of rows that meet the criteria (for example Stock A)
should not exceed 10.
The problem I see about using auto filter is that there are many
different stocks and they change in time.
avializq

 

[T. Valko]

Biff,
The amount of rows grow daily. Eventually, there will be 1000 rows,
but the amount of rows that meet the criteria (for example Stock A)
should not exceed 10.
The problem I see about using auto filter is that there are many
different stocks and they change in time.
avializq

Here's a sample file that demonstates this:

Delete_Me.xls 18kb

http://cjoint.com/?gffLHM0ch3

Enter a stock symbol in cell G2 and the bordered table will fill in.

I've used a few defined names. To see these goto the menu
Insert>Name>Define.

I entered the array formula** in cell H2 then copied across to J2 then down
to row 11.

As you add new data to the stock table the ranges will automatically adjust.
This is based on the assumption that your stock table will be a contiguous
block with no empty cells within. You'll have to see if this impacts the
performance of your file.

You said that there should only be about 10 entries that meet the criteria
so I've copied the formula to only 10 rows.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

P.S. - that file name is for my own use! I have 100's of sample files and
trying to come up with new unique names is a challenge. When I post sample
files they're unique and specific to the subject of the post. After a few
days I delete them so the file name lets me know which files I can delete.

Biff

 

[antonio]

Here's a sample file that demonstates this:

Delete_Me.xls 18kb

http://cjoint.com/?gffLHM0ch3

Enter a stock symbol in cell G2 and the bordered table will fill in.

I've used a few defined names. To see these goto the menu
Insert>Name>Define.

I entered the array formula** in cell H2 then copied across to J2 then down
to row 11.

As you add new data to the stock table the ranges will automatically adjust.
This is based on the assumption that your stock table will be a contiguous
block with no empty cells within. You'll have to see if this impacts the
performance of your file.

You said that there should only be about 10 entries that meet the criteria
so I've copied the formula to only 10 rows.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

P.S. - that file name is for my own use! I have 100's of sample files and
trying to come up with new unique names is a challenge. When I post sample
files they're unique and specific to the subject of the post. After a few
days I delete them so the file name lets me know which files I can delete.

Biff

Biff,

Thank you very much. That is what I was looking for.

I took the time to write out and analyse the formulas you used, and it
wasn't easy. I think I understand how most of the formulas work,
except for your definitions of Row, Stock and Table. Could you explain
these?

Thank you again for your time and effort,

avializq

 

[T. Valko]

Biff,

Thank you very much. That is what I was looking for.

I took the time to write out and analyse the formulas you used, and it
wasn't easy. I think I understand how most of the formulas work,
except for your definitions of Row, Stock and Table. Could you explain
these?

Thank you again for your time and effort,

avializq

All of those named formulas are used to define a dynamic range.

Table refers to the actual data that you want to extract.

Stock refers to the range of stock symbols. Stock is the "key" column It's
assumed that for every entry is Stock there will be data in the other
columns. So we use the "key" column to get the total number of rows of data
in the entire table instead of counting the rows in every column.

Row generates an array of numbers from 1 to n. n = the number of rows in
Stock. This is used in the INDEX function to tell it which values to
extract.

Biff