Finding matching rows on two worksheets

Wire323 · Apr 13, 2006

I'm trying to compare rows on 2 worksheets. If there are matches then I want to take the rows off of the 1st worksheet and move it to a 3rd one.

My 1st worksheet consists of "admin calls." Column A is the phone number, H is the date, and F is the start time.

My 2nd worksheet consists of "all calls." Column I is the phone, B is the date, and C is the start time.

I want to look at each row of the 1st worksheet, and try to find a matching row on the 2nd. A match will mean the phone number, date, and start time are all the same. If it finds a match, then I want to copy the value of Column B on the 1st worksheet to COlumn M on the 2nd. I'll then take the row off of the 1st worksheet and move it to a 3rd worksheet (called "matches").

Any help will be greatly appreciated. This is the last piece of a project I'm working on to make our billing girl's life a lot easier.

If I need to explain anything better then please let me know. I'm posting this question in a few different forums.

Thanks.

viquarshaikh · Apr 13, 2006

Let's hope this works for you.

There are a couple of ways to do this. You could write a script or simply let excel do it via it's extensive list of formulas.

I'm sure there are several others here who'd have an alternative solution to this one.

I'm assuming that your first Sheet's Name (AdminSheet) is Sheet1 and second Sheet's name is Sheet2. If not, then change as you please in this formula

Code:

=IF(ISERROR(MATCH(I2,Sheet1!$A:$A,0)),"No Match",IF(AND(B2=INDIRECT("Sheet1!H"&MATCH(I2,Sheet1!$A:$A,0)),C2=INDIRECT("Sheet1!F"&MATCH(I2,Sheet1!$A:$A,0))),INDIRECT("Sheet1!M"&MATCH(I2,Sheet1!$A:$A,0)),"No Matching"))

011 · Apr 13, 2006

I have almost the same question :
In several sheets i have almost the same information
for example
sheet 1
david 45 calls ,time 66:45:12:held calls15
sarah 22 calls,time23:35:16 ; hel calls 12
in the sheet 2 i have the same names + some other names
What i want ?-summarize date of this sheets

Wire323 · Apr 13, 2006

I'm looking for a VBA solution. Thanks for the help so far.

Wire323 · Apr 13, 2006

I posted a sample of my workbook on Ozgrid.

I also explained what I'm trying to do in a little more detail.

Thanks.

HalfAce · Apr 13, 2006

Hello Wire323, welcome to the board.
Here are a couple of ideas that should work.
The first one is the faster of the two (only a single loop) but it will only work if there will never be more than one match on sheet1 for any phone number on sheet2.

Code:

Option Explicit

Sub FindMatchesDemo()
Dim LstRw1 As Long
Dim LstRw2 As Long
Dim i As Long
Dim srchRng As Range
Dim fCell As Range

LstRw1 = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
LstRw2 = Sheets("Sheet2").Cells(Rows.Count, "I").End(xlUp).Row
With Sheets("Sheet1")
    Set srchRng = Range(.Cells(2, 1), .Cells(LstRw1, 1))
End With

For i = LstRw2 To 2 Step -1
    On Error Resume Next
    Set fCell = srchRng.Find(Cells(i, "I"), LookAt:=xlWhole)
    If Not fCell Is Nothing Then
        If fCell.Offset(, 7) = Cells(i, "B") _
        And fCell.Offset(, 5) = Cells(i, "C") Then
            Cells(i, "M").Value = fCell.Offset(, 1).Value
            fCell.EntireRow.Copy _
            Sheets("matches").Cells(Rows.Count, "A").End(xlUp)(2, 1)
        End If
    End If
Next i

End Sub

If there can be more than one matching phone number on sheet1 for any phone number on sheet2, then this will be a little slower but more reliable as it runs a loop within a loop. (If your user has thousands of rows they'll notice some speed difference, otherwise probably not.)

Code:

Option Explicit
Sub FindMatchesDemo2()
Dim Rng1 As Range, Rng2 As Range
Dim c1 As Range, c2 As Range

With Sheets("Sheet1")
    Set Rng1 = Range(.Cells(2, "A"), .Cells(Rows.Count, "A").End(xlUp))
End With
With Sheets("Sheet2")
    Set Rng2 = Range(.Cells(2, "I"), .Cells(Rows.Count, "I").End(xlUp))
End With
For Each c2 In Rng2
    For Each c1 In Rng1
        If c1 = c2 _
        And c1.Offset(, 5) = c2.Offset(, -6) _
        And c1.Offset(, 7) = c2.Offset(, -7) Then
            c2.Offset(, 4) = c1.Offset(, 1)
            c1.EntireRow.Copy _
                Sheets("matches").Cells(Rows.Count, "A").End(xlUp)(2, 1)
        End If
    Next c1
Next c2

End Sub

The code goes in a standard module and is intended to be run with sheet2 being the active sheet.
It also assumes row 1 is used as a header row in all 3 sheets.
If I've understood your intent, (and your layout is just as you've described) then one or the other ought to get it done.

Hope it helps.

Wire323 · Apr 13, 2006

Looks great HalfAce. I'll try them out on an actual workbook and see how they do.

Thanks.

Wire323 · Apr 13, 2006

I couldn't use the first example because there are duplicate phone numbers on the first sheet.

The second example you gave works great! There are only 2 changes I need to make now.

1. When a match is found, I want to remove the row from Sheet1 and move it to "Matches," instead of copying it. The reason for this is that I'm trying to reduce the number of records that are looked at manually. Once the matches are removed, I want there to be as few records on Sheet1 as possible.

2. When I compare the times, I want a time that is +/- 15 to also be considered a match. (2234 and 2248 should be considered a match, but 1601 and 1617 should not). This is to account for human error when the times on Sheet1 are entered.

I can probably figure out #2 on my own, but I have no idea what syntax to use for #1.

Thanks for all the help so far. You're a life saver!!

Wire323 · Apr 13, 2006

I got the time to work by doing this:

Code:

 For Each c2 In Rng2
        For Each c1 In Rng1
        
            timelow = c2.Offset(, -6).Value - 15
            timehigh = c2.Offset(, -6).Value + 15
            
            If c1 = c2 _
                And c1.Offset(, 5) >= timelow _
                And c1.Offset(, 5) <= timehigh _
                And c1.Offset(, 7) = c2.Offset(, -7) Then
                c2.Offset(, 4) = c1.Offset(, 1)
                c1.EntireRow.Copy _
                    Sheets("Matches").Cells(Rows.Count, "A").End(xlUp)(2, 1)
            End If
        Next c1
    Next c2

I know it won't work exactly right, because even though 1058 and 1102 are only 4 minutes apart, it won't consider it a match because they are 44 numbers apart. I get the times exported from a database as numbers rather than times, and I'm sure it would be more trouble that it's worth to convert them both to times in order to compare them.

Now I'll see if I can figure out how to take the rows off of the first sheet when they are a match.

Wire323 · Apr 13, 2006

I think I was able to do #1. I just added this after it copied the row over:

Code:

c1.EntireRow.Delete Shift:=xlUp

Is that the correct way to do it?

Thanks again for the help HalfAcer. I wish there was something I could do for you in return.

Finding matching rows on two worksheets

Wire323

New Member

Excel Facts

viquarshaikh

Active Member

011

New Member

Wire323

New Member

Wire323

New Member

HalfAce

MrExcel MVP

Wire323

New Member

Wire323

New Member

Wire323

New Member

Wire323

New Member

Similar threads

Forum statistics

Share this page

Finding matching rows on two worksheets

New Member

Excel Facts

Active Member

New Member

New Member

New Member

MrExcel MVP

New Member

New Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock