Matching pairs based on criteria

Hi all,

On the attached spreadsheet is 28 rows of data, across columns A-M (with F to I hidden)

The data shown are pairs, based on their location and the times. The cell in column M is highlighted when there is a location and time match.

At times I can work with 000's of rows of data and then I need to number the pairs in column D, as shown.

A pair would be worked out based on column M being highlighted and the times in column C for the rows are within 10 minutes of a different coloured call (as shown by column A).

I have started numbering the instances to show an example, but for 20,000 rows where there could be 9,000 instances it is hard to do manually.

Any ideas how to automate the pairing?

How do you compare location. When I compare by time, within ten minutes, they all highlight

There is nothing in your data that looks like it would be a location, and every cell in column M is highlighted, so it's hard to figure out what your criteria are.

Hi guys,

sorry - hopefully this attachment helps more.

on sheet called "Schedule" there is some data. If you press the big blue square it runs the following code:

Sub All()

Dim d As Object
Dim lr As Long
Dim tr As Long
Dim nr As Long
Dim tk1 As String
Dim tk2 As String
Dim p1, p2
Dim s1 As Worksheet
Dim nv As Worksheet

Set s1 = Worksheets("Schedule")
Set nv = Worksheets("Near Values 2")

Set d = CreateObject("Scripting.Dictionary")
lr = nv.Cells(nv.Rows.Count, "A").End(xlUp).Row
For tr = 2 To lr
    tk1 = nv.Cells(tr, "C").Value & " " & nv.Cells(tr, "U").Value
    tk2 = nv.Cells(tr, "D").Value & " " & nv.Cells(tr, "V").Value
    d(tk1 & " " & tk2) = nv.Cells(tr, "E").Value
Next tr

lr = s1.Cells(s1.Rows.Count, "Q").End(xlUp).Row
For tr = 2 To lr - 1
    For nr = tr + 1 To lr
        If Int(s1.Cells(nr, "B").Value) + s1.Cells(nr, "C").Value - Int(s1.Cells(tr, "B").Value) - s1.Cells(tr, "C").Value <= TimeSerial(0, 10, 0) Then
            tk1 = s1.Cells(tr, "Q").Value & " " & s1.Cells(tr, "R").Value & " " & s1.Cells(tr, "M").Value
            tk2 = s1.Cells(nr, "Q").Value & " " & s1.Cells(nr, "R").Value & " " & s1.Cells(nr, "M").Value
            If s1.Cells(nr, "AD").Value <> s1.Cells(tr, "AD").Value _
            And (d.exists(tk1 & " " & tk2) Or d.exists(tk2 & " " & tk1)) Then
                s1.Cells(tr, "M").Interior.Color = RGB(255, 255, 0)
                s1.Cells(nr, "M").Interior.Color = RGB(255, 255, 0)
            End If
        Else
            Exit For
        End If
    Next nr
Next tr

End Sub

The code is looking to see if any of the location pairs in the "Near values 2" tab feature in the "Schedule" tab, where the time is within 10 minutes and the subject number is different (Column AD)

As you will see on the schedule tab between rows 2 and 3 there is a match to a pair in "Near Values 2" and the time is within 10 minutes. If you run the code, then the values in column M are highlighted yellow.

Also you will see that in the instance column there are two numbers 1s in here, with two number 2s in the next pair between rows 25 and 26.

I have manually added these numbers in, but want to automate it so that when the values in column M are highlighted yellow, the numbers are put in column D - so show a sequence.


Can anyone help please?