Find the closest match (duration value) in Excel based on multiple criteria

[Philippe97531]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]My estimate[/TD]
[TD]My estimate[/TD]
[TD]My estimate[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Runner[/TD]
[TD]Time[/TD]
[TD]Jane[/TD]
[TD]John[/TD]
[TD]Chris[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jane[/TD]
[TD]1:40:00[/TD]
[TD]1:41:00[/TD]
[TD]1:39:50[/TD]
[TD]1:43:00[/TD]
[TD]=INDEX($C2:$E$2;MATCH(MIN(ABS(C3:E3-B3));ABS(C3:E3-B3);0))[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]John[/TD]
[TD]1:30:00[/TD]
[TD]1:32:00[/TD]
[TD]1:32:30[/TD]
[TD]1:28:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Chris[/TD]
[TD]1:46:00[/TD]
[TD]1:48:00[/TD]
[TD]1:52:00[/TD]
[TD]1:44:00[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Would love to have a single formula that points to the person whose estimate is closest to the actual running time of fellow half marathon runners.

This one works:
=INDEX(C3:E3;MATCH(MIN(ABS(C2:E2-B3));ABS(C2:E2-B3);0)) (CSE array formula, obviously). But it won't find more than one correct estimate (we're a group of 30 runners so it's possible two or more people will have the same estimate).

Even better would be to simply have the best estimate cell(s) highlighted, including the persons' name, but conditional formatting doesn't allow for array formulas.

Note 1: not everyone is going to estimate everyone's time, so some cells will be empty. (don't think that'll matter though)
Note 2: of course more than one person can be closest, or exactly estimating. This doesn't work with my formula.
Note 3: I'm on a Mac (Excel 2016 [v15.31]), so replace the semicolon with a comma if you're on Windows.

Any clever Excel guru out there?

BIG thanks for your time!

 

[Coding4Fun]

So are you saying that the sheet is actually 32 Rows by 32 Columns + The Formula column?

Do you require a Formula or are you open to VBA?

 

[Philippe97531]

So are you saying that the sheet is actually 32 Rows by 32 Columns + The Formula column?

Do you require a Formula or are you open to VBA?

1) BIG thanks for responding this quick!

2) Yup, 32 rows and columns. Unfortunately I don't have any VBA experience. I can look at it, but hardly write something like that myself. A formula would be great. I'm now using a workaround to the disallowance of an array formula for the CF bit by simply adding an extra column. This may be even better as is clearly shows whose estimate it was, and allows me to also show the number of seconds their time differs.

3) The CF stops at the first 'hit' (the closest match). But if there are 'two hits' the CF only applies to the first time it finds...

 

[Coding4Fun]

Ok this may look a little crazy but this should work, I set up a mock Template of what you have and expanded it to 31 Users
The spreadsheet should be set up as you have in in your example meaning the Names for the guesses should be in Row 2 starting with Column C

Your Result box should be on the far right side of your sheet in Column AH with the header on Row 2

Then you will create a Macro and paste the following code

Then once all the guesses are populated you can run this and it will give you the winner for each row displayed in column AH. If there is more than one winner it will display all winners.

Sub Tester()

' Storing the base time
Dim myArray1(1 To 31) As Variant


' Storing the Names
Dim myArray2(1 To 31) As Variant


'Storing the Guess
Dim myArray3(1 To 31) As Variant


'Storing the diff between guess and actual
Dim myArray4(1 To 31) As Double


Dim TrimResult As String
Dim NewResult As Double


BaseRow = 3


For m = 1 To 31


GuessCol = 3
NameRow = 2
CheckCol = 3


For i = 1 To 31


myArray1(i) = Cells(BaseRow, 2).Value
myArray2(i) = Cells(NameRow, CheckCol).Value
myArray3(i) = Cells(BaseRow, GuessCol).Value


Sheets("Sheet2").Cells(i, 1) = myArray1(i)
Sheets("Sheet2").Cells(i, 2) = myArray2(i)
Sheets("Sheet2").Cells(i, 3) = myArray3(i)


CheckCol = CheckCol + 1
GuessCol = GuessCol + 1


myArray4(i) = DateDiff("h", myArray1(i), myArray3(i)) + DateDiff("m", myArray1(i), myArray3(i)) + DateDiff("s", myArray1(i), myArray3(i))


Sheets("Sheet2").Cells(i, 4) = myArray4(i)




TrimResult = Sheets("Sheet2").Cells(i, 4)
NewResult = Replace(TrimResult, "-", "")
Sheets("Sheet2").Cells(i, 5) = NewResult


Next i


Sheets("Sheet2").Activate
Sheets("Sheet2").Range("E1:E31").Copy
Range("F1").Select


Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False


ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("E1:E32") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet2").Sort
        .SetRange Range("A1:G32")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


Dim myArray5(1 To 31, 2 To 6) As String
Dim Winner As String


ResultRow = 1




For h = 1 To 31


PostResult = 9




For j = 2 To 6


myArray5(h, j) = Sheets("Sheet2").Cells(h, j)


Sheets("Sheet2").Cells(h, PostResult) = myArray5(h, j)
Sheets("Sheet2").Cells(h, 8) = myArray5(h, 2) & " - " & Format(myArray5(h, 3), "hh:mm:ss") & " - " & myArray5(h, 6)


PostResult = PostResult + 1


Next j


Winner = Sheets("Sheet2").Cells(h, 8).Text


If Sheets("Sheet2").Cells(1, 14).Value = "" Then
Sheets("Sheet2").Cells(1, 14).Value = Winner
Else
Sheets("Sheet2").Cells(1, 14).Value = Winner & vbNewLine & Sheets("Sheet2").Cells(1, 14).Value
End If


Sheets("Sheet2").Cells(h, 10).Value = Format(Sheets("Sheet2").Cells(h, 10), "hh:mm:ss")


If Sheets("Sheet2").Range("F" & ResultRow) <> Sheets("Sheet2").Range("F" & ResultRow + 1) Then
Exit For
End If


ResultRow = ResultRow + 1
Next h


Sheets("Sheet1").Range("AH" & BaseRow) = Trim(Sheets("Sheet2").Range("N1").Text)


Sheets("Sheet1").Activate
Range("AH" & BaseRow).Select


    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
BaseRow = BaseRow + 1


Sheets("Sheet2").Cells.Delete
Sheets("Sheet1").Activate




Next m


End Sub

 

[Philippe97531]

Ok this may look a little crazy but this should work, I set up a mock Template of what you have and expanded it to 31 Users [...]

Wow! Thanks ever so much for helping out here! Eager to get home early today and apply this. You're a BIG help; much appreciated.

Cheers,
Phil

 

[Philippe97531]

That works a charm sir! It creates a new column with the winners, and if there are two they are both listed within the same cell. All with their estimated time. Thanks ever so much; really appreciate the work and help here.

Wish I could be of assistance to return the favour. Ask me any tech question and I'll see if I can find an answer if I don't know it myself.

Best Regards,
Phil

 

[Coding4Fun]

Glad I could help