Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find "closest" match
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 | +-------------------+---------------------------------------------------+ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
find "closest" match
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? -- Cheers Nigel "Bruce Bowler" wrote in message ... 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 | +-------------------+---------------------------------------------------+ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
find "closest" match
On Mon, 11 Jul 2005 18:24:18 +0100, Nigel put fingers to keyboard and
said: 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 | +-------------------+---------------------------------------------------+ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
find "closest" match
On Mon, 11 Jul 2005 13:38:20 -0400, Bruce Bowler put fingers to keyboard
and said: On Mon, 11 Jul 2005 18:24:18 +0100, Nigel put fingers to keyboard and said: 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 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 | +-------------------+---------------------------------------------------+ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
find "closest" match
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 -- Cheers Nigel "Bruce Bowler" wrote in message ... On Mon, 11 Jul 2005 13:38:20 -0400, Bruce Bowler put fingers to keyboard and said: On Mon, 11 Jul 2005 18:24:18 +0100, Nigel put fingers to keyboard and said: 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 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 | +-------------------+---------------------------------------------------+ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Where is "open/tools/find/find files that match these criteria"? | Excel Discussion (Misc queries) | |||
match true exact "dd" vs. find next 5th character=y | Excel Discussion (Misc queries) | |||
How to replace "#N/A" w "0"when vlookup couldn't find the match? | Excel Discussion (Misc queries) | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Compare value to array of values, "best fit" (closest without going over, e.g. price is right) | Excel Programming |