Tabulating the cost of the product in past 5 yrs

L

lsc

It's very difficult to tabulate the following cost
increases for above 500 products
Product Date Increase cost $
A 3/1/97 1.00
A 5/1/97 1.50
A 8/1/98 2.00
A 3/1/00 2.25
A 2/1/01 2.50
A 2/1/02 3.50
B 2/1/97 0.50
B 2/1/98 1.00
B 3/1/99 1.25
B 5/1/99 1.70
B 4/1/00 2.05
B 5/1/00 2.50
B 2/1/01 2.75
Is there a way to tabulate the cost for past 5 yrs by
monthly based on calender year (form Jan'98 till Dec'02),
however for month of Sept should appear twice. Total
should be 65 months.

The format should look like this

Product Jan'98 Feb'98...Jul'98 Aug'98 Sept'98 Sept'98....
A 1.50 1.50 1.50 2.00 2.00 2.00
 
M

Max

Here's one crack at it.

Assume the sample cost increase table below is in B1:D14 of Sheet1 (say)
[Note: the dates in the Date col are assumed
to be in sorted ascending order (m/d/yy )]

Product Date Increase cost $
A 3/1/97 1.00
A 5/1/97 1.50
A 8/1/98 2.00
A 3/1/00 2.25
A 2/1/01 2.50
A 2/1/02 3.50
B 2/1/97 0.50
B 2/1/98 1.00
B 3/1/99 1.25
B 5/1/99 1.70
B 4/1/00 2.05
B 5/1/00 2.50
B 2/1/01 2.75

Put in A2: =TRIM(B2&C2) >> this should return "A35490" in A2
Copy down A2:A14

Name the range A2:D14 as : TBL


In a separate Sheet2:

Put in B2: 31-Jan-98
Put in C2: 28-Feb-98

Select B2:C2
Copy across B2:BJ2
(this will fill-in 31-Mar-98 in D2, 30-Apr-98 in E2, .. 31-Dec-02 in BJ2)

Insert cols to the left of each of the 4 Oct months
for the repeat Sep months, for example:

Click on K2 (shows: 31-Oct-98)
Click Insert > Col
Put in "new" K2: =J2

Do similar steps to insert the repeats for the other 3 Sep months

The completed set-up for row 2 will be in B2:BN2 (total: 65 cols, as you
stated)

Put in A3: A
Put in A4: B
(A3 downwards will contain the product names)

Put in B3
: =VLOOKUP(TRIM($A3)&DATEVALUE(TEXT(B$2,"dd-mmm-yy")),TBL,4,TRUE)

Copy across B3:BN3, then copy down B3:BN4 (i.e. the last row of product)

Use B1:BN1 to fill-in the col labels: Jan-98, Feb-98 . Dec 02

Hide row 2
 
M

Max demechanik

hi, i responded via OE 12 hours ago
but it didn't appear here in cdo...
response attached below, give it a try..


Here's one crack at it.

Assume the sample cost increase table below is in B1:D14
of Sheet1 (say)
[Note: the dates in the Date col are assumed
to be in sorted ascending order (m/d/yy )]

Product Date Increase cost $
A 3/1/97 1.00
A 5/1/97 1.50
A 8/1/98 2.00
A 3/1/00 2.25
A 2/1/01 2.50
A 2/1/02 3.50
B 2/1/97 0.50
B 2/1/98 1.00
B 3/1/99 1.25
B 5/1/99 1.70
B 4/1/00 2.05
B 5/1/00 2.50
B 2/1/01 2.75

Put in A2: =TRIM(B2&C2) >> this should return "A35490" in
A2

Copy down A2:A14

Name the range A2:D14 as : TBL


In a separate Sheet2:

Put in B2: 31-Jan-98
Put in C2: 28-Feb-98

Select B2:C2
Copy across B2:BJ2
(this will fill-in 31-Mar-98 in D2, 30-Apr-98 in E2, .. 31-
Dec-02 in BJ2)

Insert cols to the left of each of the 4 Oct months
for the repeat Sep months, for example:

Click on K2 (shows: 31-Oct-98)
Click Insert > Col
Put in "new" K2: =J2

Do similar steps to insert the repeats for the other 3 Sep
months

The completed set-up for row 2 will be in B2:BN2 (total:
65 cols, as you
stated)

Put in A3: A
Put in A4: B
(A3 downwards will contain the product names)

