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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.