Combining several tables from one sheet into 1 data set based on columns

[tmrwall]

I would like to combine several tables from one excel spreadsheet into one with the table header as a field, based on common fields. In the example below I have included 2 tables but the Product 2 table repeats 6 times with an inconsistent number of null columns between tables. Thanks in advance!

Table 1: Starting format of the excel sheet

Product 1								Product 2
					Product 1b
Year	A	B	C	D	C	D		Year	C	D
2010	1	7	13	19	25	31		2010	37	43
2011	2	8	14	20	26	32		2011	38	44
2012	3	9	15	21	27	33		2012	39	45
2013	4	10	16	22	28	34		2013	40	46
2014	5	11	17	23	29	35		2014	41	47
2015	6	12	18	24	30	36		2015	42	48

Table 2: The format I would like the result to take

Year	Product	A	B	C	D
2010	Product 1	1	7	13	19
2011	Product 1	2	8	14	20
2012	Product 1	3	9	15	21
2013	Product 1	4	10	16	22
2014	Product 1	5	11	17	23
2015	Product 1	6	12	18	24
2010	Product 1b	1	7	25	31
2011	Product 1b	2	8	26	32
2012	Product 1b	3	9	27	33
2013	Product 1b	4	10	28	34
2014	Product 1b	5	11	29	35
2015	Product 1b	6	12	30	36
2010	Product 2	1	7	37	43
2011	Product 2	2	8	38	44
2012	Product 2	3	9	39	45
2013	Product 2	4	10	40	46
2014	Product 2	5	11	41	47
2015	Product 2	6	12	42	48

[BS_THE_ANALYST]

@tmrwall Solution needs refining but it gets the job done. I'd seriously suggest have complete data in the beginning. There's lots of assumptions to make based off your 'Table 1' and lots of missing columns. For instance. Product 1b and Product 2 both receive columns A and B even though they don't exist in the table. Therefore, Alteryx has to create this for you. There's no reason to omit columns of data; it makes everything a lot more challenging.

You've got Product Names spanning two rows. The solution accounts for this but it's difficult to understand why that happens.

 

All the best,

BS