Put in B3
: =VLOOKUP(TRIM($A3)&DATEVALUE(TEXT(B$2,"dd-mmm-
yy")),TBL,4,TRUE)

Copy across B3:BN3, then copy down B3:BN4 (i.e. the last
row of product)

Use B1:BN1 to fill-in the col labels: Jan-98, Feb-98 . Dec
02

Hide row 2
 
L

lscheng

Hi ,
Thanks for the advise and the formula work greatly.

Another question,

If a new product call "Z" just release in the Market after
Apr'98, how to tabulate the cost price "0" in month of
Jan'98, Feb'98 and Mar'03. Really appreciate your inputs
ASAP.
Thank you very much.
-----Original Message-----
hi, i responded via OE 12 hours ago
but it didn't appear here in cdo...
response attached below, give it a try..


Here's one crack at it.

Assume the sample cost increase table below is in B1:D14
of Sheet1 (say)
[Note: the dates in the Date col are assumed
to be in sorted ascending order (m/d/yy )]

Product Date Increase cost $
A 3/1/97 1.00
A 5/1/97 1.50
A 8/1/98 2.00
A 3/1/00 2.25
A 2/1/01 2.50
A 2/1/02 3.50
B 2/1/97 0.50
B 2/1/98 1.00
B 3/1/99 1.25
B 5/1/99 1.70
B 4/1/00 2.05
B 5/1/00 2.50
B 2/1/01 2.75

Put in A2: =TRIM(B2&C2) >> this should return "A35490" in
A2

Copy down A2:A14

Name the range A2:D14 as : TBL


In a separate Sheet2:

Put in B2: 31-Jan-98
Put in C2: 28-Feb-98

Select B2:C2
Copy across B2:BJ2
(this will fill-in 31-Mar-98 in D2, 30-Apr-98 in E2, .. 31-
Dec-02 in BJ2)

Insert cols to the left of each of the 4 Oct months
for the repeat Sep months, for example:

Click on K2 (shows: 31-Oct-98)
Click Insert > Col
Put in "new" K2: =J2

Do similar steps to insert the repeats for the other 3 Sep
months

The completed set-up for row 2 will be in B2:BN2 (total:
65 cols, as you
stated)

Put in A3: A
Put in A4: B
(A3 downwards will contain the product names)

Put in B3
: =VLOOKUP(TRIM($A3)&DATEVALUE(TEXT(B$2,"dd-mmm-
yy")),TBL,4,TRUE)

Copy across B3:BN3, then copy down B3:BN4 (i.e. the last
row of product)

Use B1:BN1 to fill-in the col labels: Jan-98, Feb-98 . Dec
02

Hide row 2
------------------
Adapt above accordingly to suit your data


lsc said:
It's very difficult to tabulate the following cost
increases for above 500 products
Product Date Increase cost $
A 3/1/97 1.00
A 5/1/97 1.50
A 8/1/98 2.00
A 3/1/00 2.25
A 2/1/01 2.50
A 2/1/02 3.50
B 2/1/97 0.50
B 2/1/98 1.00
B 3/1/99 1.25
B 5/1/99 1.70
B 4/1/00 2.05
B 5/1/00 2.50
B 2/1/01 2.75
Is there a way to tabulate the cost for past 5 yrs by
monthly based on calender year (form Jan'98 till Dec'02),
however for month of Sept should appear twice. Total
should be 65 months.

The format should look like this

Product Jan'98 Feb'98...Jul'98 Aug'98 Sept'98 Sept'98....
A 1.50 1.50 1.50 2.00 2.00 2.00
Click to expand...

.
Click to expand...
 
M

Max

Go to the range: TBL (in sheet1)

a. Insert a new row just above the initial line for the new product Z
b. Copy the TRIM formula down in the 1st col to cover this new row
c. Type a "-" in the cell under the product col

(See illustration below)

Product Date Increase cost $
X 3/1/97 1.00
X 5/1/97 1.50
Y 8/1/98 2.00
Y 3/1/00 2.25
- << Insert row,copy TRIM down (1st col),type
"-" under Prod. col
Z 4/1/98 3.50
Z 7/1/98 0.50

The vlookup formula will now return '0'
for the months of Jan'98, Feb'98 and Mar'03

Hi ,
Thanks for the advise and the formula work greatly.

Another question,

If a new product call "Z" just release in the Market after
Apr'98, how to tabulate the cost price "0" in month of
Jan'98, Feb'98 and Mar'03. Really appreciate your inputs
ASAP.
Thank you very much.
-----Original Message-----
hi, i responded via OE 12 hours ago
but it didn't appear here in cdo...
response attached below, give it a try..


Here's one crack at it.

Assume the sample cost increase table below is in B1:D14
of Sheet1 (say)
[Note: the dates in the Date col are assumed
to be in sorted ascending order (m/d/yy )]

Product Date Increase cost $
A 3/1/97 1.00
A 5/1/97 1.50
A 8/1/98 2.00
A 3/1/00 2.25
A 2/1/01 2.50
A 2/1/02 3.50
B 2/1/97 0.50
B 2/1/98 1.00
B 3/1/99 1.25
B 5/1/99 1.70
B 4/1/00 2.05
B 5/1/00 2.50
B 2/1/01 2.75

Put in A2: =TRIM(B2&C2) >> this should return "A35490" in
A2

Copy down A2:A14

Name the range A2:D14 as : TBL


In a separate Sheet2:

Put in B2: 31-Jan-98
Put in C2: 28-Feb-98

Select B2:C2
Copy across B2:BJ2
(this will fill-in 31-Mar-98 in D2, 30-Apr-98 in E2, .. 31-
Dec-02 in BJ2)

Insert cols to the left of each of the 4 Oct months
for the repeat Sep months, for example:

Click on K2 (shows: 31-Oct-98)
Click Insert > Col
Put in "new" K2: =J2

Do similar steps to insert the repeats for the other 3 Sep
months

The completed set-up for row 2 will be in B2:BN2 (total:
65 cols, as you
stated)

Put in A3: A
Put in A4: B
(A3 downwards will contain the product names)

Put in B3
: =VLOOKUP(TRIM($A3)&DATEVALUE(TEXT(B$2,"dd-mmm-
yy")),TBL,4,TRUE)

Copy across B3:BN3, then copy down B3:BN4 (i.e. the last
row of product)

Use B1:BN1 to fill-in the col labels: Jan-98, Feb-98 . Dec
02

Hide row 2
------------------
Adapt above accordingly to suit your data


lsc said:
It's very difficult to tabulate the following cost
increases for above 500 products
Product Date Increase cost $
A 3/1/97 1.00
A 5/1/97 1.50
A 8/1/98 2.00
A 3/1/00 2.25
A 2/1/01 2.50
A 2/1/02 3.50
B 2/1/97 0.50
B 2/1/98 1.00
B 3/1/99 1.25
B 5/1/99 1.70
B 4/1/00 2.05
B 5/1/00 2.50
B 2/1/01 2.75
Is there a way to tabulate the cost for past 5 yrs by
monthly based on calender year (form Jan'98 till Dec'02),
however for month of Sept should appear twice. Total
should be 65 months.

The format should look like this

Product Jan'98 Feb'98...Jul'98 Aug'98 Sept'98 Sept'98....
A 1.50 1.50 1.50 2.00 2.00 2.00
Click to expand...

.
Click to expand...
Click to expand...
 
L

lscheng

Hi Max, (I think this sholud be your name)

It's work. Thank you.
I sugguest, it is possible not to do manually insert a
row to put in "-" as I have 500 products with 60k of rows.

For exmpale,
Product Date Increase cost $
A 3/1/97 1.00
A 5/1/97 1.50
A 8/1/98 2.00
A 3/1/00 2.25
A 2/1/01 2.50
A 2/1/02 3.50
B 2/1/97 0.50
B 2/1/98 1.00
B 3/1/99 1.25
B 5/1/99 1.70
B 4/1/00 2.05
B 5/1/00 2.50
B 2/1/01 2.75
Z 4/1/98 0.05 Release in Mkt on 1st Apr'98
Z 12/1/98 0.10
Z 5/1/99 0.50
Z 7/1/00 1.50
Z 9/16/01 1.75
Z 2/01/03 1.80
I have tried it couple of times with your 1st formula and
it keep on tabulated 2.75 which is from Product B instead
it should be 0 in Jan'98 till Mar'98. Pls help......

Once again, thanks for your help
 
L

lscheng

Pls Help.....
-----Original Message-----
Hi Max, (I think this sholud be your name)

It's work. Thank you.
I sugguest, it is possible not to do manually insert a
row to put in "-" as I have 500 products with 60k of rows.

For exmpale,
Product Date Increase cost $
A 3/1/97 1.00
A 5/1/97 1.50
A 8/1/98 2.00
A 3/1/00 2.25
A 2/1/01 2.50
A 2/1/02 3.50
B 2/1/97 0.50
B 2/1/98 1.00
B 3/1/99 1.25
B 5/1/99 1.70
B 4/1/00 2.05
B 5/1/00 2.50
B 2/1/01 2.75
Z 4/1/98 0.05 Release in Mkt on 1st Apr'98
Z 12/1/98 0.10
Z 5/1/99 0.50
Z 7/1/00 1.50
Z 9/16/01 1.75
Z 2/01/03 1.80
I have tried it couple of times with your 1st formula and
it keep on tabulated 2.75 which is from Product B instead
it should be 0 in Jan'98 till Mar'98. Pls help......

Once again, thanks for your help


.
Click to expand...
 
M

Max

hi, much as i would like to, afraid i'm pretty much out of ideas
on how to proceed further, given your actual data arrangement
as you have explained.

the vlookup formula given using TRUE as the 4th param
is doing its job but because of the data arrangement, where
the data is not entirely consistent across all products
- for example: "new" products would not have price data
before Jan-98 - it causes the "wrong" prices to be returned.

perhaps you could put in a fresh post for others to offer alternative views

good luck!

cheers
Max
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Retuyrning a single entry when multiples of the same 3
Add numbers with a DATE as one of the conditions 5
use a function to look up a result in a spreadsheet 4
Sales with quarter and annual totals 1
SUMIF and YEAR 13
sum of info in a range based on dates 4
returns calculation using sumproduct 6
lookup date in the interval 4

Top