Tough challenge.. help needed.. Data consolidation.. Guessing VBA works best

Pogodker

New Member
Joined
Jun 8, 2015
Messages
4
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)

ID
Date
title
Qty
abc123abc18/12/2013
Things 492
xyz12xy14/9/2014Fame 485
qpqpqpqp29/5/2014Door 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
abc123abc9
xyz12xy321

<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
xyz12xy152379
qpqpqpqp11

<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
titleQty2013121620131217201312182013121920131220201312212013122220131223201312242013122520131226201312272013122820131229
abc123abc18/12/2013Things 4929
xyz12xy14/9/2014Fame 485321152379
qpqpqpqp29/5/2014Door 372411

<colgroup><col><col><col span="16"></colgroup><tbody>
</tbody>
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
giving it a bump.. anyone who knows VBA.. please help. thanks in advance
Question... for the number on Row 1 of each individual sheet, will there ever be repeats of numbers on two or more sheets or are the numbers on each sheet unique to that sheet? If there are duplicates, what should be done with their values per ID... add them together?
 
Upvote 0
There are no duplicates. thanks for your help on this
Give this macro a try...
Code:
Sub CombineData()
  Dim R As Long, RowNum As Long, LastRow As Long, LastCol As Long, Col As Long
  Dim SH As Worksheet, WS As Worksheet
  Set SH = Sheets("Sheet1")
  LastRow = SH.Cells(Rows.Count, "A").End(xlUp).Row
  For Each WS In Worksheets
    If WS.Name <> "Sheet1" Then
      LastCol = WS.Cells(1, Columns.Count).End(xlToLeft).Column
      Col = SH.Cells(1, Columns.Count).End(xlToLeft).Column + 1
      WS.Range(WS.Cells(1, "B"), WS.Cells(1, LastCol)).Copy SH.Cells(1, Col)
      For R = 2 To LastRow
        On Error GoTo CannotFind
        RowNum = WS.Columns("A").Find(SH.Cells(R, "A").Value, , , xlWhole, , False).Row
        WS.Range(WS.Cells(RowNum, 2), WS.Cells(RowNum, LastCol)).Copy SH.Cells(R, Col)
Continue:
      Next
    End If
  Next
  Exit Sub
CannotFind:
  Resume Continue
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top