Find a Min positive value
 

I need some help.
I have two columns with data. In my case it is time - columns "One" and "Two"
I need to find for each value in Two the value in One, that is closest, i.e. I need to substract values from Two with each value from One and to get the MIN non negative value.
Example: 10:11 from "Two" is related to 10:09 from "One" because the min non negative value after a substraction is 0:02

One Two Result
10:00 10:01 0:01
10:03 10:03 0:00
10:06 10:11
10:09 10:13 0:02
10:12




Thanks in advance,

Yakimo

Find a Min positive value
 

Dim rng as Range
Dim cell1 as Range
Dim cell2 as Range
set rng = Range(Cells(2,1),Cells(2,1).End(xldown))
for each cell2 in Range(Cells(2,2),cells(2,2).End(xldown))
dblDiff = 1
for each cell1 in rng
if cell2-cell1 0 then
if cell2-cell1 < dblDiff then
dblDiff = cell2-cell1
end if
end if
Next
cell2.offset(0,1).value = dblDiff
cell2.offset(0,1).Numberformat:= "hh:mm"
Next

--
Regards,
Tom Ogilvy


"Yakimoto" wrote in message
...
I need some help.
I have two columns with data. In my case it is time - columns "One" and
"Two"
I need to find for each value in Two the value in One, that is closest, i.e.
I need to substract values from Two with each value from One and to get the
MIN non negative value.
Example: 10:11 from "Two" is related to 10:09 from "One" because the min non
negative value after a substraction is 0:02

OneTwoResult
10:0010:010:01
10:0310:030:00
10:0610:11
10:0910:130:02
10:12




Thanks in advance,

Yakimo

Find a Min positive value
 

Thanks, Tom

I was just thinking of some array formula in the result column, without
having to write an action macro


"Tom Ogilvy" wrote in message
...
Dim rng as Range
Dim cell1 as Range
Dim cell2 as Range
set rng = Range(Cells(2,1),Cells(2,1).End(xldown))
for each cell2 in Range(Cells(2,2),cells(2,2).End(xldown))
dblDiff = 1
for each cell1 in rng
if cell2-cell1 0 then
if cell2-cell1 < dblDiff then
dblDiff = cell2-cell1
end if
end if
Next
cell2.offset(0,1).value = dblDiff
cell2.offset(0,1).Numberformat:= "hh:mm"
Next

--
Regards,
Tom Ogilvy


"Yakimoto" wrote in message
...
I need some help.
I have two columns with data. In my case it is time - columns "One" and
"Two"
I need to find for each value in Two the value in One, that is closest,
i.e.
I need to substract values from Two with each value from One and to get
the
MIN non negative value.
Example: 10:11 from "Two" is related to 10:09 from "One" because the min
non
negative value after a substraction is 0:02

OneTwoResult
10:0010:010:01
10:0310:030:00
10:0610:11
10:0910:130:02
10:12




Thanks in advance,

Yakimo

Find a Min positive value
 

In C2
=min(if((B2-$A$2:$A$20)0,B2-$A$2:$A$20))

Entered with Ctrl+Shift+Enter rather than Enter
Then drag fill down column C.


"Yakimoto" wrote in message
...
Thanks, Tom

I was just thinking of some array formula in the result column, without
having to write an action macro


"Tom Ogilvy" wrote in message
...
Dim rng as Range
Dim cell1 as Range
Dim cell2 as Range
set rng = Range(Cells(2,1),Cells(2,1).End(xldown))
for each cell2 in Range(Cells(2,2),cells(2,2).End(xldown))
dblDiff = 1
for each cell1 in rng
if cell2-cell1 0 then
if cell2-cell1 < dblDiff then
dblDiff = cell2-cell1
end if
end if
Next
cell2.offset(0,1).value = dblDiff
cell2.offset(0,1).Numberformat:= "hh:mm"
Next

--
Regards,
Tom Ogilvy


"Yakimoto" wrote in message
...
I need some help.
I have two columns with data. In my case it is time - columns "One" and
"Two"
I need to find for each value in Two the value in One, that is closest,
i.e.
I need to substract values from Two with each value from One and to get
the
MIN non negative value.
Example: 10:11 from "Two" is related to 10:09 from "One" because the min
non
negative value after a substraction is 0:02

OneTwoResult
10:0010:010:01
10:0310:030:00
10:0610:11
10:0910:130:02
10:12




Thanks in advance,

Yakimo