Find a Min positive value

  • Thread starter Thread starter Yakimoto
  • Start date Start date
Y

Yakimoto

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


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
 
Thanks, Tom

I was just thinking of some array formula in the result column, without
having to write an action macro
 
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.
 
Back
Top