Combine 4 Sheets into 1

[idoon3y]

Hi,

My file has multiple sheets. The goal is to find an exact match in column C of sheet 4 to column M of sheet 1, sheet 2 and sheet 3.

Edit: Code formatting

Image of sheet 1:

https://imgur.com/a/pQQvGHL

Image of sheet 2:

https://imgur.com/a/lhUvRjz

Image of sheet 3:

https://imgur.com/a/YDQD5je

Image of sheet 4:

https://imgur.com/a/soox91q

Image of desired sheet 4 outcome:

https://imgur.com/a/1Ke1MZH

In theory, what I am trying to do is if X = Y, then X = Z.

For example, if we are working with cell C2 in sheet 4, where C2 = 107028. I want to find which row in sheet 1 M contains 107028. When a row from column M of sheet 1 = 107028, I want to take the data from that row in columns D:K of that row and make them the values for columns D:K in sheet 4 in the row that 107028 is on.

This process repeats for sheets 2 and 3. Find exact matches in sheet 2 row M to sheet 4 row C and subsequently sheet 3 row M value to sheet 4 row C.

I have attempted to loop through the sheet of "Print_Stats" (sheet 1 in my post's case), "Email_Stats" (sheet 2 in my post's case), and "Link_Stats" (sheet 3 in my posts case) into "Combined_Stats" (which in our case is sheet 4):

Code:

Sub Combine_Stats ()

'attempted loop

Dim p_id As Integer

Dim l_id As Integer

Dim e_id As Integer

Dim c_id As String

Dim i As Long

'get the number of the last row with data in all four sheets

lastRowcombined = Worksheets("Combined_Stats").Cells(Rows.Count, "C").End(xlUp).Row

lastRowprint = Worksheets("Print_Stats").Cells(Rows.Count, "M").End(xlUp).Row

lastRowemail = Worksheets("Email_Stats").Cells(Rows.Count, "M").End(xlUp).Row

LastRowlink = Worksheets("Link_Stats").Cells(Rows.Count, "M").End(xlUp).Row

'for every value in column M of From Print_Stats trying to find when the p_id = the c_id from column C in Combined_Stats sheet

For i = 1 To lastRowprint

If Worksheets("Print_Stats").Cells(i, "M").Value = Worksheets("Combined_Stats").Cells(i,"C").Value Then

Worksheets("Print_Stats").Range(Cells(i, "M")).Copy

Worksheets("Combined_Stats").Range("C" & lastRowcombined).PasteSpecial xlPasteValues

End If

Next

'for every value in column M of From Email_Stats trying to find when the p_id = the c_id from column C in Combined_Stats sheet. Pasting values to next available column

For i = 1 To lastRowemail

If Worksheets("Email_Stats").Cells(i, "M").Value = Worksheets("Combined_Stats").Cells(i,"C").Value Then

Worksheets("Email_Stats").Range(Cells(i, "M")).Copy

Worksheets("Combined_Stats").Range("C" & lastRowcombined).PasteSpecial xlPasteValues

End If

Next

'for every value in column M of From Link_Stats trying to find when the p_id = the c_id from column C in Combined_Stats sheet. Pasting values to next available column

For i = 1 To LastRowlink

If Worksheets("Link_Stats").Cells(i, "M").Value = Worksheets("Combined_Stats").Cells(i,"C").Value Then

Worksheets("Link_Stats").Range(Cells(i, "M")).Copy

Worksheets("Combined_Stats").Range("C" & lastRowcombined).PasteSpecial xlPasteValues

End If

Next

End Sub

 

[NdNoviceHlp]

Trial #4...

Sub Combine_Stats()
Dim i As Long, Cnt As Long, lastRowcombined As Long
Dim LastRow As Long, WsCnt As Integer
'get the number of the last row with data 
lastRowcombined = Worksheets(4).Cells(Rows.Count, "C").End(xlUp).Row
On Error GoTo erfix
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Cnt = 2 To lastRowcombined
For WsCnt = 1 To 3
LastRow = Worksheets(WsCnt).Cells(Rows.Count, "M").End(xlUp).Row
For i = 2 To LastRow
If Worksheets(WsCnt).Cells(i, "M").Value = Worksheets(4).Cells(Cnt, "C").Value Then
If WsCnt = 1 Then
With Worksheets(WsCnt)
.Range(.Cells(i, "D"), .Cells(i, "K")).Copy
End With
Worksheets(4).Range("D" & Cnt).PasteSpecial xlPasteValues
End If
If WsCnt = 2 Then
With Worksheets(WsCnt)
.Range(.Cells(i, "D"), .Cells(i, "K")).Copy
End With
Worksheets(4).Range("L" & Cnt).PasteSpecial xlPasteValues
End If
If WsCnt = 3 Then
With Worksheets(WsCnt)
.Range(.Cells(i, "D"), .Cells(i, "E")).Copy
End With
Worksheets(4).Range("T" & Cnt).PasteSpecial xlPasteValues
End If
Application.CutCopyMode = False
Exit For
End If
Next i
Next WsCnt
Next Cnt
erfix:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Dave

 

[idoon3y]

Trial #4...

Sub Combine_Stats()
Dim i As Long, Cnt As Long, lastRowcombined As Long
Dim LastRow As Long, WsCnt As Integer
'get the number of the last row with data
lastRowcombined = Worksheets(4).Cells(Rows.Count, "C").End(xlUp).Row
On Error GoTo erfix
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Cnt = 2 To lastRowcombined
For WsCnt = 1 To 3
LastRow = Worksheets(WsCnt).Cells(Rows.Count, "M").End(xlUp).Row
For i = 2 To LastRow
If Worksheets(WsCnt).Cells(i, "M").Value = Worksheets(4).Cells(Cnt, "C").Value Then
If WsCnt = 1 Then
With Worksheets(WsCnt)
.Range(.Cells(i, "D"), .Cells(i, "K")).Copy
End With
Worksheets(4).Range("D" & Cnt).PasteSpecial xlPasteValues
End If
If WsCnt = 2 Then
With Worksheets(WsCnt)
.Range(.Cells(i, "D"), .Cells(i, "K")).Copy
End With
Worksheets(4).Range("L" & Cnt).PasteSpecial xlPasteValues
End If
If WsCnt = 3 Then
With Worksheets(WsCnt)
.Range(.Cells(i, "D"), .Cells(i, "E")).Copy
End With
Worksheets(4).Range("T" & Cnt).PasteSpecial xlPasteValues
End If
Application.CutCopyMode = False
Exit For
End If
Next i
Next WsCnt
Next Cnt
erfix:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Dave

This seemed to work in the correct manner in terms of the correct data being pasted into the correct range in sheet 4. However there were a lot of values from sheet 4 column C that did not get any data pasted into them even though there were values on sheets 1/2/3.

 

[NdNoviceHlp]

Are there more than 1 values in each sheet that match the value from sheet 4 (ie. sheet 1 may have multiple matches for the same sheet 4 value)? Are the "Values" numbers or text? Dave

 

[idoon3y]

Are there more than 1 values in each sheet that match the value from sheet 4 (ie. sheet 1 may have multiple matches for the same sheet 4 value)? Are the "Values" numbers or text? Dave

Sometimes, yes. The values are calculated - so I believe they are text. Which would be easier for the solution - making them numbers or text?

 

[NdNoviceHlp]

If U have more than 1 match per sheet, there's no place to paste .... your pasting to the same row as the matching value. The code is designed to stop looking ("Exit For") after it finds a match being that there is only 1 row for matching data to be placed. Doesn't seem possible to achieve your desired outcome? Dave