I have built a macro that will compare any two data inputs based on a mapping maintained in an external spreadsheet and only process the mapped columns between the datasets but then include all columns in the output along with the differences.
The resulting output is used by analysis to monitor production, carry out QA and support overall troubleshooting.
I can compare 4 systems against a baseline system with 289,000 rows; 130 columns (37,570,000 data points) in approximately 10 minutes.
The output goes to multiple tabs of an Excel workbook.
The fields generated and maintained by my macro are constant in what they output.
The macro is able to handle any number of columns as input.
Given that, I want to output a standard number of columns to support Excel pivot table lack of dynamic ranges.
Let's say I want to output 500 columns every time.
At the very end of my macro, I will get the field info of the output and count up the number of columns.
If there are only 450 columns, I would like to create 50 generic no-data columns to simply maintain the dimensions of the output table.
So the remaining columns could have any titles. For example: -No-Use-001-, -No-Use-002-
While a self explanatory column name would be good so when an analyst comes across them they get that they are irrelevant, I'm not hung up on what they are called right now.
I would prefer to focus on the creation of them.
Anyone have any suggestions?
Solved! Go to Solution.
I would use a sample tool to capture the 1st row of data and column names, then transpose to get your field names in a single column and add a RecordID. Then you can probably use a join and union tool with a static list of 500 rows (RecordID, Name and Value) to add in the missing column names up to 500. CrossTab the list back, remove the 1 line of data and Union to your original data set.
Thank you - I'm going to try it tonight.
This word perfectly, thanks very much for your help.