Transpose Dates and Corresponding Data

[dwgnome]

I have a table that is set up with actual monthly dates from D5 through AC15 (defaults to first day of month).
Data is at various rows beneath from D6:D9 through AC6:AC9.

I want to create a second lookup table that will transpose the dates and associated data in vertical format listing the transposed dates (my month). However I want to only transpose those 12 months based on the year selected in AD5. The header in AD5 is a drop down for “Total 2015”, “Total 2016” etc. So I only need the numeric year portion of the text string. I thought it would be easy to isolate the year portion and match to the header rows, but I was unable to do that.

For the transposed dates, I tried using the following to create the months:

[FONT=Calibri][COLOR=#0000cd]AV6 = INDEX($P$5:$AA6,1,ROW()-5[/COLOR][COLOR=#000000])[/COLOR][/FONT]

drag down.

It works if I force the range just right (ie., only the months in 2016). In this case starting with P5 = 1/1/2016.

The adjacent cell starting in AJ6 grabs the corresponding data:

AJ6 =INDEX($D$8:$AC$8,MATCH(AI6,$D$5:$AC$5,0))

which works fine.

So I need the lookup table (with transposed dates) to be flexible to allow the user to pick any year and have all of the months in that year transposed and its corresponding data in adjacent columns.

Excel 2010.

Excel 2010

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
5	Customer	Metrics	Jan 2015	Feb 2015	Mar 2015	Apr 2015	May 2015	Jun 2015	Jul 2015	Aug 2015	Sep 2015	Oct 2015	Nov 2015	Dec 2015	Jan 2016	Feb 2016	Mar 2016	Apr 2016	May 2016	Jun 2016	Jul 2016	Aug 2016	Sep 2016	Oct 2016	Nov 2016	Dec 2016	Jan 2017	Feb 2017	Total 2016
6	Anderson	Sector 1	45	76	59	57	58	57	59	60	49	53	55	58	57	53	56	54	54	17	52	54	17	55	53	53	17	45	575
7	Kirkpatrick	Sector 1	21	63	51	49	51	39	36	50	51	57	67	67	65	62	62	53	45	40	46	43	50	62		41	34	32	569
8	Johnson	Sector 2	23	29	34	35	36	31	29	27	27	26	34	41	39	32	34	31	32	32	32	28	29	30	32	43	46	92	394
9	Kiefer	Sector 2	31	15	23	27	22	11	6	4	5	17	45	56	30	18	23	22	21	12	14	15	7	8	13	39	42	14	222

<tbody>
</tbody>

ACU Data​

Worksheet Formulas Cell Formula AD6 =SUMIFS($D6:$AC6,$D$5:$AC$5,">="&DATE(RIGHT($AD$5,4),1,1),$D$5:$AC$5,"<="&DATE(RIGHT($AD$5,4),12,31)) <tbody> </tbody>

<tbody>
</tbody>

Excel 2010

	AI	AJ	AK
5	Date	Johnson	Kiefer
6	Jan 2016	39	30.0
7	Feb 2016	32	17.9
8	Mar 2016	34	23.0
9	Apr 2016	31	21.7
10	May 2016	32	20.6
11	Jun 2016	32	12.2
12	Jul 2016	32	14.5
13	Aug 2016	28	15.4
14	Sep 2016	29	7.0
15	Oct 2016	30	7.6
16	Nov 2016	32	12.9
17	Dec 2016	43	39.5

<tbody>
</tbody>

ACU Data​

Worksheet Formulas Cell Formula AI6 =INDEX($P$5:$AA6,1,ROW()-5) AJ6 =INDEX($D$8:$AC$8,MATCH(AI6,$D$5:$AC$5,0)) AK6 =INDEX($D$9:$AC$9,MATCH(AI6,$D$5:$AC$5,0)) <tbody> </tbody>

<tbody>
</tbody>

 

[XLRATOR]

Good morning,
Before trying to develop anything in VBA, I've always made sure it does not exist in Excel, for the whole thing you are trying to do, a Pivot Table would do a great job in no time.

Regards.

 

[dwgnome]

Not good with Pivot tables, so I would a appreciate a formula or VBA to solve.

 

[dwgnome]

I found a simple solution:

[B]AI6[/B] = [COLOR=#000080]DATE(RIGHT(AD5,4),1,1)[/COLOR]

Then in AI7

[COLOR=#000080]=EDATE(AI6,1)[/COLOR]

Drag down.

I can now change the drop down at AD5 to any year displayed as "Total 2015" or "Total 2016" etc and the secondary table updates perfectly.