Reformat/Layout Data in a Table

[nikneven]

I have a table that is laid out with date as the Row heading and then several optional columns and then several required columns. I need to dynamically link it to a table that is better laid out for data analysis.


Data comes in as:

Book1
	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Optional 1	Optional 2	Optional 3	Optional 4	Optional 5	Optional 6	Optional 7	Optional 8	Optional 9	Manual Adjustments	Date	Fiscal Quarter	Fiscal Year	Hours Year
9	0	0	0	0	107.5	0	0	0	0	0	8/23/2018	4	2018	2019
10	0	0	0	0	107.5	0	0	0	0	0	8/24/2018	4	2018	2019
11	0	0	38.8866667	0	0	0	0	0	0	0	8/25/2018	4	2018	2019
12	0	0	0	0	0	0	0	0	0	0	8/26/2018	4	2018	2019
13	410	0	0	0	0	0	0	0	16.76	0	8/27/2018	4	2018	2019
14	410	0	0	0	0	0	0	0	11.76	0	8/28/2018	4	2018	2019
15	410	0	0	0	0	0	0	0	16.76	0	8/29/2018	4	2018	2019
16	410	0	0	0	0	0	0	0	11.76	0	8/30/2018	4	2018	2019
17	0	228.9833	0	0	0	0	0	0	0	0	8/31/2018	4	2018	2019
18	0	0	38.8866667	0	0	0	0	0	0	0	9/1/2018	1	2019	2019
19	0	0	0	0	0	0	0	0	0	0	9/2/2018	1	2019	2019
Calendar

And I need it linked to something like this (something that ignores values of 0):

Book1
	A	B	C	D	E	F
1	Option #	Hours	Date	Fiscal Quarter	Fiscal Year	Hours Year
2	Optional 5	107.5	8/23/2018	4	2018	2019
3	Optional 5	107.5	8/24/2018	4	2018	2019
4	Optional 3	38.886667	8/25/2018	4	2018	2019
5	Optional 1	410	8/27/2018	4	2018	2019
6	Option 9	16.76	8/27/2018	4	2018	2019
7	Optional 1	410	8/28/2018	4	2018	2019
8	Option 9	11.76	8/28/2018	4	2018	2019
9	Optional 1	410	8/29/2018	4	2018	2019
10	Option 9	16.76	8/29/2018	4	2018	2019
11	Optional 1	410	8/30/2018	4	2018	2019
12	Option 9	11.76	8/30/2018	4	2018	2019
13	Optional 2	228.98333	8/31/2018	4	2018	2019
14	Optional 3	38.886667	9/1/2018	1	2019	2019
Sheet1

I can figure out how to do it clumsily across a multiple dummy sheets, but I was hoping for a elegant or easy-ish solution.

Any help would be greatly appreciated!

 

[Rijnsent]

Hi nikeven,
you're basically trying to "unpivot" your data. You could do that with formulas or macros. Check out e.g. unpivot_002.xlsm that I created a while ago: https://www.dropbox.com/sh/l7ywfwzfk5j20sr/AACXwUGywU-4J-IWwdZY58Ppa?dl=0
Hope that helps,
Koen