Pull recent pricing data from table

[Pierre]

Have a pricing ordeal:

Prices are organized as follows on data tab:

Date Min Max Price
1/25/04 5 9 11.50
1/25/04 10 24 9.85
1/25/04 25 99 6.50
6/16/06 5 9 13.80
6/16/06 10 24 11.00
6/16/06 25 99 8.50
6/16/06 100 249 6.35


Am looking to create a table on a price tab, populating the most recent
dates and
all 4 price breaks(and the figure from the min column which can
change).

Result would be on price tab:

Date Qty Price
6/16/06 5 13.80
6/16/06 10 11.00
6/16/06 25 8.50
6/16/06 100 6.35

Note: Dates in the data tab are grouped in chronological order
by date, and ascending by quantity.


TIA for your interest and help.

Pierre

 

[Toppers]

I placed the "new" table in columns F to H on the same sheet and used the
following, starting in row 2:

F2: =LARGE($A$2:$A$8,1)
G2: =INDEX($B$2:$D$8,MATCH($F$2,$A$2:$A$8,0)+ROW()-2,1)
H2: =INDEX($B$2:$D$8,MATCH($F$2,$A$2:$A$8,0)+ROW()-2,3)

Copy down to F5

HTH

 

[Max]

This might suffice for what you have as set-up, and in mind ..

Assume source table in sheet: Data,
cols A to D, data from row2 down

In the sheet: Price,
assume you have the same col headers in A11

Put in E2: =IF(Data!$A$2="","",Data!A2-ROW()/10^10)
Copy E2 down as far as required to cover the max expected extent of source
dates (col A in Data). Eg: copy down to E1000 if source dates could populate
to that extent in Data.
(Leave E1 empty)

Then just place in A2:
=INDEX(Data!A:A,MATCH(LARGE($E:$E,ROW(A1)),$E:$E,0))
Copy A2 to D2, fill down 4 rows to return the required results
Format col A as date

 

[Max]

Just hide away the criteria col E if desired, for a neater look

 

[teelim]

Hi,

I am not too sure how exactly you want your spreadsheet to look like
but I hope this is what you have in mind...

hope it helps


+-------------------------------------------------------------------+
|Filename: recent price.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5118 |
+-------------------------------------------------------------------+

 

[Pierre]

Hi,

I am not too sure how exactly you want your spreadsheet to look like
but I hope this is what you have in mind...

hope it helps


+-------------------------------------------------------------------+
|Filename: recent price.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5118 |
+-------------------------------------------------------------------+

Thanks to those of you who have replied. Am working on the 3 solutions
for the best fit. Thanks again.

Pierre

 

[Max]

Thanks to those of you who have replied.
Am working on the 3 solutions for the best fit. Thanks again.

Pierre, thanks for the interim call-back.
Do drop us a line here which options worked for you,
and your preference/why. It doesn't matter if it isn't mine.

 

[Pierre]

Pierre, thanks for the interim call-back.
Do drop us a line here which options worked for you,
and your preference/why. It doesn't matter if it isn't mine.

Max, had collaborated and this is what we came up with:

=LARGE(Data!A:A,1) located in I21

=INDEX(Data!$E:$I,MATCH($I21,Data!$A:$A,0)+ROW()-ROW(),1)

=INDEX(Data!$E:$I,MATCH($I21,Data!$A:$A,0)+ROW()-ROW(),4) (4 columns
over)

Did the trick. Thanks to all.

Pierre

 

[Max]

Max, had collaborated and this is what we came up with:
=LARGE(Data!A:A,1) located in I21
=INDEX(Data!$E:$I,MATCH($I21,Data!$A:$A,0)+ROW()-ROW(),1)
=INDEX(Data!$E:$I,MATCH($I21,Data!$A:$A,0)+ROW()-ROW(),4) (4 columns
over)
Did the trick. Thanks to all.

Glad you got a solution up & running
Thanks for posting back ..