I need help to consolidate some massive data files. i will be really greatful to anyone who can give me a working VBA code. have spent almost 2 days trying, powerqueries,match etc etc. i am now convinced a well written VBA code is my only way out.. please help..
Here is the different datasets
Dataset A (in a separate worksheet sheet1)
<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>
DataSetB1 (each in a separate worksheet named on the value in column B1 i.e. 20131216 in this example)
<colgroup><col><col span="7"></colgroup><tbody>
</tbody>
DataSetB2 (each in a separate worksheet named on the value in column B1 i.e. 20131223 in this example)
<colgroup><col span="8"></colgroup><tbody>
</tbody>
The desired output is a consolidation of all datasetB type worksheets (about 75) with the 1 databaseA worksheet.
Sample output set required
<colgroup><col><col><col span="16"></colgroup><tbody>
</tbody>
Here is the different datasets
Dataset A (in a separate worksheet sheet1)
ID | Date | title | Qty |
abc123abc | 18/12/2013 | Things | 492 |
xyz12xy | 14/9/2014 | Fame | 485 |
qpqpqpqp | 29/5/2014 | Door | 3724 |
<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>
DataSetB1 (each in a separate worksheet named on the value in column B1 i.e. 20131216 in this example)
ID | 20131216 | 20131217 | 20131218 | 20131219 | 20131220 | 20131221 | 20131222 |
abc123abc | 9 | ||||||
xyz12xy | 32 | 1 |
<colgroup><col><col span="7"></colgroup><tbody>
</tbody>
DataSetB2 (each in a separate worksheet named on the value in column B1 i.e. 20131223 in this example)
ID | 20131223 | 20131224 | 20131225 | 20131226 | 20131227 | 20131228 | 20131229 |
xyz12xy | 1 | 52 | 3 | 7 | 9 | ||
qpqpqpqp | 1 | 1 |
<colgroup><col span="8"></colgroup><tbody>
</tbody>
The desired output is a consolidation of all datasetB type worksheets (about 75) with the 1 databaseA worksheet.
Sample output set required
ID | Date | title | Qty | 20131216 | 20131217 | 20131218 | 20131219 | 20131220 | 20131221 | 20131222 | 20131223 | 20131224 | 20131225 | 20131226 | 20131227 | 20131228 | 20131229 |
abc123abc | 18/12/2013 | Things | 492 | 9 | |||||||||||||
xyz12xy | 14/9/2014 | Fame | 485 | 32 | 1 | 1 | 52 | 3 | 7 | 9 | |||||||
qpqpqpqp | 29/5/2014 | Door | 3724 | 1 | 1 |
<colgroup><col><col><col span="16"></colgroup><tbody>
</tbody>