combining 2 excel sheets into 1

[shonu1320]

I want to combine my weekly reports into one master file. After I copy paste them into one sheet, I have to go through them manually to match all the rows. There can be 7000 plus rows and it takes all day to manually fix them. What can I do to automate it? V lookup only work on one column. I want to look up 3 values(3 columns). Can I use macros? Any advices? Thanks!

 

[alansidman]

Your explanation of your issue is to vague to respond with a solution. Suggest you explain in simple English exactly what you are trying to do. Showing examples of what you have and and what you wish to accomplish will help to get you a solution. We need more information.

 

[petertenthije]

A quick and dirty solution would be to add a new column, and in it combine the three columns you are trying to look up. You can then use a VLOOKUP against the added column.

=A1&"-"&B1&"-"&C1

The added dashes are optional but are an easy way to keep track.

There's probably a more elegant way to do this, but I am sure some of the more experienced gurus can help you with that.

 

[shonu1320]

I understand that it is hard to understand. I have a sample but I am not sure how to attach file in the forum.

So I have three spreadsheets. I want to combine them into 1 but need to make sure data aligns. For example, below I have copied data from all three files into 1 sheet. But I want value of column 4 & 5 of all 3 files match with the column 1,2and 3 AND on the same row. So in my master file. I will have columns State, DOC, Type, Begin1, end 1, begin 2, end 2, begin 3 and end 3. Where the data is not available, it should leave it blank.
Below Example shows my raw data that I just copy pasted next to each other.

State	DOC	Type	Begin 1	End 1		State	DOC	Type	Begin 2	End 2		State	DOC	Type	Begin 3	End 3
Chicago	PA	1	7	7		Chicago	PA	1	6	6		Chicago	PA	1	5	4
Chicago	PA	2				Chicago	PA	2	1	0		Chicago	PA	2	1	0
Chicago	PA	3	12	12		Chicago	PA	3	20	16		Chicago	PA	3	3	2
Chicago	PB	3	5	5		Chicago	PB	3	9	9		Chicago	PB	3	5	5
Chicago	PC	1				Chicago	PD	1	166	158		Chicago	PC	3	2	2
Chicago	PC	3	1	1		Chicago	PD	2	91	85		Chicago	PD	1	210	204
Chicago	PD	4				Chicago	PD	3	613	611		Chicago	PD	2	107	101
Chicago	PD	1	158	154		Chicago	PE	3	4	4		Chicago	PD	3	658	657

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup>

This is how the final result should be

State	DOC	Type	Begin 1	End 1	Begin 2	End 2	Begin 3	End 3
Chicago	PA	1	7	7	6	6	5	4
Chicago	PA	2			1	0	1	0
Chicago	PA	3	12	12	20	16	3	2
Chicago	PB	3	5	5	9	9	5	5
Chicago	PC	1
Chicago	PC	3	1	1			2	2
Chicago	PC	4
Chicago	PD	1	158	154	166	158	210	204

<colgroup><col width="64" style="width: 48pt;" span="9">
<tbody>



































































































</tbody>

 

[Peter_SSs]

I have a sample but I am not sure how to attach file in the forum.

You can't, but see here: Attachments

So I have three spreadsheets.

Are they all in the same workbook? If so, what are their names? If not, how do we locate & identify the relevant sheets?

I want to combine them into 1 ..

Does the destination sheet already exist? If so, what is its name and does it already contain data that must be retained?

 

[Peter_SSs]

In relation to my own questions, I have made these assumptions:
- All 3 data sheets are in the same workbook. Names are "Data1", "Data2" & "Data3", but can be easily changed in the code where indicated.
- The results go on sheet "Summary" in the same workbook (Again the name can be changed in the code) & this sheet has nothing in it that needs to be kept.

If those assumptions happen to be correct, then you could try this in a copy of your workbook.

