Home |
Search |
Today's Posts |
#1
|
|||
|
|||
spreadsheet for sundries with option for better and best pricing
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. |
#2
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|