Discerning data by the date
 

I wish to know how to have Excel discern different rows by the date.

Sheet1 (Columns)
-Date
-Part #
-Cost

Sheet2(Columns
-Part#
-2006
-2007
-2008

EX. Sheet1

Date Part# Cost
10/20/2006 12-DFC-12 $6.00
10/20/2008 12-DFC-12 $6.85
10/21/2007 12-DFC-12 $7.00

Based on the above information, I want Sheet2 to look like

Part# 2006 2007 2008
12-DFC-12 6.00 7.00 6.85

The problem being that we constantly add more to Sheet1 and it's sorted
ascending. I want it to be able to find the right part, figure out which
date goes under which column, and post the right cost in the right place.

Any ideas on how to do this?

Andrew Murray

Discerning data by the date
 

Assuming your formulas start in Sheet2!B2 (col A is the part list and
B1:D1 are the years), in Sheet2!B2:

=SUMPRODUCT((Sheet1!$B$2:$B$100=$A2)*(YEAR(Sheet1! $A$2:$A$100=B$1))

Copy down and across to cols B:D.
As to Sheet2 column A:A, parts list, do you have it ready?

HTH
Kostis Vezerides


On Jan 25, 8:12 pm, Andrew M.
wrote:
I wish to know how to have Excel discern different rows by the date.

Sheet1 (Columns)
-Date
-Part #
-Cost

Sheet2(Columns
-Part#
-2006
-2007
-2008

EX. Sheet1

Date Part# Cost
10/20/2006 12-DFC-12 $6.00
10/20/2008 12-DFC-12 $6.85
10/21/2007 12-DFC-12 $7.00

Based on the above information, I want Sheet2 to look like

Part# 2006 2007 2008
12-DFC-12 6.00 7.00 6.85

The problem being that we constantly add more to Sheet1 and it's sorted
ascending. I want it to be able to find the right part, figure out which
date goes under which column, and post the right cost in the right place.

Any ideas on how to do this?

Andrew Murray

Discerning data by the date
 

In Sheet1 beginning in A1 I have you data
10/20/2006 12-DFC-12 $6.00
10/20/2008 12-DFC-12 $6.85
10/21/2007 12-DFC-12 $7.00
But I add in D1 the formula =YEAR(A1)&B1 and copy this down the column

On Sheet2 beginning in A1 I have
Part# 2006 2007 2008
12-DFC-12 6.00 7.00 6.85

The formula in B2 that returns the value of 6.00 is
=INDEX(Sheet1!$C$1:$C$3,MATCH(B$1&$A2,Sheet1!$D$1: $D$3,0))

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Andrew M." wrote in message
...
I wish to know how to have Excel discern different rows by the date.

Sheet1 (Columns)
-Date
-Part #
-Cost

Sheet2(Columns
-Part#
-2006
-2007
-2008

EX. Sheet1

Date Part# Cost
10/20/2006 12-DFC-12 $6.00
10/20/2008 12-DFC-12 $6.85
10/21/2007 12-DFC-12 $7.00

Based on the above information, I want Sheet2 to look like

Part# 2006 2007 2008
12-DFC-12 6.00 7.00 6.85

The problem being that we constantly add more to Sheet1 and it's sorted
ascending. I want it to be able to find the right part, figure out which
date goes under which column, and post the right cost in the right place.

Any ideas on how to do this?

Andrew Murray

Discerning data by the date
 

Thanks alot, Bernard. Your help was easy to understand and did what i needed
it to.

"Bernard Liengme" wrote:

In Sheet1 beginning in A1 I have you data
10/20/2006 12-DFC-12 $6.00
10/20/2008 12-DFC-12 $6.85
10/21/2007 12-DFC-12 $7.00
But I add in D1 the formula =YEAR(A1)&B1 and copy this down the column

On Sheet2 beginning in A1 I have
Part# 2006 2007 2008
12-DFC-12 6.00 7.00 6.85

The formula in B2 that returns the value of 6.00 is
=INDEX(Sheet1!$C$1:$C$3,MATCH(B$1&$A2,Sheet1!$D$1: $D$3,0))

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Andrew M." wrote in message
...
I wish to know how to have Excel discern different rows by the date.

Sheet1 (Columns)
-Date
-Part #
-Cost

Sheet2(Columns
-Part#
-2006
-2007
-2008

EX. Sheet1

Date Part# Cost
10/20/2006 12-DFC-12 $6.00
10/20/2008 12-DFC-12 $6.85
10/21/2007 12-DFC-12 $7.00

Based on the above information, I want Sheet2 to look like

Part# 2006 2007 2008
12-DFC-12 6.00 7.00 6.85

The problem being that we constantly add more to Sheet1 and it's sorted
ascending. I want it to be able to find the right part, figure out which
date goes under which column, and post the right cost in the right place.

Any ideas on how to do this?

Andrew Murray

Discerning data by the date
 

Thanks for feedback
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Andrew M." wrote in message
...
Thanks alot, Bernard. Your help was easy to understand and did what i
needed
it to.

"Bernard Liengme" wrote:

In Sheet1 beginning in A1 I have you data
10/20/2006 12-DFC-12 $6.00
10/20/2008 12-DFC-12 $6.85
10/21/2007 12-DFC-12 $7.00
But I add in D1 the formula =YEAR(A1)&B1 and copy this down the column

On Sheet2 beginning in A1 I have
Part# 2006 2007 2008
12-DFC-12 6.00 7.00 6.85

The formula in B2 that returns the value of 6.00 is
=INDEX(Sheet1!$C$1:$C$3,MATCH(B$1&$A2,Sheet1!$D$1: $D$3,0))

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Andrew M." wrote in message
...
I wish to know how to have Excel discern different rows by the date.

Sheet1 (Columns)
-Date
-Part #
-Cost

Sheet2(Columns
-Part#
-2006
-2007
-2008

EX. Sheet1

Date Part# Cost
10/20/2006 12-DFC-12 $6.00
10/20/2008 12-DFC-12 $6.85
10/21/2007 12-DFC-12 $7.00

Based on the above information, I want Sheet2 to look like

Part# 2006 2007 2008
12-DFC-12 6.00 7.00 6.85

The problem being that we constantly add more to Sheet1 and it's sorted
ascending. I want it to be able to find the right part, figure out
which
date goes under which column, and post the right cost in the right
place.

Any ideas on how to do this?

Andrew Murray