Excel data table consolidation (and transpose)

Help
1

Hello -

I need to read two data tables, consolidate the data in the tables, transpose the data, and delete a few unnecessary lines from the input data tables.

I want to use these two data inputs (One.xls and Two.xls) and end up with New.IdealState.xls.

The following needs to happen:

  1. Delete the unnecessary text lines at the bottom of the input files (One.xls and Two.xls)
  2. Consolidate data rows for a single ID Number (ignore null fields and ignore duplicate data fields to create a single row of data for each input .xls file)
  3. Transpose the consolidated data (or switch 2. and 3.)

I’ve been able to accomplish this through using a macro for transposition and a dictionary that ignores null fields to essentially consolidate the data, but now I need to write an .xls that physically shows the consolidated and appended data table. I could write an .xls based on the dictionary, but I was having issues with this. Additionally, I don’t think I have the best approach for this whole process so I wanted to see what others may recommend.

Note that the input data tables will vary in the number of datarows with the same ID Number (though they will always have the same ID Number) and the number of columns.

Any help is appreciated!

ConsolidateDataTables.zip (28.9 KB)