Collating Diagonally arranged output of Batch Macro into Tabular format

[Tej_Singh]

Hi All,

My batch macro after reading 4 files (with common rows (A,B,C,D) and different column name (2/1/2021, 2/2/2021, ...)) is outputting data diagonally as shown in worksheet "Data" in the attached shared excel file.

Name	2/1/2021	2/2/2021	2/3/2021	2/4/2021
A	1
B	2
C	3
A		4
B		5
C		6
D		7
A			8
B			9
C			10
A				11
B				12
C				13

I want to collate them into a single table as shown in worksheet "Desired Output" in the attached shared excel file.

Name	2/1/2021	2/2/2021	2/3/2021	2/4/2021
A	1	4	8	11
B	2	5	9	12
C	3	6	10	13
D		7

Can anyone suggest a best way to that??? 

[Emil_Kos · Accepted solution]

Hi @Tej_Singh,

I took the data set from the post and created output for your:

Summarize tool works excellent here:

[Tej_Singh]

Thanks @Emil_Kos

That was really quick. This really worked if the column names are the same. 🙂

I have another situation. I am running batch macro at the end of every month. Hence; if I have a situation where my column names in the next month output changes from 2/1/2021 to 3/1/2021,  2/2/2021 to 3/2/2021,   2/3/2021 to 3/3/2021,  2/4/2021 to 3/4/2021 then I guess summarize won't work.

Any suggestions how to deal with this situation?

[Tej_Singh]

Thank You @Emil_Kos . It works as accepted. 🙂