pyrte
02-16-2016, 06:30 AM
I have scoured the internet for a quick solution to this problem, there are many questions that seem to ask the same thing I require but no answers I can see.
Microsoft knowledge base is useful, but unfortunately I can't seem to find a solution that applies to this particular case. Same with MrExcel, so if there is a thread I've missed that'll make my century!
_____
I have 95 workbooks
A total of about 3000 unique headers
Each worksheet has about 400 rows of data and anything from 6-58 columns
~160,000 rows of data total
As is probably clear from the fact that the number of columns varies, not all of the headers are consistent - but many are commonly occuring.
I found the consolidate tool, and this had the potential to make my task very simple, but the problem I have is it doesn't appear to have the option to simply append data.
For example, the simplest worksheet has headers:
Supplier (common to all - fixed position, Column A, repeating values)
Date (common to all - moves, repeating values)
Qty (common to all - moves, repeating values)
Low (common to all - moves, repeating values)
High (common to all - moves, repeating values)
Renew (only present in the very old worksheets but I wish to retain this column)
The most complicated has:
Supplier
ContactName
Tel1
Tel2
Add1
Add2
Add3
Add4
Add5
Added
LastActivity
Date
Qty
Low
High
RandomA (occassionally repeating values, not always present)
RandomB (occassionally repeating values, not always present)
...
(and more)
I need to combine these many worksheets so that ALL headers from ALL sheets are present in the consolidated worksheet and that the matching columns are appended into one huge worksheet.
Any help that you guys can offer is appreciated.
Microsoft knowledge base is useful, but unfortunately I can't seem to find a solution that applies to this particular case. Same with MrExcel, so if there is a thread I've missed that'll make my century!
_____
I have 95 workbooks
A total of about 3000 unique headers
Each worksheet has about 400 rows of data and anything from 6-58 columns
~160,000 rows of data total
As is probably clear from the fact that the number of columns varies, not all of the headers are consistent - but many are commonly occuring.
I found the consolidate tool, and this had the potential to make my task very simple, but the problem I have is it doesn't appear to have the option to simply append data.
For example, the simplest worksheet has headers:
Supplier (common to all - fixed position, Column A, repeating values)
Date (common to all - moves, repeating values)
Qty (common to all - moves, repeating values)
Low (common to all - moves, repeating values)
High (common to all - moves, repeating values)
Renew (only present in the very old worksheets but I wish to retain this column)
The most complicated has:
Supplier
ContactName
Tel1
Tel2
Add1
Add2
Add3
Add4
Add5
Added
LastActivity
Date
Qty
Low
High
RandomA (occassionally repeating values, not always present)
RandomB (occassionally repeating values, not always present)
...
(and more)
I need to combine these many worksheets so that ALL headers from ALL sheets are present in the consolidated worksheet and that the matching columns are appended into one huge worksheet.
Any help that you guys can offer is appreciated.