Formula to find matching IP values in two sheets
Closed
c4rn3v1l
Posts
3
Registration date
Tuesday June 13, 2017
Status
Member
Last seen
June 21, 2017
-
Updated on Jun 22, 2017 at 06:17 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Jun 22, 2017 at 03:59 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Jun 22, 2017 at 03:59 AM
Related:
- Formula to find matching IP values in two sheets
- Number to words in excel formula - Guide
- What is my ip on my phone - Guide
- Date formula in excel dd/mm/yyyy - Guide
- Logitech formula vibration feedback wheel driver - Download - Drivers
- Sheets right to left - Guide
2 responses
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Jun 14, 2017 at 07:03 AM
Jun 14, 2017 at 07:03 AM
Hello c4rn3v1l,
Try the following code, placed in a standard module and assigned to a button:-
The code should find matching values in Column A of both sheets 1 and 2 and then transfer the relevant row of data from sheet1 to sheet 3.
Run the code from Sheet 1.
I hope that this helps.
Cheerio,
vcoolio.
Try the following code, placed in a standard module and assigned to a button:-
Sub CompareSheets()
Application.ScreenUpdating = False
Dim cell As Range
Dim sValue As Range
For Each cell In Sheet1.Range("A2", Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp))
Set sValue = Sheet2.Columns("A:A").Find(cell.Value)
If sValue Is Nothing Then GoTo NextCell
If cell.Value = sValue.Value Then
Sheet1.Range(Cells(cell.Row, "A"), Cells(cell.Row, "F")).Copy Sheet3.Range("A" & Rows.Count).End(3)(2)
End If
NextCell:
Next cell
Sheet3.Select
Application.ScreenUpdating = True
End Sub
The code should find matching values in Column A of both sheets 1 and 2 and then transfer the relevant row of data from sheet1 to sheet 3.
Run the code from Sheet 1.
I hope that this helps.
Cheerio,
vcoolio.
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Jun 22, 2017 at 03:59 AM
Jun 22, 2017 at 03:59 AM
Hello C4rn3v1l,
To check for differences in two sheets, we may have to change tack a little and reference one sheet from the other and vice versa. So try the following code ( untested ):-
Again, the code assumes that you are looking for differences in Column A only. If you need to check for differences over entire rows, then let me know.
I hope that this helps.
Cheerio,
vcoolio.
To check for differences in two sheets, we may have to change tack a little and reference one sheet from the other and vice versa. So try the following code ( untested ):-
Sub CompareSheets2()
Application.ScreenUpdating = False
Dim lr As Long
Dim lCol As Long
Dim i As Integer
lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
lCol = Sheet1.Cells(2, Columns.Count).End(xlToLeft).Column
Sheet1.Select
For i = 2 To lr
If Sheet2.Cells(i, 1).Value <> Sheet1.Cells(i, 1).Value Then
Sheet1.Range(Cells(i, 1), Cells(i, lCol)).Copy Sheet3.Range("A" & Rows.Count).End(3)(2)
End If
Next
Sheet2.Select
For i = 2 To lr
If Sheet1.Cells(i, 1).Value <> Sheet2.Cells(i, 1).Value Then
Sheet2.Range(Cells(i, 1), Cells(i, lCol)).Copy Sheet3.Range("A" & Rows.Count).End(3)(2)
End If
Next
Sheet3.Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Again, the code assumes that you are looking for differences in Column A only. If you need to check for differences over entire rows, then let me know.
I hope that this helps.
Cheerio,
vcoolio.
Jun 15, 2017 at 11:32 AM
Jun 15, 2017 at 10:05 PM
Cheerio,
vcoolio.
Jun 21, 2017 at 12:58 PM
"Sheet1.Range(Cells(cell.Row, "A"), Cells(cell.Row, "F")).Copy Sheet3.Range("A" & Rows.Count).End(3)(2)" is where im getting the error