PDA

View Full Version : How to Merge/ Combine , three worksheets ?



dunkin
10-17-2016, 08:14 AM
I have Project, I attached below. I need to combine both worksheet. primary column is Child ID. First worksheet has one 16 columns + 51 Columns, but when I merge sing Microsoft query one matching rows came on new work sheet. What is best ways to combine both data. with all columns?

jolivanes
10-17-2016, 10:59 AM
Sub Easy_Peasy()
With Sheets("Datawarehouse")
.Range("A1:P" & .Cells(.Rows.Count, "A").End(xlUp).Row).Copy Sheets("Merged Data").Range("A1")
With Sheets("3 Rd Party Data")
.Range("A1:BB" & .Cells(.Rows.Count, "A").End(xlUp).Row).Copy Sheets("Merged Data").Range("Q1")
End With
End With
End Sub


If it does not do what you want it to do you have to explain in more detail.
And maybe put some before and after in your attachment. An empty workbook, with the exception of headers, does not tell me much. But that might be just me.

dunkin
10-17-2016, 12:21 PM
this is good but its not combine. there some columns has same names, ..... as example " child name, case name, gender, Child id number those needs to combine in one. if you still have hard time to understand then I can upload files with row data.

jolivanes
10-17-2016, 01:22 PM
The only ones that are the same that I can find between these two sheets is "Child ID" and "County Name"
Where do you find the other ones you mentioned?


To recap things.
In "Datawarehouse" sheet you have 16 column headers
In "3 Rd Party Data" sheet you have 54 column headers
Two headers, that I can see, are the same between these two sheets.
In "Merged Data" sheet you have no headers.
Do you want to copy all the headers from "3 Rd Party Data" sheet into "Merged Data" sheet and add the non duplicates from "Datawarehouse" sheet to the end?
After that, copy all the data from both first two sheets under the same header in the third sheet?

jolivanes
10-17-2016, 09:24 PM
This is the nearest I understand it to be.

Sub Transfer()
Dim md As Worksheet, rpd As Worksheet, dw As Worksheet
Dim a, b, i As Long, j As Long, c As Range, lc As Long
Set dw = Sheets("Datawarehouse")
Set md = Sheets("Merged Data")
Set rpd = Sheets("3 Rd Party Data")
a = Application.Transpose(Application.Transpose(dw.Range(dw.Cells(1, 1), dw.Cells(1, dw.Cells(1, dw.Columns.Count).End(xlToLeft).Column)).Value))
b = Array("Datawarehouse", "3 Rd Party Data")
Application.ScreenUpdating = False

md.Cells(1, 1).Resize(, rpd.Cells(1, rpd.Columns.Count).End(xlToLeft).Column).Value = _
rpd.Range(rpd.Cells(1, 1), rpd.Cells(1, rpd.Cells(1, rpd.Columns.Count).End(xlToLeft).Column)).Value

For i = LBound(a) To UBound(a)
If WorksheetFunction.CountIf(rpd.Range(rpd.Cells(1, 1), rpd.Cells(1, rpd.Cells(1, rpd.Columns.Count).End(xlToLeft).Column)), a(i)) = 0 Then _
md.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).Value = a(i)
Next i

For j = LBound(b) To UBound(b)
With Sheets(b(j))
lc = .Cells(1, .Columns.Count).End(xlToLeft).Column


For Each c In .Range(.Cells(1, 1), .Cells(1, lc))
c.Offset(1).Resize(.Cells(.Rows.Count, c.Column).End(xlUp).Row - 1).Copy _
md.Cells(Rows.Count, md.Rows(1).Find(c.Value, , , 1).Column).End(xlUp).Offset(1)
Next c


End With

Next j
Application.ScreenUpdating = True
End Sub

dunkin
10-18-2016, 07:43 AM
Run time error '1004'
Application -defined or object defined error came up on screen,

I ADD ALSO DATA ON SPREAD SHEETS , NOW IS READY TO MERGE DATA. CHILD ID SHOULD BE ONE COLUMN.

jolivanes
10-18-2016, 09:48 AM
If you delete the headers in "3 Rd Party Data" sheet where there is no info below it, it'll work.
If that is not an option, let us know.

dunkin
10-18-2016, 10:15 AM
Washington data

Third party data


Student Name
Child ID

Student First Name
Student Surname
New Column
Student Number


Rozanne Rumbaugh
A0007

Robert
Remus
Robert Remus
A0001


Alyson Ader


Thi
Tart
Thi Tart
A0002


Rozanne Rumbaugh
A0007

Synthia
Sirmons
Synthia Sirmons
A0003


Alyson Ader


Meggan
Mohan
Meggan Mohan
A0004


Rozanne Rumbaugh
A0007

Shayna
Soukup
Shayna Soukup
A0005


