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

Question

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     YearABCDCD YearCD20101713192531 2010374320112814202632 2011384420123915212733 20123945201341016222834 20134046201451117232935 20144147201561218243036 20154248

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

YearProductABCD2010Product 11713192011Product 12814202012Product 13915212013Product 141016222014Product 151117232015Product 161218242010Product 1b1725312011Product 1b2826322012Product 1b3927332013Product 1b41028342014Product 1b51129352015Product 1b61230362010Product 21737432011Product 22838442012Product 23939452013Product 241040462014Product 251141472015Product 26124248

BS_THE_ANALYST · Answer

@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

CommunityPivoting.yxzp