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

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

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


------------------------------------------------------------------------
teelim's Profile: http://www.excelforum.com/member.php...o&userid=36856
View this thread: http://www.excelforum.com/showthread...hreadid=565607

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