Data with different headers

I am seeking advice on how to merge over 100 files, each containing attributes of part numbers, into a single master file. Although the first column of all files is identical ("PartNumber"), the remaining columns may vary in their content and number of headers. I have attached two sample files for reference. Can someone provide guidance on the most efficient method to combine all of these files while preserving the attributes of each part number?

Attached Files:MEI_partAttributes.xlsx | MER_partAttributes.xlsx

Below is the possible solution I can think of if we can merge all and create three columns like the below; if there is any other better way please let me know:
possible.JPG

To merge the data, the headers in each sheet must be unique (within the sheet). This condition is not met by the sheet in the MEI_partAttributes file. Therefore, there is no assurance that this problem does not occur in the other files.

Artik

Originally Posted by Artik

To merge the data, the headers in each sheet must be unique (within the sheet). This condition is not met by the sheet in the MEI_partAttributes file. Therefore, there is no assurance that this problem does not occur in the other files.

Artik

Thank you for your reply. What is the solution if we consider each sheet has unique headers?