Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro | Excel Discussion (Misc queries) | |||
Sum data by date range | Excel Discussion (Misc queries) | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Sort pages? | Excel Discussion (Misc queries) | |||
Extracting data from the current date | Excel Worksheet Functions |