Aligning mixed data in an excel file

[jmarcuzb]

Hi everyone. First off I would like to say thank you for the help you guys have given me recently. This is a great community! Ok so now let me explain the issue I am facing now.

I have a data file containing numerical data and this data is arranged by time periods. For example from 1st Quarter 2022 through 4th Quarter 2022 and also additional years. The issue is that there are several sections of this file where

1. The time periods do not line up in the correct column

2. The columns headers are not named the same and

3. Sometimes the quarter and year are on two different lines as shown below.

I have created some mock data (file is attached) so you could see what I am facing.

3Q	2Q	1Q	4Q	3Q
2022	2022	2022	2021	2021
$24,654	$27,215	$26,165	$26,438	$25,857
619,533	642,045	728,367	714,396	734,012
3Q22	2Q22	1Q22	4Q21	3Q21
$1,674	$1,586	$2,008	$3,494	$3,282
5,383	4,990	5,105	2,182	3,546
3Q'22		2Q'22		1Q'22		4Q'21		3Q'21
$652,321		$694,644		$742,311		$767,713		$756,653
322,053		305,132		294,951		268,953		262,679
204,479		207,437		218,030		207,059		189,418

Question: How can I give each quarter standard names such as shown below and align the rest of the data in the correct columns ? I would like to create a single header column like the example below and align all the data in the file under these column headers. 

Additionally, the column headers row numbers can change from period to period. They will not always be located on the same row.

3Q 2022	2Q 2022	1Q 2022	4Q 2022	3Q 2021

Thanks in advance for any help you could provide.

[binuacs · Accepted solution]

@jmarcuzb One way of doing this with the batch macro

[jmarcuzb]

@binuacs , a quick question. I ran your solution but for me it seems to have flipped the data. Do you have any idea why that happened?

[jmarcuzb]

@binuacs, Oh I got it. I had to set the order in the union tool! Thanks! I will apply this to my large data sets!

[jmarcuzb]

@binuacs, I made a note above that stated the column headers will not always be located in the same row. This data changes from period to period. I think you used the sample data tool assuming the year and period information would always be located in rows 1 and 2. That is not the case. is there a way to dynamically locate this or even better just define the column header myself?