Alyson Ader


Waltraud
Wiltse
Waltraud Wiltse
A0006


Rozanne Rumbaugh
A0007

Rozanne
Rumbaugh
Rozanne Rumbaugh
A0007


Alyson Ader


Hilda
Hysell
Hilda Hysell
A0008


Rozanne Rumbaugh
A0007

Ayesha
Applegate
Ayesha Applegate
A0009


Alyson Ader


Kirsten
Kratzer
Kirsten Kratzer
A0010


Rozanne Rumbaugh
A0007

Reynalda
Rolfes
Reynalda Rolfes
A0011


Alyson Ader


Casey
Crays
Casey Crays
A0012


Rozanne Rumbaugh
A0007

Camelia
Czerwinski
Camelia Czerwinski
A0013


Alyson Ader


Leesa
Licon
Leesa Licon
A0014


Rozanne Rumbaugh
A0007

Piper
Paradise
Piper Paradise
A0015


Alyson Ader


Kazuko
Kawasaki
Kazuko Kawasaki
A0016


Rozanne Rumbaugh
A0007

Leonarda
Latimore
Leonarda Latimore
A0017


Alyson Ader


Siu
Stouffer
Siu Stouffer
A0018


Rozanne Rumbaugh
A0007

Rob
Rummel
Rob Rummel
A0019


Alyson Ader


Alyson
Ader
Alyson Ader
A0020

jolivanes
10-18-2016, 11:33 AM
Just now I saw your previous post (Post #8)
Run this code and let us know what needs changing.


Sub Transfer_B()
Dim md As Worksheet, rpd As Worksheet, dw As Worksheet
Dim a, b, i As Long, j As Long, c As Range, lc As Long
Set dw = Sheets("Datawarehouse")
Set md = Sheets("Merged Data")
Set rpd = Sheets("3 Rd Party Data")
a = Application.Transpose(Application.Transpose(rpd.Range(rpd.Cells(1, 1), rpd.Cells(1, rpd.Cells(1, rpd.Columns.Count).End(xlToLeft).Column)).Value))
b = Array("Datawarehouse", "3 Rd Party Data")
Application.ScreenUpdating = False
md.Cells(1, 1).Resize(, dw.Cells(1, dw.Columns.Count).End(xlToLeft).Column).Value = _
dw.Range(dw.Cells(1, 1), dw.Cells(1, dw.Cells(1, dw.Columns.Count).End(xlToLeft).Column)).Value
For i = LBound(a) To UBound(a)
If WorksheetFunction.CountIf(dw.Range(dw.Cells(1, 1), dw.Cells(1, dw.Cells(1, dw.Columns.Count).End(xlToLeft).Column)), a(i)) = 0 Then _
md.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).Value = a(i)
Next i
For j = LBound(b) To UBound(b)
With Sheets(b(j))
lc = .Cells(1, .Columns.Count).End(xlToLeft).Column
For Each c In .Range(.Cells(1, 1), .Cells(1, lc))
If Not c.Offset(1) = "" Then _
c.Offset(1).Resize(.Cells(.Rows.Count, c.Column).End(xlUp).Row - 1).Copy _
md.Cells(Rows.Count, md.Rows(1).Find(c.Value, , , 1).Column).End(xlUp).Offset(1)
Next c
End With
Next j
Application.ScreenUpdating = True
End Sub

jolivanes
10-18-2016, 03:23 PM
In your Post #8 You have the following Column Headers:
Student Name and Child ID under Washington data.
Student First Name, Student Surname, New Column and Student Number under Third party data.
There is no "Student Name" in either sheet. You mean "Child Name" from "Datawarehouse" sheet I assume.
There is a "Child ID" in "Datawarehouse" sheet.
There is no "Student First Name" in "3 Rd Party Data" sheet.
There is no "Student Surname" in "3 Rd Party Data" sheet.
New Column?????????? Is this a Column to be added/inserted?
There is no "Student Number" in "3 Rd Party Data" sheet. However, the numbers are the same as under the "Child ID" header in the "3 Rd Party Data" sheet.
Why are the names, Rozanne Rumbaugh and Alyson Ader, repeated in the left picture of Post #8?
Why no Child ID for Alyson Ader?

dunkin
10-19-2016, 06:54 AM
Please forget about those table. Recently I updated my attachment file. you can download now. I made littlie bit easier.

jolivanes
10-19-2016, 07:59 AM
There are no attachments to your Post #11.

dunkin
10-19-2016, 09:15 AM
I updated 1st post.

jolivanes
10-19-2016, 09:41 AM
Explain the difference please between the fist attachment with only headers and the, as you mention in the post above, updated version with only headers.
And then explain what you want to accomplish. Remember, you know your workbook and you know what you want, we are just guessing until it is explained what is needed.