How to compare two sheet and paste the result in sheet 3
Category: [Excel] Demo Available
How to compare two sheet and paste the result in sheet 3
Requirement:
The user wants to compare sheet 1 each complete row with sheet 2 complete rows if the data are same so put true in sheet 3 and if it false then put false in sheet3.
Solution:
Code:
Sub CompareRows()
Application.ScreenUpdating = False
Dim bottomA1 As Long
bottomA1 = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Dim bottomA2 As Long
bottomA2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Dim lColumn1 As Long
Dim lColumn2 As Long
Dim Rng As Range, RngList As Object, x As Long, joinStr As String
Set RngList = CreateObject("Scripting.Dictionary")
For x = 1 To bottomA2
lColumn2 = Sheets("Sheet2").Cells(x, Sheets("Sheet2").Columns.Count).End(xlToLeft).Column
For Each Rng In Sheets("Sheet2").Range(Sheets("Sheet2").Cells(x, 1), Sheets("Sheet2").Cells(x, lColumn2))
joinStr = joinStr & Rng.Value
Next Rng
If Not RngList.Exists(joinStr) Then
RngList.Add joinStr, Nothing
End If
joinStr = ""
Next x
joinStr = ""
For x = 1 To bottomA1
lColumn1 = Sheets("Sheet1").Cells(x, Columns.Count).End(xlToLeft).Column
For Each Rng In Sheets("Sheet1").Range(Sheets("Sheet1").Cells(x, 1), Sheets("Sheet1").Cells(x, lColumn1))
joinStr = joinStr & Rng.Value
Next Rng
If Not RngList.Exists(joinStr) Then
Sheets("Sheet3").Cells(Sheets("Sheet3").Rows.Count, "A").End(xlUp).Offset(1, 0) = "Row " & x
Sheets("Sheet3").Cells(Sheets("Sheet3").Rows.Count, "B").End(xlUp).Offset(1, 0) = "False"
Else
Sheets("Sheet3").Cells(Sheets("Sheet3").Rows.Count, "A").End(xlUp).Offset(1, 0) = "Row " & x
Sheets("Sheet3").Cells(Sheets("Sheet3").Rows.Count, "B").End(xlUp).Offset(1, 0) = "True"
End If
joinStr = ""
Next x
RngList.RemoveAll
Application.ScreenUpdating = True
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by Mumps.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to copy the data from sheet 1 and paste to sheet 2 |
| How to paste a number as text |
| How to use VBA code to not copy and paste the same information |
| How to copy columns from multiple workbooks and paste into one worksheet |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.