Pulling data from list into month/year table

[EC11]

Hey Guys,

I am trying to create a table (years on top, months on side) to track my utility meter data. I have a lot of data with random start and end dates and i've been trying to get the data ("Average daily consumption normalized for calender" column) to simply go into the cell that applies to it (example: if I had a meter start date of august 10th 2007, I would want that to pull from the list into the table cell for august 2007.) The main problem I'm having is getting it to recognize the years and format it correctly. At first this seemed an easy task, but I've given it a few days work, tried many things, and not much has proven successful. Below are a few images of what i'm looking at and trying to accomplish. One is of the list I'm trying to pull from, and the other is the table i'm trying to put the data into. I would appreciate any help or advice. Thank you. expirement table 1.jpgexpirement table 2.JPG

[Tony Valko]

Ok, we can do this...

However, I'm not about to try to recreate your data so you're going to have to post a SMALL sample file that we can work on.

SMALL = about 20 rows worth of data

[EC11]

Thanks Tony,

Here is a small sample of what i'm looking to transfer into the table:


A1-Start Date B1-End Date C1-Usage/Quantity D1-Cost ($) E1-Days in Billing Period F1-Average Daily Use G1-Days in Month H1-calcs I1-Avg Daily Consumption normalized for calender month J1-ave monthly consumption
5/16/2007 6/15/2007 159600 27856.76 30 5,320 31 329,600 5,403 167,502
6/15/2007 7/18/2007 170000 29230.46 33 5,152 30 334,800 5,489 164,656
7/18/2007 8/16/2007 164800 28457.22 29 5,683 31 340,800 5,497 170,400
8/16/2007 9/17/2007 176000 29917.64 32 5,500 31 328,400 5,384 166,892
9/17/2007 10/15/2007 152400 26532.58 28 5,443 30 305,200 5,003 150,098
10/15/2007 11/13/2007 152800 23106.65 29 5,269 31 305,600 5,010 155,305
11/13/2007 12/14/2007 152800 18589.29 31 4,929 30 279,200 4,577 137,311
12/14/2007 1/14/2008 126400 18589.29 31 4,077 31 260,400 4,200 130,200
1/14/2008 2/12/2008 134000 15557.65 29 4,621 31 280,800 4,680 145,080
2/12/2008 3/13/2008 146800 17462.94 30 4,893 29 284,800 4,747 137,653
3/13/2008 4/11/2008 138000 16661.38 29 4,759 31 286,000 4,689 145,344
4/11/2008 5/13/2008 148000 20207.19 32 4,625 30 307,200 5,036 151,082
5/13/2008 6/12/2008 159200 25795.07 30 5,307 31 318,800 5,226 162,013
6/12/2008 7/14/2008 159600 25776.14 32 4,988 30 311,600 5,108 153,246
7/14/2008 8/12/2008 152000 24805.9 29 5,241 31 311,200 5,019 155,600
8/12/2008 9/11/2008 159200 25761.21 30 5,307 31 308,800 5,062 156,931
9/11/2008 10/10/2008 149600 25046.08 29 5,159 30 298,800 4,898 146,951
10/10/2008 11/10/2008 149200 23702.76 31 4,813 31 286,400 4,695 145,548


What i'm looking to transfer into looks like:

Average Daily Use Year
Calendar Month 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016
1
2
3
4
5
6
7
8
9
10
11
12
Complete Year Total Consumption 0 0 0 0 0 0 0 0 0 0

[Tony Valko]

Which column do we want to pull the data from? Column F?

[EC11]

Column I- "Avg Daily Consumption normalized for calender month"

[EC11]

Start Date End Date Avg Daily Consumption normalized for calender month
5/16/2007 6/15/2007 5,403
6/15/2007 7/18/2007 5,489
7/18/2007 8/16/2007 5,497
8/16/2007 9/17/2007 5,384
9/17/2007 10/15/2007 5,003
10/15/2007 11/13/2007 5,010
11/13/2007 12/14/2007 4,577
12/14/2007 1/14/2008 4,200
1/14/2008 2/12/2008 4,680
2/12/2008 3/13/2008 4,747
3/13/2008 4/11/2008 4,689
4/11/2008 5/13/2008 5,036
5/13/2008 6/12/2008 5,226
6/12/2008 7/14/2008 5,108
7/14/2008 8/12/2008 5,019
8/12/2008 9/11/2008 5,062
9/11/2008 10/10/2008 4,898
10/10/2008 11/10/2008 4,695


Columns A, B, and I for your convenience

[Tony Valko]

Try this...

Data on Sheet1 in the range A2:J19


Sheet2

	A	B	C	D	E	F	G	H	I	J	K
1	Month	2007	2008	2009	2010	2011	2012	2013	2014	2015	2016
2	1	0	4680	0	0	0	0	0	0	0	0
3	2	0	4747	0	0	0	0	0	0	0	0
4	3	0	4689	0	0	0	0	0	0	0	0
5	4	0	5036	0	0	0	0	0	0	0	0
6	5	5403	5226	0	0	0	0	0	0	0	0
7	6	5489	5108	0	0	0	0	0	0	0	0
8	7	5497	5019	0	0	0	0	0	0	0	0
9	8	5384	5062	0	0	0	0	0	0	0	0
10	9	5003	4898	0	0	0	0	0	0	0	0
11	10	5010	4695	0	0	0	0	0	0	0	0
12	11	4577	0	0	0	0	0	0	0	0	0
13	12	4200	0	0	0	0	0	0	0	0	0

This formula entered in B2:

=SUMPRODUCT(--(TEXT(Sheet1!$A$2:$A$19,"myyyy")=$A2&B$1),Sheet1!$I$2:$I$19)

Copy down to B13 then across to K2:K13.

[EC11]

Yes, it worked. Thank you very much.

[Tony Valko]

You're welcome. Thanks for the feedback!