Solved: Merging Sheets

[willhh3]

Hi,
Please forgive me for what may be a totally newb question. I've been searching for this and have found no easy way to accomplish the merge I need. Basically, I have a workbook with say 10 tabs (sheets). Each sheet has different columns with the expection or one, let's call it the key column. What I would like to be able to do is merge these 10 sheets into one sheet with one row per key number.

So sheet1 may have
A B C D
1
2
3

Sheet2 has
A E F G
1
2
3

Sheet3 has
A H I J K
1
2
3

Hopefully, that is enough information. If not let me know and I'll try to explain further.

Thanks in advance for any help.
Whh3

[oyarar]

did you try to do that with vlookup or index and match functions?

[mbarron]

Please post a sample of what you have and what you want.

[willhh3]

Hi and thanks for the quick reply. I did start using vlookups and exploring match (I'm teaching myself this stuff along the way), but frankly have too many sheets and too much data. I'm looking for a an easy bit of code to get is in one worksheet and then I'm planning on using pivot tables to slice and dice the informaton. I know I could do this all easily in Access, but don't have access to it at work. I'm attempting attach a sample spread sheet with 3 sheets to merge. The 4th tab is a sample of what I'm trying to accomplish.

Thanks!
Whh3

[willhh3]

Anybody help?

[mbarron]

Does this do what you want?
Sub moveCombine()
Dim sReq As Worksheet, sIE As Worksheet
Dim sLoc As Worksheet, sComb As Worksheet

Set sReq = Sheets("Req")
sReq.Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = "CombinedData"
Set sComb = ActiveSheet
Set sIE = Sheets("IE")
Set sLoc = Sheets("Loc")

sIE.Columns("B").Copy _
Destination:=sComb.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
sLoc.Columns("B:C").Copy _
Destination:=sComb.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)

End Sub

[willhh3]

Absolutely perfect mbarron! Thank you!

Now for one more complication and then I should be good to go. What if one of the sheets had duplicate numbers with different values. I'm uploading the sample workbook with the "Type" tab added and note that some of the values in the PK_id are duplicate, but with different "type" data. I added what I was thinking it should look like, if possilbe on the Desired Output tab, but am totally open to any ideas on a better way to do this. I think the Type_id field wouldn't be needed in this case, just the vaule in each type field. Not sure if this bit is doable, but definitely thanks for the first part...that will help tremendously.

Whh3

[mbarron]

Try this one:

Sub moveCombine()
Dim sReq As Worksheet, sIE As Worksheet
Dim sLoc As Worksheet, sComb As Worksheet
Dim sType As Worksheet, i As Long, j As Integer, k As Long
Dim lCol As Long

Application.ScreenUpdating = False
Set sReq = Sheets("Req")
sReq.Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = "CombinedData"
Set sComb = ActiveSheet
Set sIE = Sheets("IE")
Set sLoc = Sheets("Loc")

sIE.Columns("B").Copy _
Destination:=sComb.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
sLoc.Columns("B:C").Copy _
Destination:=sComb.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
i = 2
j = 4
k = 2

Set sType = Sheets("Type")


Do Until sType.Cells(i, 1) = ""
If sType.Cells(i, 1) = sType.Cells(i - 1, 1) Then
If j = 4 Then k = k - 1
j = j + 1
sType.Cells(k, j) = sType.Cells(i, 3)
If sType.Cells(i, 1) <> sType.Cells(i + 1, 1) Then
k = k + 1
End If
Else
j = 4
sType.Cells(k, j) = sType.Cells(i, 3)
k = k + 1
End If
i = i + 1
Loop
lCol = sType.Cells.Find(what:="*", searchdirection:=xlPrevious, searchorder:=xlByColumns).Column

For i = 4 To lCol
Cells(1, i) = "Type_Desc" & i - 3
Next

sType.Columns("D").Resize(Rows.Count, lCol - 3).Copy _
Destination:=sComb.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
End Sub

[willhh3]

Mbarron, you are a wizard! Thank you for your timely help, this will save me a lot of time and help me learn some tricks along the way!

Whh3