Pull recent pricing data from table

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

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


"Pierre" wrote:

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

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 A1:D1

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Pierre" wrote:
> 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
>
>

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 A1:D1

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Pierre" wrote:
> 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
>
>

Just hide away the criteria col E if desired, for a neater look
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

teelim wrote:
> 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 |
> +-------------------------------------------------------------------+
>
> --
> teelim
>


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

Pierre

"Pierre" wrote:
> 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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Max wrote:
> "Pierre" wrote:
> > 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.
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---

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

"Pierre" wrote:
> 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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---