Typo correction:

Enter this formula in A14 as an array using the key combo of
CTRL,SHIFT,ENTER and copy over to E13:

Should read:

Enter this formula in B13 as an array using the key combo of
CTRL,SHIFT,ENTER and copy over to E13:

Biff

"Biff" wrote in message
...
Well, ties opens up a big can of worms!

Consider this: the ties might be by more than one person on the same day.
See this screencap:

http://img325.imageshack.us/img325/4634/max9sy.jpg

The bordered box is where the data has been extracted to.

To extract the dates you'll need a helper column (I hate helper columns!).
In this example I used column F and then hid that column.

Enter this formula in F2 and copy down to F10:

=IF(COUNTIF(B2:E2,MAX(B$2:E$10)),ROW(),"")

Enter this formula in A13 to extract the date(s):

=IF(ROWS($1:1)<=COUNT(F$2:F$10),INDEX(A$2:A$10,MAT CH(SMALL(F$2:F$10,ROWS($1:1)),F$2:F$10,0)),"")

Enter this formula in A14 as an array using the key combo of
CTRL,SHIFT,ENTER and copy over to E13:

=IF($A13="","",INDEX($B$1:$E$1,SMALL(IF(OFFSET(IND EX($A$2:$A$10,MATCH($A13,$A$2:$A$10,0)),,4,,-4)=MAX($B$2:$E$10),COLUMN($B1:$E1)-COLUMN($B1)+1),COLUMNS($A:A))))

Now, select A13:E13 then copy down to enough cells to account for all the
possible ties.

In the above formula, in the Offset function, the 4 and -4 are the number
of data columns in the table.

No error trapping/checking in the above formula. I used conditional
formatting to hide them.

Biff

"Dave Peterson" wrote in message
...
I think that I would never come up with a formula that could handle ties.
But
maybe a little user defined function would work for you???

Option Explicit
Function myLabel(rng As Range) As String

Dim myMax As Double
Dim TableRng As Range
Dim NumberOfMatches As Long

Dim mCtr As Long
Dim myStr As String

Dim iCol As Long
Dim iRow As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim FirstRow As Long
Dim LastRow As Long

With rng
Set TableRng = .Resize(.Rows.Count - 1, _
.Columns.Count - 1).Offset(1, 1)
End With

myMax = Application.Max(TableRng)
NumberOfMatches = Application.CountIf(TableRng, myMax)

mCtr = 0
myStr = ""
With TableRng
FirstCol = .Column
LastCol = .Cells(.Cells.Count).Column
FirstRow = .Row
LastRow = .Cells(.Cells.Count).Row
End With

With rng.Parent
For iCol = FirstCol To LastCol
For iRow = FirstRow To LastRow
If .Cells(iRow, iCol).Value = myMax Then
myStr = myStr & "; " & .Cells(iRow, FirstCol - 1).Text
_
& "--" & .Cells(FirstRow - 1, iCol).Text
mCtr = mCtr + 1
If mCtr = NumberOfMatches Then
Exit For
End If
End If
Next iRow
Next iCol
End With

If myStr = "" Then
'do nothing
Else
myStr = Mid(myStr, 3)
End If

myLabel = myStr

End Function

This goes in a general module.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Then you can use it like any other function:

=mylabel(G16:K19)

And returns something like:
01/01/2006--MR A; 03/01/2006--MR B; 02/01/2006--MR C



Matthew wrote:

Could still do with some help on this guys !! Please!

"Matthew" wrote:

Yes, you are right...it could be repeated...unlikely...but possible !
Dammit!!
I cant quite get the date formulae to work....could it be because my
data is
in the range B241..F267? I can get the name bit right no problem -

{=INDEX($B$241:$F$241,MIN(IF(C242:F267=MAX(C242:F2 67),COLUMN(C242:F267)-1)))}


"Biff" wrote:

I'll await a reply from the OP!

Biff

"Dave Peterson" wrote in message
...
Ooh. Excellent point.

I'll await one of your formulas!

Biff wrote:

What if there are duplicate max values?

Biff

"Matthew" wrote in message
...
Please help all you clever people !!

I have a table in the format:
MR A MR B MR C MR D
01/01/06 1 3 7 8
02/01/06 2 5 4 4
03/01/06 3 9 5 7

I want a formula to analyse the entire table (Which can change)
and to
return me who has achieved the Max value and on which date.

The answer here should be MR B on the 03/01/06.

PLEASE HELPPP!!!



--

Dave Peterson




--

Dave Peterson