Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
Ok, I have two worksheets that have similar data in two fields and different data in others. I want to be able to merge the two worksheets into one so one record has all information. For example:
worksheet One has:
____A___________B__________C__________D
Manufacturer___Job________Cost______Month_Edlebrock__Overhaul____1,752.00_____Mar
__Holly_____Replace_______700.25_____AprOk, the Second worksheet contains:
A B C D
Customer Manufacturer Cost Phone #Jones Holly 700.25 555-5555
Smith Edlebrock 1,752.00 555-5555What I want to do is merge the worksheets into one. Is there a way to do that using the cost and manufacturer fields. No two records would have identical cost and Manufacturer fields.
Thanks in advance.
Hopefully this isn't too confusing I've tried to recreate the Excel worksheets the best I can!
worksheet One has:
____A___________B__________C__________D
Manufacturer____Job________Cost________Month_Edlebrock__Overhaul____1,752.00_____Mar
__Holly_____Replace_______700.25_____AprOk, the Second worksheet contains:
____A_________B___________C___________D
Customer__Manufacturer___Cost______Phone #_Jones_______Holly_______700.25____555-5555
_Smith_____Edlebrock___1,752.00____555-5555What I want to do is merge the worksheets into one. Is there a way to do that using the cost and manufacturer fields. No two records would have identical cost and Manufacturer fields.
Thanks in advance.
0 
"No two records would have identical cost and Manufacturer fields."
In your example both have identical cost and manufacturer values?
Do you just want to bring in the Customer and Phone numbers into the first sheet?
Not sure of what you are wanting to do.
Bryan
0
Sorry about the way I wrote that, what I ment is in both worksheet One and worksheet Two they would match but there would never be two records in each workbook that had both
Manufacter and Cost the same. I want to merge both worksheets to one adding the additional fields of both worksheets. So the final resulting worksheet would have all these fields:Manufacturer,Cost,Job,Month,Customer,Phone
What I'm trying to say is that I have two worksheets that have records that correspond, I want to merge each record so it contains all the above fields. So, if Manufacturer and Cost are the same in both worksheets then the records correspond and can be merged.
I hope this makes it more clear, if not I will answer any remaining questions.
Thanks
0
So I am thinking a Multi-criteria VLookup will do the trick.
Insert a column on both sheets at Column A.
The common fields are Manufacturer and Cost.Using the concatenate formula to combine these fields.
In sheet1:
=A2&C2
Drag the formula down the column.In Sheet2
=B2&C2
and drag it down.Now you can use a VLookup to bring in the values of the missing fields of Customer and Phone.
If you need to know how to do that then post back.
Then you can either copy and paste special, values on the brought in fields or you can hide column A containing the combined fields.
There are other ways to do what you want but this is an easy way.
HTH
Bryan
0
An Example by Aaron Blood can be downloaded from here.
Just right click and select Save target as.It is 20.5kb in size. It is very similar to the way I explained but it contains the formulas too.
Let me know if and when you egt it so I can take it off of my site.
HTH
Bryan
0
Hey Bryan. Can i get that example of you too. i am trying to do the same thing - merge two worksheets with a common field between them. Cheers
0
Brian, I placed it back up on my webpage.
Let me know after you get it so I can take it off again.
Otherwise I will remove it tomorrow night.
Bryan
0
I have a similar problem but slightly more complex. I have two worksheets. Each worksheet has a series of categories and attributes listing that category...
here is an ex. of what each worksheet is like
------
Category Make Colour Cost
Fans sony black $200
Fans abc white $140Category Make Colour Model Cost
Car VW red Jetta $300
Car Audi white A4 $250
how do i merge the two worksheet based on the category...
ie. i want one worksheet listing all the cars from the 1st and 2nd worksheet, but it is imperative that the header remain consistant of the header for cars while the merger of the fans from worksheet 1 and 2
contain the header for fans...
0
If your two worksheets contain only the Fans and Cars then the following will work to Combine the worksheets.
The two worksheets must be in the same Workbook.
The following Macro was created by Derk at OzGrid's Excel/VBA board.
Dim OK As Boolean
Sub ConsolidateWorkbook()
Dim k1 As Long, k2 As Long, n As Long
Application.ScreenUpdating = False
k1 = 1
k2 = 2
n = ActiveWorkbook.Worksheets.Count
While k2 <= n
CombineSheets Worksheets(k1), Worksheets(k2)
If OK Then
k2 = k2 + 1
Else
k1 = k1 + 1
If k2 = k1 Then k2 = k2 + 1
End If
Wend
Application.DisplayAlerts = False
For k2 = n To k1 + 1 Step -1
Worksheets(k2).Delete
Next k2
MsgBox n & " worksheets have been consolidated to " & k1, vbOKOnly + vbInformation, "All Done"
End Sub
Sub CombineSheets(s1 As Worksheet, s2 As Worksheet)
Dim n1 As Long, n2 As Long
n1 = s1.Range("A65536").End(xlUp).Row
n2 = s2.Range("A65536").End(xlUp).Row
OK = (n1 + n2 <= 65536)
If OK Then
With s2.Range(s2.Cells(1, 1), s2.Cells(n2, s2.UsedRange.Columns.Count))
.Copy s1.Range("A" & n1 + 1)
.Clear
End With
End If
End SubThe formatting does not appear correctly in this post but you can go to the link and copy it from there.
If you need to know how to incorporate a Macro then post back for instructions.
If this macro does not suit your needs then post your question on OzGrid's Excel/VBA board as there are some very knowledgable folks there. I am in 'Macro' first grade.
HTH
Bryan
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
