software

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!!!
To get Mr. B:
=INDEX(B1:E1,MIN(IF(B2:E4=MAX(B2:E4),COLUMN(B2:E4)-1)))
to get the 3/1/2006
=INDEX(A2:A4,MIN(IF(B2:E4=MAX(B2:E4),ROW(B2:E4)-1)))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column--oddly enough, you
can use the whole row.

Matthew wrote:
gt;
gt; Please help all you clever people !!
gt;
gt; I have a table in the format:
gt; MR A MR B MR C MR D
gt; 01/01/06 1 3 7 8
gt; 02/01/06 2 5 4 4
gt; 03/01/06 3 9 5 7
gt;
gt; I want a formula to analyse the entire table (Which can change) and to
gt; return me who has achieved the Max value and on which date.
gt;
gt; The answer here should be MR B on the 03/01/06.
gt;
gt; PLEASE HELPPP!!!

--

Dave Peterson

What if there are duplicate max values?

Biff

quot;Matthewquot; gt; wrote in message
...
gt; Please help all you clever people !!
gt;
gt; I have a table in the format:
gt; MR A MR B MR C MR D
gt; 01/01/06 1 3 7 8
gt; 02/01/06 2 5 4 4
gt; 03/01/06 3 9 5 7
gt;
gt; I want a formula to analyse the entire table (Which can change) and to
gt; return me who has achieved the Max value and on which date.
gt;
gt; The answer here should be MR B on the 03/01/06.
gt;
gt; PLEASE HELPPP!!!
gt;
gt;
Ooh. Excellent point.

I'll await one of your formulas!

Biff wrote:
gt;
gt; What if there are duplicate max values?
gt;
gt; Biff
gt;
gt; quot;Matthewquot; gt; wrote in message
gt; ...
gt; gt; Please help all you clever people !!
gt; gt;
gt; gt; I have a table in the format:
gt; gt; MR A MR B MR C MR D
gt; gt; 01/01/06 1 3 7 8
gt; gt; 02/01/06 2 5 4 4
gt; gt; 03/01/06 3 9 5 7
gt; gt;
gt; gt; I want a formula to analyse the entire table (Which can change) and to
gt; gt; return me who has achieved the Max value and on which date.
gt; gt;
gt; gt; The answer here should be MR B on the 03/01/06.
gt; gt;
gt; gt; PLEASE HELPPP!!!
gt; gt;
gt; gt;

--

Dave Peterson

I'll await a reply from the OP!

Biff

quot;Dave Petersonquot; gt; wrote in message
...
gt; Ooh. Excellent point.
gt;
gt; I'll await one of your formulas!
gt;
gt; Biff wrote:
gt;gt;
gt;gt; What if there are duplicate max values?
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;Matthewquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Please help all you clever people !!
gt;gt; gt;
gt;gt; gt; I have a table in the format:
gt;gt; gt; MR A MR B MR C MR D
gt;gt; gt; 01/01/06 1 3 7 8
gt;gt; gt; 02/01/06 2 5 4 4
gt;gt; gt; 03/01/06 3 9 5 7
gt;gt; gt;
gt;gt; gt; I want a formula to analyse the entire table (Which can change) and to
gt;gt; gt; return me who has achieved the Max value and on which date.
gt;gt; gt;
gt;gt; gt; The answer here should be MR B on the 03/01/06.
gt;gt; gt;
gt;gt; gt; PLEASE HELPPP!!!
gt;gt; gt;
gt;gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
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)))}quot;Biffquot; wrote:

