Reshape Repeating Columns into Rows

[mikedess]

Struggling to determine an efficient means of dynamically combining repeating Columns into Rows when all is unknown. Known variables are company name in row 1, Field headers in Row 2. There can be extra blank columns between each company data section, rows/amount of data can vary, number of company sections can vary. See attached for sample data. Desired output is below with full output on second tab of attached excel

Company	Date	Amount
Company 1	1/2/2019	(1,000,000.00)
Company 1	10/17/2020	771.52
Company 1	6/6/2022	4,412.49
Company 1	9/5/2022	(9,687.62)
Company 1	11/8/2022	(7,301.59)
Company 1	5/27/2023	5,569.53
Company 2	5/6/2011	(1,000,000.00)
Company 2	2/18/2013	(88,000.00)
Company 2	10/8/2014	(150,000.00)
Company 2	1/7/2015	6,045.35
Company 2	3/12/2015	7,554.44
Company 2	9/28/2015	(98.68)
Company 2	4/15/2016	1,333.91
Company 2	11/1/2016	(2,028.56)
Company 2	1/31/2017	2,883.57
Company 2	11/16/2018	6,153.43
Company 2	6/4/2019	(1,003.81)
Company 2	12/21/2019	(9,312.90)
Company 2	7/8/2020	8,265.36
Company 2	1/24/2021	(5,629.87)

[christine_assaad]

Hi @mikedess 

Does the attached look like an acceptable solution?

[Qiu]

@christine_assaad 
I feel what is he need is reversed process of your flow.😁

@mikedess 
If my understanding is correct, I would try with an iterative macro to "Join" the Repeating Columns horizontally as below.

Have to say, dealing with unstructured data is not really the strong suit of Alteryx.