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

P

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)

[TABLE="class: grid, width: 295"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD]ID
[/TD]
[TD="align: center"]Date
[/TD]
[TD="align: center"]title
[/TD]
[TD="align: center"]Qty
[/TD]
[/TR]
[TR]
[TD]abc123abc[/TD]
[TD="align: right"]18/12/2013
[/TD]
[TD="align: center"]Things [/TD]
[TD="align: right"]492[/TD]
[/TR]
[TR]
[TD]xyz12xy[/TD]
[TD="align: right"]14/9/2014[/TD]
[TD="align: center"]Fame [/TD]
[TD="align: right"]485
[/TD]
[/TR]
[TR]
[TD]qpqpqpqp[/TD]
[TD="align: right"]29/5/2014[/TD]
[TD="align: center"]Door [/TD]
[TD="align: right"]3724[/TD]
[/TR]
</tbody>[/TABLE]

DataSetB1 (each in a separate worksheet named on the value in column B1 i.e. 20131216 in this example)

[TABLE="class: grid, width: 536"]
<colgroup><col><col span="7"></colgroup><tbody>[TR]
[TD]ID
[/TD]
[TD="align: right"]20131216
[/TD]
[TD="align: right"] 20131217
[/TD]
[TD="align: right"]20131218
[/TD]
[TD="align: right"]20131219
[/TD]
[TD="align: right"]20131220[/TD]
[TD="align: right"] 20131221
[/TD]
[TD="align: right"]20131222[/TD]
[/TR]
[TR]
[TD]abc123abc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]xyz12xy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1
[/TD]
[/TR]
</tbody>[/TABLE]

DataSetB2 (each in a separate worksheet named on the value in column B1 i.e. 20131223 in this example)

[TABLE="class: grid, width: 512"]
<colgroup><col span="8"></colgroup><tbody>[TR]
[TD]ID
[/TD]
[TD="align: right"]20131223[/TD]
[TD="align: right"] 20131224
[/TD]
[TD="align: right"] 20131225
[/TD]
[TD="align: right"]20131226[/TD]
[TD="align: right"] 20131227
[/TD]
[TD="align: right"] 20131228
[/TD]
[TD="align: right"] 20131229
[/TD]
[/TR]
[TR]
[TD]xyz12xy[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="colspan: 2"]qpqpqpqp[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1
[/TD]
[/TR]
</tbody>[/TABLE]


The desired output is a consolidation of all datasetB type worksheets (about 75) with the 1 databaseA worksheet.

Sample output set required

[TABLE="class: grid, width: 1163"]
<colgroup><col><col><col span="16"></colgroup><tbody>[TR]
[TD]ID
[/TD]
[TD]Date
[/TD]
[TD]title[/TD]
[TD]Qty[/TD]
[TD="align: right"]20131216[/TD]
[TD="align: right"]20131217[/TD]
[TD="align: right"]20131218[/TD]
[TD="align: right"]20131219[/TD]
[TD="align: right"]20131220[/TD]
[TD="align: right"]20131221[/TD]
[TD="align: right"]20131222[/TD]
[TD="align: right"]20131223[/TD]
[TD="align: right"]20131224[/TD]
[TD="align: right"]20131225[/TD]
[TD="align: right"]20131226[/TD]
[TD="align: right"]20131227[/TD]
[TD="align: right"]20131228[/TD]
[TD="align: right"]20131229[/TD]
[/TR]
[TR]
[TD]abc123abc[/TD]
[TD="align: right"]18/12/2013[/TD]
[TD]Things [/TD]
[TD="align: right"]492[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]xyz12xy[/TD]
[TD="align: right"]14/9/2014[/TD]
[TD]Fame [/TD]
[TD="align: right"]485[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]qpqpqpqp[/TD]
[TD="align: right"]29/5/2014[/TD]
[TD]Door [/TD]
[TD="align: right"]3724[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
Pogodker said:
giving it a bump.. anyone who knows VBA.. please help. thanks in advance
Click to expand...
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
Pogodker said:
There are no duplicates. thanks for your help on this
Click to expand...
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
You must log in or register to reply here.

Similar threads

V
  • Question Question
VBA urgent help needed
Replies
5
Views
919
Vbalearner85
V
J
  • Question Question
Populate Table with filtered data from another table.
Replies
3
Views
3K
DanteAmor
DanteAmor
A
  • Question Question
VBA to copy a column to another column with the same date at the top
Replies
3
Views
3K
Repush
R
C
  • Question Question
Need Help with VBA Code - Copy data below and above a specific text
Replies
1
Views
864
onlyadrafter
O
R
  • Question Question
Need excel help
Replies
3
Views
690
rajesh1983
R

Forum statistics

Threads
1,223,738
Messages
6,174,209
Members
452,551
Latest member
croud

Share this page

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