gt; I'll await a reply from the OP!
gt;
gt; Biff
gt;
gt; quot;Dave Petersonquot; gt; wrote in message
gt; ...
gt; gt; Ooh. Excellent point.
gt; gt;
gt; gt; I'll await one of your formulas!
gt; gt;
gt; gt; Biff wrote:
gt; gt;gt;
gt; gt;gt; What if there are duplicate max values?
gt; gt;gt;
gt; gt;gt; Biff
gt; gt;gt;
gt; gt;gt; quot;Matthewquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; Please help all you clever people !!
gt; gt;gt; gt;
gt; gt;gt; gt; I have a table in the format:
gt; gt;gt; gt; MR A MR B MR C MR D
gt; gt;gt; gt; 01/01/06 1 3 7 8
gt; gt;gt; gt; 02/01/06 2 5 4 4
gt; gt;gt; gt; 03/01/06 3 9 5 7
gt; gt;gt; gt;
gt; gt;gt; gt; I want a formula to analyse the entire table (Which can change) and to
gt; gt;gt; gt; return me who has achieved the Max value and on which date.
gt; gt;gt; gt;
gt; gt;gt; gt; The answer here should be MR B on the 03/01/06.
gt; gt;gt; gt;
gt; gt;gt; gt; PLEASE HELPPP!!!
gt; gt;gt; gt;
gt; gt;gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt;
gt;
gt;

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

quot;Matthewquot; wrote:

gt; Yes, you are right...it could be repeated...unlikely...but possible ! Dammit!!
gt; I cant quite get the date formulae to work....could it be because my data is
gt; in the range B241..F267? I can get the name bit right no problem -
gt;
gt; {=INDEX($B$241:$F$241,MIN(IF(C242:F267=MAX(C242:F2 67),COLUMN(C242:F267)-1)))}
gt;
gt;
gt; quot;Biffquot; wrote:
gt;
gt; gt; I'll await a reply from the OP!
gt; gt;
gt; gt; Biff
gt; gt;
gt; gt; quot;Dave Petersonquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Ooh. Excellent point.
gt; gt; gt;
gt; gt; gt; I'll await one of your formulas!
gt; gt; gt;
gt; gt; gt; Biff wrote:
gt; gt; gt;gt;
gt; gt; gt;gt; What if there are duplicate max values?
gt; gt; gt;gt;
gt; gt; gt;gt; Biff
gt; gt; gt;gt;
gt; gt; gt;gt; quot;Matthewquot; gt; wrote in message
gt; gt; gt;gt; ...
gt; gt; gt;gt; gt; Please help all you clever people !!
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; I have a table in the format:
gt; gt; gt;gt; gt; MR A MR B MR C MR D
gt; gt; gt;gt; gt; 01/01/06 1 3 7 8
gt; gt; gt;gt; gt; 02/01/06 2 5 4 4
gt; gt; gt;gt; gt; 03/01/06 3 9 5 7
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; I want a formula to analyse the entire table (Which can change) and to
gt; gt; gt;gt; gt; return me who has achieved the Max value and on which date.
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; The answer here should be MR B on the 03/01/06.
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; PLEASE HELPPP!!!
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt;
gt; gt;
gt; gt;

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 = quot;quot;
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 amp; quot;; quot; amp; .Cells(iRow, FirstCol - 1).Text _
amp; quot;--quot; amp; .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 = quot;quot; 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:
www.mvps.org/dmcritchie/excel/getstarted.htmThen 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:
gt;
gt; Could still do with some help on this guys !! Please!
gt;
gt; quot;Matthewquot; wrote:
gt;
gt; gt; Yes, you are right...it could be repeated...unlikely...but possible ! Dammit!!
gt; gt; I cant quite get the date formulae to work....could it be because my data is
gt; gt; in the range B241..F267? I can get the name bit right no problem -
gt; gt;
gt; gt; {=INDEX($B$241:$F$241,MIN(IF(C242:F267=MAX(C242:F2 67),COLUMN(C242:F267)-1)))}
gt; gt;
gt; gt;
gt; gt; quot;Biffquot; wrote:
gt; gt;
gt; gt; gt; I'll await a reply from the OP!
gt; gt; gt;
gt; gt; gt; Biff
gt; gt; gt;
gt; gt; gt; quot;Dave Petersonquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; Ooh. Excellent point.
gt; gt; gt; gt;
gt; gt; gt; gt; I'll await one of your formulas!
gt; gt; gt; gt;
gt; gt; gt; gt; Biff wrote:
gt; gt; gt; gt;gt;
gt; gt; gt; gt;gt; What if there are duplicate max values?
gt; gt; gt; gt;gt;
gt; gt; gt; gt;gt; Biff
gt; gt; gt; gt;gt;
gt; gt; gt; gt;gt; quot;Matthewquot; gt; wrote in message
gt; gt; gt; gt;gt; ...
gt; gt; gt; gt;gt; gt; Please help all you clever people !!
gt; gt; gt; gt;gt; gt;
gt; gt; gt; gt;gt; gt; I have a table in the format:
gt; gt; gt; gt;gt; gt; MR A MR B MR C MR D
gt; gt; gt; gt;gt; gt; 01/01/06 1 3 7 8
gt; gt; gt; gt;gt; gt; 02/01/06 2 5 4 4
gt; gt; gt; gt;gt; gt; 03/01/06 3 9 5 7
gt; gt; gt; gt;gt; gt;
gt; gt; gt; gt;gt; gt; I want a formula to analyse the entire table (Which can change) and to
gt; gt; gt; gt;gt; gt; return me who has achieved the Max value and on which date.
gt; gt; gt; gt;gt; gt;
gt; gt; gt; gt;gt; gt; The answer here should be MR B on the 03/01/06.
gt; gt; gt; gt;gt; gt;
gt; gt; gt; gt;gt; gt; PLEASE HELPPP!!!
gt; gt; gt; gt;gt; gt;
gt; gt; gt; gt;gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; Dave Peterson
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;

