Previous Topic Next Topic

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
1355
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 10, 2022
 - Jun 22, 2017 at 03:59 AM
I really need help figuring out this issue that I am not too sure that actually has a solution.

I have two separate spreadsheets (lets call them "sheet1" and "sheet2") both of which in column "A" have IP addresses listed.
What I need to do is if "sheet1" has a matching IP in "sheet2" i want to have a copy of the entire matching row from "sheet1" be put into a new sheet (sheet3).

Essentially If 'A' from sheet1 = 'A' from sheet2 then copy the matching row of information into sheet3 (sheet3 will become its own spreadsheet consisting of all the matches in IP from the other two sheets)

Thanks in advance for any and all help

2 replies

Answer 1 / 2
vcoolio
Posts
1355
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 10, 2022
 250
Jun 14, 2017 at 07:03 AM
Hello c4rn3v1l,

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.
1
c4rn3v1l
Posts
3
Registration date
Tuesday June 13, 2017
Status
Member
Last seen
June 21, 2017
Jun 15, 2017 at 11:32 AM
This worked like a dream sir, thank you so very much!
0
vcoolio
Posts
1355
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 10, 2022
 250 > c4rn3v1l
Posts
3
Registration date
Tuesday June 13, 2017
Status
Member
Last seen
June 21, 2017
Jun 15, 2017 at 10:05 PM
You're welcome. Glad that I was able to help.

Cheerio,
vcoolio.
0
c4rn3v1l
Posts
3
Registration date
Tuesday June 13, 2017
Status
Member
Last seen
June 21, 2017
Jun 21, 2017 at 12:58 PM
Well unfortunately I have run into a problem.I tried to reuse the same code after making a slight tweak, buy making it post the the differences in the two sheets to a 3rd sheet. So Im getting a run time error "424" object required 
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


"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
0
Answer 2 / 2
vcoolio
Posts
1355
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 10, 2022
 250
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 ):- 

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.
0