=MAX-Return Cell info

[Matthew]

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]

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

[Biff]

What if there are duplicate max values?

Biff

"Matthew" <[email protected]> wrote in message
news:[email protected]...
> 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]

Ooh. Excellent point.

I'll await one of your formulas!

Biff wrote:
>
> What if there are duplicate max values?
>
> Biff
>
> "Matthew" <[email protected]> wrote in message
> news:[email protected]...
> > 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

[Biff]

I'll await a reply from the OP!

Biff

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Ooh. Excellent point.
>
> I'll await one of your formulas!
>
> Biff wrote:
>>
>> What if there are duplicate max values?
>>
>> Biff
>>
>> "Matthew" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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

[Matthew]

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:F267),COLUMN(C242:F267)-1)))}


"Biff" wrote:

> I'll await a reply from the OP!
>
> Biff
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > Ooh. Excellent point.
> >
> > I'll await one of your formulas!
> >
> > Biff wrote:
> >>
> >> What if there are duplicate max values?
> >>
> >> Biff
> >>
> >> "Matthew" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
>
>
>

[Matthew]

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:F267),COLUMN(C242:F267)-1)))}
>
>
> "Biff" wrote:
>
> > I'll await a reply from the OP!
> >
> > Biff
> >
> > "Dave Peterson" <[email protected]> wrote in message
> > news:[email protected]...
> > > Ooh. Excellent point.
> > >
> > > I'll await one of your formulas!
> > >
> > > Biff wrote:
> > >>
> > >> What if there are duplicate max values?
> > >>
> > >> Biff
> > >>
> > >> "Matthew" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> > 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]

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:F267),COLUMN(C242:F267)-1)))}
> >
> >
> > "Biff" wrote:
> >
> > > I'll await a reply from the OP!
> > >
> > > Biff
> > >
> > > "Dave Peterson" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Ooh. Excellent point.
> > > >
> > > > I'll await one of your formulas!
> > > >
> > > > Biff wrote:
> > > >>
> > > >> What if there are duplicate max values?
> > > >>
> > > >> Biff
> > > >>
> > > >> "Matthew" <[email protected]> wrote in message
> > > >> news:[email protected]...
> > > >> > 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

[Biff]

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,MATCH(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(INDEX($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" <[email protected]> wrote in message
news:[email protected]...
>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:F267),COLUMN(C242:F267)-1)))}
>> >
>> >
>> > "Biff" wrote:
>> >
>> > > I'll await a reply from the OP!
>> > >
>> > > Biff
>> > >
>> > > "Dave Peterson" <[email protected]> wrote in message
>> > > news:[email protected]...
>> > > > Ooh. Excellent point.
>> > > >
>> > > > I'll await one of your formulas!
>> > > >
>> > > > Biff wrote:
>> > > >>
>> > > >> What if there are duplicate max values?
>> > > >>
>> > > >> Biff
>> > > >>
>> > > >> "Matthew" <[email protected]> wrote in message
>> > > >> news:[email protected]...
>> > > >> > 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

[Biff]

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" <[email protected]> wrote in message
news:[email protected]...
> 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,MATCH(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(INDEX($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" <[email protected]> wrote in message
> news:[email protected]...
>>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:F267),COLUMN(C242:F267)-1)))}
>>> >
>>> >
>>> > "Biff" wrote:
>>> >
>>> > > I'll await a reply from the OP!
>>> > >
>>> > > Biff
>>> > >
>>> > > "Dave Peterson" <[email protected]> wrote in message
>>> > > news:[email protected]...
>>> > > > Ooh. Excellent point.
>>> > > >
>>> > > > I'll await one of your formulas!
>>> > > >
>>> > > > Biff wrote:
>>> > > >>
>>> > > >> What if there are duplicate max values?
>>> > > >>
>>> > > >> Biff
>>> > > >>
>>> > > >> "Matthew" <[email protected]> wrote in message
>>> > > >> news:[email protected]...
>>> > > >> > 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
>
>

[Dave Peterson]

Just to make your headache, the OP said: "could it be because my data is in the
range B241..F267?"

With no testing at all (even just glimpsing at those formulas makes me weak in
the knees), do you need to adjust anything? <vbg>


Biff wrote:
>
> 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,MATCH(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(INDEX($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" <[email protected]> wrote in message
> news:[email protected]...
> >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:F267),COLUMN(C242:F267)-1)))}
> >> >
> >> >
> >> > "Biff" wrote:
> >> >
> >> > > I'll await a reply from the OP!
> >> > >
> >> > > Biff
> >> > >
> >> > > "Dave Peterson" <[email protected]> wrote in message
> >> > > news:[email protected]...
> >> > > > Ooh. Excellent point.
> >> > > >
> >> > > > I'll await one of your formulas!
> >> > > >
> >> > > > Biff wrote:
> >> > > >>
> >> > > >> What if there are duplicate max values?
> >> > > >>
> >> > > >> Biff
> >> > > >>
> >> > > >> "Matthew" <[email protected]> wrote in message
> >> > > >> news:[email protected]...
> >> > > >> > 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

--

Dave Peterson

[Biff]

>do you need to adjust anything? <vbg>

I can't do *ALL* the work!

Biff

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Just to make your headache, the OP said: "could it be because my data is
> in the
> range B241..F267?"
>
> With no testing at all (even just glimpsing at those formulas makes me
> weak in
> the knees), do you need to adjust anything? <vbg>
>
>
> Biff wrote:
>>
>> 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,MATCH(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(INDEX($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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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:F267),COLUMN(C242:F267)-1)))}
>> >> >
>> >> >
>> >> > "Biff" wrote:
>> >> >
>> >> > > I'll await a reply from the OP!
>> >> > >
>> >> > > Biff
>> >> > >
>> >> > > "Dave Peterson" <[email protected]> wrote in message
>> >> > > news:[email protected]...
>> >> > > > Ooh. Excellent point.
>> >> > > >
>> >> > > > I'll await one of your formulas!
>> >> > > >
>> >> > > > Biff wrote:
>> >> > > >>
>> >> > > >> What if there are duplicate max values?
>> >> > > >>
>> >> > > >> Biff
>> >> > > >>
>> >> > > >> "Matthew" <[email protected]> wrote in message
>> >> > > >> news:[email protected]...
>> >> > > >> > 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
>
> --
>
> Dave Peterson