--

Dave Peterson

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:

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(),quot;quot;)

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

=IF(ROWS($1:1)lt;=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)),quot;quot;)

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

=IF($A13=quot;quot;,quot;quot;,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

quot;Dave Petersonquot; gt; wrote in message
...
gt;I think that I would never come up with a formula that could handle ties.
gt;But
gt; maybe a little user defined function would work for you???
gt;
gt; Option Explicit
gt; Function myLabel(rng As Range) As String
gt;
gt; Dim myMax As Double
gt; Dim TableRng As Range
gt; Dim NumberOfMatches As Long
gt;
gt; Dim mCtr As Long
gt; Dim myStr As String
gt;
gt; Dim iCol As Long
gt; Dim iRow As Long
gt; Dim FirstCol As Long
gt; Dim LastCol As Long
gt; Dim FirstRow As Long
gt; Dim LastRow As Long
gt;
gt; With rng
gt; Set TableRng = .Resize(.Rows.Count - 1, _
gt; .Columns.Count - 1).Offset(1, 1)
gt; End With
gt;
gt; myMax = Application.Max(TableRng)
gt; NumberOfMatches = Application.CountIf(TableRng, myMax)
gt;
gt; mCtr = 0
gt; myStr = quot;quot;
gt; With TableRng
gt; FirstCol = .Column
gt; LastCol = .Cells(.Cells.Count).Column
gt; FirstRow = .Row
gt; LastRow = .Cells(.Cells.Count).Row
gt; End With
gt;
gt; With rng.Parent
gt; For iCol = FirstCol To LastCol
gt; For iRow = FirstRow To LastRow
gt; If .Cells(iRow, iCol).Value = myMax Then
gt; myStr = myStr amp; quot;; quot; amp; .Cells(iRow, FirstCol - 1).Text
gt; _
gt; amp; quot;--quot; amp; .Cells(FirstRow - 1, iCol).Text
gt; mCtr = mCtr 1
gt; If mCtr = NumberOfMatches Then
gt; Exit For
gt; End If
gt; End If
gt; Next iRow
gt; Next iCol
gt; End With
gt;
gt; If myStr = quot;quot; Then
gt; 'do nothing
gt; Else
gt; myStr = Mid(myStr, 3)
gt; End If
gt;
gt; myLabel = myStr
gt;
gt; End Function
gt;
gt; This goes in a general module.
gt;
gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt;
gt;
gt; Then you can use it like any other function:
gt;
gt; =mylabel(G16:K19)
gt;
gt; And returns something like:
gt; 01/01/2006--MR A; 03/01/2006--MR B; 02/01/2006--MR C
gt;
gt;
gt;
gt; Matthew wrote:
gt;gt;
gt;gt; Could still do with some help on this guys !! Please!
gt;gt;
gt;gt; quot;Matthewquot; wrote:
gt;gt;
gt;gt; gt; Yes, you are right...it could be repeated...unlikely...but possible !
gt;gt; gt; Dammit!!
gt;gt; gt; I cant quite get the date formulae to work....could it be because my
gt;gt; gt; data is
gt;gt; gt; in the range B241..F267? I can get the name bit right no problem -
gt;gt; gt;
gt;gt; gt; {=INDEX($B$241:$F$241,MIN(IF(C242:F267=MAX(C242:F2 67),COLUMN(C242:F267)-1)))}
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; quot;Biffquot; wrote:
gt;gt; gt;
gt;gt; gt; gt; I'll await a reply from the OP!
gt;gt; gt; gt;
gt;gt; gt; gt; Biff
gt;gt; gt; gt;
gt;gt; gt; gt; quot;Dave Petersonquot; gt; wrote in message
gt;gt; gt; gt; ...
gt;gt; gt; gt; gt; Ooh. Excellent point.
gt;gt; gt; gt; gt;
gt;gt; gt; gt; gt; I'll await one of your formulas!
gt;gt; gt; gt; gt;
gt;gt; gt; gt; gt; Biff wrote:
gt;gt; gt; gt; gt;gt;
gt;gt; gt; gt; gt;gt; What if there are duplicate max values?
gt;gt; gt; gt; gt;gt;
gt;gt; gt; gt; gt;gt; Biff
gt;gt; gt; gt; gt;gt;
gt;gt; gt; gt; gt;gt; quot;Matthewquot; gt; wrote in message
gt;gt; gt; gt; gt;gt; ...
gt;gt; gt; gt; gt;gt; gt; Please help all you clever people !!
gt;gt; gt; gt; gt;gt; gt;
gt;gt; gt; gt; gt;gt; gt; I have a table in the format:
gt;gt; gt; gt; gt;gt; gt; MR A MR B MR C MR D
gt;gt; gt; gt; gt;gt; gt; 01/01/06 1 3 7 8
gt;gt; gt; gt; gt;gt; gt; 02/01/06 2 5 4 4
gt;gt; gt; gt; gt;gt; gt; 03/01/06 3 9 5 7
gt;gt; gt; gt; gt;gt; gt;
gt;gt; gt; gt; gt;gt; gt; I want a formula to analyse the entire table (Which can change)
gt;gt; gt; gt; gt;gt; gt; and to
gt;gt; gt; gt; gt;gt; gt; return me who has achieved the Max value and on which date.
gt;gt; gt; gt; gt;gt; gt;
gt;gt; gt; gt; gt;gt; gt; The answer here should be MR B on the 03/01/06.
gt;gt; gt; gt; gt;gt; gt;
gt;gt; gt; gt; gt;gt; gt; PLEASE HELPPP!!!
gt;gt; gt; gt; gt;gt; gt;
gt;gt; gt; gt; gt;gt; gt;
gt;gt; gt; gt; gt;
gt;gt; gt; gt; gt; --
gt;gt; gt; gt; gt;
gt;gt; gt; gt; gt; Dave Peterson
gt;gt; gt; gt;
gt;gt; gt; gt;
gt;gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
Typo correction:

gt;Enter this formula in A14 as an array using the key combo of
gt;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

quot;Biffquot; gt; wrote in message
...
gt; Well, ties opens up a big can of worms!
gt;
gt; Consider this: the ties might be by more than one person on the same day.
gt; See this screencap:
gt;
gt; img325.imageshack.us/img325/4634/max9sy.jpg
gt;
gt; The bordered box is where the data has been extracted to.
gt;
gt; To extract the dates you'll need a helper column (I hate helper columns!).
gt; In this example I used column F and then hid that column.
gt;
gt; Enter this formula in F2 and copy down to F10:
gt;
gt; =IF(COUNTIF(B2:E2,MAX(B$2:E$10)),ROW(),quot;quot;)
gt;
gt; Enter this formula in A13 to extract the date(s):
gt;
gt; =IF(ROWS($1:1)lt;=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)),quot;quot;)
gt;
gt; Enter this formula in A14 as an array using the key combo of
gt; CTRL,SHIFT,ENTER and copy over to E13:
gt;
gt; =IF($A13=quot;quot;,quot;quot;,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))))
gt;
gt; Now, select A13:E13 then copy down to enough cells to account for all the
gt; possible ties.
gt;
gt; In the above formula, in the Offset function, the 4 and -4 are the number
gt; of data columns in the table.
gt;
gt; No error trapping/checking in the above formula. I used conditional
gt; formatting to hide them.
gt;
gt; Biff
gt;
gt; quot;Dave Petersonquot; gt; wrote in message
gt; ...
gt;gt;I think that I would never come up with a formula that could handle ties.
gt;gt;But
gt;gt; maybe a little user defined function would work for you???
gt;gt;
gt;gt; Option Explicit
gt;gt; Function myLabel(rng As Range) As String
gt;gt;
gt;gt; Dim myMax As Double
gt;gt; Dim TableRng As Range
gt;gt; Dim NumberOfMatches As Long
gt;gt;
gt;gt; Dim mCtr As Long
gt;gt; Dim myStr As String
gt;gt;
gt;gt; Dim iCol As Long
gt;gt; Dim iRow As Long
gt;gt; Dim FirstCol As Long
gt;gt; Dim LastCol As Long
gt;gt; Dim FirstRow As Long
gt;gt; Dim LastRow As Long
gt;gt;
gt;gt; With rng
gt;gt; Set TableRng = .Resize(.Rows.Count - 1, _
gt;gt; .Columns.Count - 1).Offset(1, 1)
gt;gt; End With
gt;gt;
gt;gt; myMax = Application.Max(TableRng)
gt;gt; NumberOfMatches = Application.CountIf(TableRng, myMax)
gt;gt;
gt;gt; mCtr = 0
gt;gt; myStr = quot;quot;
gt;gt; With TableRng
gt;gt; FirstCol = .Column
gt;gt; LastCol = .Cells(.Cells.Count).Column
gt;gt; FirstRow = .Row
gt;gt; LastRow = .Cells(.Cells.Count).Row
gt;gt; End With
gt;gt;
gt;gt; With rng.Parent
gt;gt; For iCol = FirstCol To LastCol
gt;gt; For iRow = FirstRow To LastRow
gt;gt; If .Cells(iRow, iCol).Value = myMax Then
gt;gt; myStr = myStr amp; quot;; quot; amp; .Cells(iRow, FirstCol - 1).Text
gt;gt; _
gt;gt; amp; quot;--quot; amp; .Cells(FirstRow - 1, iCol).Text
gt;gt; mCtr = mCtr 1
gt;gt; If mCtr = NumberOfMatches Then
gt;gt; Exit For
gt;gt; End If
gt;gt; End If
gt;gt; Next iRow
gt;gt; Next iCol
gt;gt; End With
gt;gt;
gt;gt; If myStr = quot;quot; Then
gt;gt; 'do nothing
gt;gt; Else
gt;gt; myStr = Mid(myStr, 3)
gt;gt; End If
gt;gt;
gt;gt; myLabel = myStr
gt;gt;
gt;gt; End Function
gt;gt;
gt;gt; This goes in a general module.
gt;gt;
gt;gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt;gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt;gt;
gt;gt;
gt;gt; Then you can use it like any other function:
gt;gt;
gt;gt; =mylabel(G16:K19)
gt;gt;
gt;gt; And returns something like:
gt;gt; 01/01/2006--MR A; 03/01/2006--MR B; 02/01/2006--MR C
gt;gt;
gt;gt;
gt;gt;
gt;gt; Matthew wrote:
gt;gt;gt;
gt;gt;gt; Could still do with some help on this guys !! Please!
gt;gt;gt;
gt;gt;gt; quot;Matthewquot; wrote:
gt;gt;gt;
gt;gt;gt; gt; Yes, you are right...it could be repeated...unlikely...but possible !
gt;gt;gt; gt; Dammit!!
gt;gt;gt; gt; I cant quite get the date formulae to work....could it be because my
gt;gt;gt; gt; data is
gt;gt;gt; gt; in the range B241..F267? I can get the name bit right no problem -
gt;gt;gt; gt;
gt;gt;gt; gt; {=INDEX($B$241:$F$241,MIN(IF(C242:F267=MAX(C242:F2 67),COLUMN(C242:F267)-1)))}
gt;gt;gt; gt;
gt;gt;gt; gt;
gt;gt;gt; gt; quot;Biffquot; wrote:
gt;gt;gt; gt;
gt;gt;gt; gt; gt; I'll await a reply from the OP!
gt;gt;gt; gt; gt;
gt;gt;gt; gt; gt; Biff
gt;gt;gt; gt; gt;
gt;gt;gt; gt; gt; quot;Dave Petersonquot; gt; wrote in message
gt;gt;gt; gt; gt; ...
gt;gt;gt; gt; gt; gt; Ooh. Excellent point.
gt;gt;gt; gt; gt; gt;
gt;gt;gt; gt; gt; gt; I'll await one of your formulas!
gt;gt;gt; gt; gt; gt;
gt;gt;gt; gt; gt; gt; Biff wrote:
gt;gt;gt; gt; gt; gt;gt;
gt;gt;gt; gt; gt; gt;gt; What if there are duplicate max values?
gt;gt;gt; gt; gt; gt;gt;
gt;gt;gt; gt; gt; gt;gt; Biff
gt;gt;gt; gt; gt; gt;gt;
gt;gt;gt; gt; gt; gt;gt; quot;Matthewquot; gt; wrote in message
gt;gt;gt; gt; gt; gt;gt; ...
gt;gt;gt; gt; gt; gt;gt; gt; Please help all you clever people !!
gt;gt;gt; gt; gt; gt;gt; gt;
gt;gt;gt; gt; gt; gt;gt; gt; I have a table in the format:
gt;gt;gt; gt; gt; gt;gt; gt; MR A MR B MR C MR D
gt;gt;gt; gt; gt; gt;gt; gt; 01/01/06 1 3 7 8
gt;gt;gt; gt; gt; gt;gt; gt; 02/01/06 2 5 4 4
gt;gt;gt; gt; gt; gt;gt; gt; 03/01/06 3 9 5 7
gt;gt;gt; gt; gt; gt;gt; gt;
gt;gt;gt; gt; gt; gt;gt; gt; I want a formula to analyse the entire table (Which can change)
gt;gt;gt; gt; gt; gt;gt; gt; and to
gt;gt;gt; gt; gt; gt;gt; gt; return me who has achieved the Max value and on which date.
gt;gt;gt; gt; gt; gt;gt; gt;
gt;gt;gt; gt; gt; gt;gt; gt; The answer here should be MR B on the 03/01/06.
gt;gt;gt; gt; gt; gt;gt; gt;
gt;gt;gt; gt; gt; gt;gt; gt; PLEASE HELPPP!!!
gt;gt;gt; gt; gt; gt;gt; gt;
gt;gt;gt; gt; gt; gt;gt; gt;
gt;gt;gt; gt; gt; gt;
gt;gt;gt; gt; gt; gt; --
gt;gt;gt; gt; gt; gt;
gt;gt;gt; gt; gt; gt; Dave Peterson
gt;gt;gt; gt; gt;
gt;gt;gt; gt; gt;
gt;gt;gt; gt; gt;
gt;gt;
gt;gt; --
gt;gt;
gt;gt; Dave Peterson
gt;
gt;