Sub Combine_And_Align()
  Dim d As Object
  Dim ws As Worksheet
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long, rw As Long
  Dim SDT As String
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  ReDim b(1 To Rows.Count, 1 To 9)
  For Each ws In Sheets(Array("Data1", "Data2", "Data3")) '<- Data sheet names here
    a = ws.Range("A1").CurrentRegion.Value
    For i = 2 To UBound(a)
      SDT = a(i, 1) & "|" & a(i, 2) & "|" & a(i, 3)
      If Not d.exists(SDT) Then
        rw = rw + 1
        d(SDT) = rw
        b(rw, 1) = a(i, 1): b(rw, 2) = a(i, 2): b(rw, 3) = a(i, 3)
      End If
      b(d(SDT), 4 + k) = a(i, 4)
      b(d(SDT), 5 + k) = a(i, 5)
    Next i
    k = k + 2
  Next ws
  With Sheets("Summary")                                  '<- Summary sheet name here
    .UsedRange.ClearContents
    With .Range("A2").Resize(rw, UBound(b, 2))
      .Value = b
      .Cells(0, 1).Resize(, 9).Value = Array("State", "DOC", "Type", "Begin 1", "End 1", "Begin 2", "End 2", "Begin 3", "End 3")
      .CurrentRegion.EntireColumn.AutoFit
    End With
  End With
End Sub

The resultant 'Summary' sheet for me is ..

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G	H	I
1	State	DOC	Type	Begin 1	End 1	Begin 2	End 2	Begin 3	End 3
2	Chicago	PA	1	7	7	6	6	5	4
3	Chicago	PA	2			1	0	1	0
4	Chicago	PA	3	12	12	20	16	3	2
5	Chicago	PB	3	5	5	9	9	5	5
6	Chicago	PC	1
7	Chicago	PC	3	1	1			2	2
8	Chicago	PD	4
9	Chicago	PD	1	158	154	166	158	210	204
10	Chicago	PD	2			91	85	107	101
11	Chicago	PD	3			613	611	658	657
12	Chicago	PE	3			4	4
Summary

 

[Marcelo Branco]

Another macro (has some similarity to Peter's code...)

Sub aTest()
    Dim dic As Object, sh As Variant, strKey As String
    Dim vData As Variant, i As Long, lCounter As Long, vAux As Variant
    
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = vbTextCompare
    
    lCounter = 0
    For Each sh In Array("Sheet1", "Sheet2", "Sheet3") '<--Adjust sheet names
        With Sheets(sh)
            vData = .Range("A2:E" & .Cells(.Rows.Count, "A").End(xlUp).Row)
            For i = LBound(vData, 1) To UBound(vData, 1)
                strKey = vData(i, 1) & "|" & vData(i, 2) & "|" & vData(i, 3)
                If dic.exists(strKey) Then
                    vAux = dic(strKey)
                    vAux(lCounter) = vData(i, 4)
                    vAux(lCounter + 1) = vData(i, 5)
                    dic(strKey) = vAux
                Else
                    Select Case lCounter
                        Case 0
                        dic(strKey) = Array(vData(i, 4), vData(i, 5), "", "", "", "")
                        Case 2
                        dic(strKey) = Array("", "", vData(i, 4), vData(i, 5), "", "")
                        Case 4
                        dic(strKey) = Array("", "", "", "", vData(i, 4), vData(i, 5))
                    End Select
                End If
            Next i
        End With
        lCounter = lCounter + 2
    Next sh
    
    With Sheets("Master") '<--adjust sheet name
        .Columns("A:I").ClearContents
        .Range("A1:I1").Value = Array("State", "DOC", "Type", "Begin 1", "End 1", "Begin 2", "End 2", "Begin 3", "End 3")
        .Range("A2").Resize(dic.Count).Value = Application.Transpose(dic.keys)
        For i = 2 To dic.Count + 1
            .Range("D" & i).Resize(, 6) = dic(.Range("A" & i).Value)
        Next i
        .Range("A2").Resize(dic.Count).TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _
                OtherChar:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1))
    End With
End Sub

M.

 

[shonu1320]

Thanks Pete.

You assumptions are accurate. So the code is for Macro? Where will I copy it in my excel file?

 

[shonu1320]

Thank you. Could you please advice on where I would need to add this macro in my worksheet? Thanks!

 

[Peter_SSs]

So the code is for Macro? Where will I copy it in my excel file?

1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog, select the macro & click ‘Run’ **
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

** There are other ways to instigate the macro but this will do to see if it works for you.