One way to set it up ..
In Sheet1
-------------
Assume data is in cols A to C,
from row2 down
Date Item Price
01-Dec-04 Sun1 $2.00
01-Dec-04 Sun2 $3.00
03-Dec-04 Sun1 $1.80
03-Dec-04 Sun3 $5.00
05-Dec-04 Sun2 $2.70
etc
Put in E2:
=IF(OR(COUNTIF($B$2:B2,B2)1,B2=""),"",ROW())
Copy E2 down by a safe "max" number of rows
that data is expected in cols A to C, down to say, E100?
(can copy down ahead of expected data input in cols A to C)
Col E will be used to drive out
a unique list of "Items" in Sheet2
In Sheet2
------------
Put in A1:C1 the headers:
Date, Item, Price (Best)
Select B2:B100
(a range of the same size as col E in Sheet1)
Put in the *formula bar*:
=IF(ISERROR(MATCH(SMALL(Sheet1!E:E,ROW(B1:B100)),S heet1!E:E,0)),"",INDEX(She
et1!B:B,MATCH(SMALL(Sheet1!E:E,ROW(B1:B100)),Sheet 1!E:E,0)))
Array-enter the formula with CTRL+SHIFT+ENTER
instead of just pressing ENTER
The above will return a unique list of Items
from Sheet1's col B
Now to populate col C ..
(i.e. extract the lowest prices for the unique items in col B)
Put in C2:
=IF(B2="","",MIN(IF(Sheet1!$B$2:$B$100=B2,Sheet1!$ C$2:$C$100)))
Array-enter the formula with CTRL+SHIFT+ENTER
Copy C2 down to C100
Format col C as currency
And finally to populate col A
(i.e. extract dates of lowest prices charged for the items) ..
Put in A2:
=IF(B2="","",INDEX(Sheet1!$A$2:$A$100,MATCH(1,(She et1!$B$2:$B$100=B2)*(Sheet
1!$C$2:$C$100=C2),0)))
Array-enter the formula with CTRL+SHIFT+ENTER
Copy A2 down to A100
Format col A as date
For the sample data in Sheet1,
you'll get the desired results in Sheet2:
Date Item Price (best)
03-Dec-04 Sun1 $1.80
05-Dec-04 Sun2 $2.70
03-Dec-04 Sun3 $5.00
Note that the set up will extract the first match
for the dates in col A, should there be duplicate
lowest prices charged for the same item in Sheet1
Adapt to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Jahaan" wrote in message
...
How can make a spread sheet for keeping a check on my best pricing that i
am
getting from my sundries supplier . he forgets what he chargres me so i
always have to waste time to reconcile with him. With a spread sheet i
want
to know when and what was he best prices to me.