find "closest" match

[Bruce Bowler]

I'm having a brain cramp...

I have a workbook with 2 worksheets in it. Worksheet "all" has ~4500 rows
in it. Worksheet "some" has ~100 rows in it. There is a column called
"time" in both worksheets. They are both sorted ascending. Both are
column A.

For each row in "some", I want to find the row in "all" whose time is
closest to the time in "some". When I find that row, I want to set the
value in column B of "all" to the row number of the corresponding time in
"some" and I want to color all of that row in "all" red.

Is there an easy way to do what I want?

Thanks!
Bruce

--
+-------------------+---------------------------------------------------+
Bruce Bowler | Above all, we cannot afford to live in the present.
1.207.633.9600 | - Thoreau
(e-mail address removed) |
+-------------------+---------------------------------------------------+

 

[Nigel]

One qualifier to help with the logic, when you say closest do you mean less
than or more than?

eg
ALL has 11.50 and 12.10
SOME value = 12.00
which in ALL is closest?

 

[Bruce Bowler]

One qualifier to help with the logic, when you say closest do you mean less
than or more than?

eg
ALL has 11.50 and 12.10
SOME value = 12.00
which in ALL is closest?

Minimize the absolute value of the difference (so in your example, 12.1
is closest). In the event that ALL has 11.9 and 12.1 and some is 12.0,
either one is acceptable

--
+-------------------+---------------------------------------------------+
Bruce Bowler | In the absence of feedback, people tend to think
1.207.633.9600 | the worst. - Anonymous
(e-mail address removed) |
+-------------------+---------------------------------------------------+

 

[Bruce Bowler]

Minimize the absolute value of the difference (so in your example, 12.1
is closest). In the event that ALL has 11.9 and 12.1 and some is 12.0,
either one is acceptable

a couple of other tidbits that might simplify things...

the first ALL will ALWAYS be less than the first SOME
the last ALL will ALWAYS be greater than the last SOME

there will NEVER be adjacent SOMEs in the ALL

--
+-------------------+---------------------------------------------------+
Bruce Bowler | In theory, there is no difference between theory
1.207.633.9600 | and practice. In practice, there is. - Anon
(e-mail address removed) |
+-------------------+---------------------------------------------------+

 

[Nigel]

Hi Bruce
Sorry for delay - have been away - here is a solution that you could try.
It reads each value in Column A on a sheet named SOME, scans column A on
sheet named ALL. It seeks to minimise the absolute difference between the
two values, if the difference rises then the nearest time on ALL has passed
so the previous row is the closest match.
It finds the lowest closest match unless two sequential values on ALL return
the same difference, in which case the higher of the two is chosen.
It assumes that times on ALL are in order - add a sort if they migth not be!


Sub TimeMatch()
Dim shS As Worksheet, shA As Worksheet
Set shS = Worksheets("SOME")
Set shA = Worksheets("ALL")
Dim sLrow As Long, aLrow As Long
aLrow = shA.Cells(Rows.Count, 1).End(xlUp).Row
sLrow = shS.Cells(Rows.Count, 1).End(xlUp).Row
Dim dMin, tDiff, sIr As Long, aIr As Long
For sIr = 1 To sLrow
dMin = 1
For aIr = 1 To aLrow
' get difference and minimise value
tDiff = Abs(shS.Cells(sIr, 1) - shA.Cells(aIr, 1))
If tDiff < dMin Then dMin = tDiff

' if value rising minimal condition was met at last value
If dMin < 1 And tDiff > dMin Then
shA.Cells(aIr - 1, 2) = sIr
shA.Rows(aIr - 1).Font.ColorIndex = 3
Exit For
End If
Next
Next
End Sub