A new excel challenge

Hi folks,

I've run into a situation where I'm not sure what to do with it.

I have seven columns: A B C D F X and POP

beneath the letter colums are totals which can be of values 0 through
11.

What I ultimately want to do is have a formula in the pop cell
corresponding to each row of data which finds the maximum value and
shows the column name corresponding to it.

Following is an example of what I'd like POP to reflect based on
sample data:

A B C D F X POP
2 0 0 7 2 0 D
3 2 0 2 4 0 F
6 3 2 0 0 0 A

etc...

If anyone can give me an idea of which route to follow I'd appreciate
it.

Regards

Ben/ND

are the seven columns adjacent and these are labels found in row 1? So the
columns involved are A to G?

What about duplicate maximum values.

Looking for formula or macro?

--
Regards,
Tom Ogilvy

"Nick Danger" <nickdangr@hotmail.com> wrote in message
news:op5tk15jujac3q83r4eeblkfqoiq3pjsau@4ax.com...
> Hi folks,
>
> I've run into a situation where I'm not sure what to do with it.
>
> I have seven columns: A B C D F X and POP
>
> beneath the letter colums are totals which can be of values 0 through
> 11.
>
> What I ultimately want to do is have a formula in the pop cell
> corresponding to each row of data which finds the maximum value and
> shows the column name corresponding to it.
>
> Following is an example of what I'd like POP to reflect based on
> sample data:
>
> A B C D F X POP
> 2 0 0 7 2 0 D
> 3 2 0 2 4 0 F
> 6 3 2 0 0 0 A
>
> etc...
>
> If anyone can give me an idea of which route to follow I'd appreciate
> it.
>
> Regards
>
> Ben/ND

Nick,

Try this formula

=CHAR(MATCH(MAX(A2:F2),A2:F2,0)+64)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Nick Danger" <nickdangr@hotmail.com> wrote in message
news:op5tk15jujac3q83r4eeblkfqoiq3pjsau@4ax.com...
> Hi folks,
>
> I've run into a situation where I'm not sure what to do with it.
>
> I have seven columns: A B C D F X and POP
>
> beneath the letter colums are totals which can be of values 0 through
> 11.
>
> What I ultimately want to do is have a formula in the pop cell
> corresponding to each row of data which finds the maximum value and
> shows the column name corresponding to it.
>
> Following is an example of what I'd like POP to reflect based on
> sample data:
>
> A B C D F X POP
> 2 0 0 7 2 0 D
> 3 2 0 2 4 0 F
> 6 3 2 0 0 0 A
>
> etc...
>
> If anyone can give me an idea of which route to follow I'd appreciate
> it.
>
> Regards
>
> Ben/ND

The following macro will do the job, or you could rewrite it as a user
defined function:

Sub FindMaxCol()
Const Alph As String = "ABCDFX" 'The column labels you gave
Dim MaxCol As Long 'The col with the highest value so far
Dim MaxVal As Long 'The highest value so far
Dim RowIndex As Long 'The row of the current cell
Dim ColIndex As Long 'The colimn of the current cell

For RowIndex = 2 To Range("A65536").End(xlUp).Row 'go from row 2 to
last row
MaxVal = 0 'Highest val found so far
MaxCol = 1 'Col of highest value
For ColIndex = 1 To 6 'Examine each cell in current row
If Cells(RowIndex, ColIndex) > MaxVal Then
'Cell is max so far so save its value and col number
MaxVal = Cells(RowIndex, ColIndex)
MaxCol = ColIndex
End If
Next ColIndex 'Look at next col in this row
'Convert col number to col label & save it
Range("G" & RowIndex) = Mid(Alph, MaxCol, 1)
Next RowIndex 'Look at next row
End Sub



On Thu, 13 Oct 2005 13:20:05 -0400, Nick Danger <nickdangr@hotmail.com>
wrote:

>Hi folks,
>
>I've run into a situation where I'm not sure what to do with it.
>
>I have seven columns: A B C D F X and POP
>
>beneath the letter colums are totals which can be of values 0 through
>11.
>
>What I ultimately want to do is have a formula in the pop cell
>corresponding to each row of data which finds the maximum value and
>shows the column name corresponding to it.
>
>Following is an example of what I'd like POP to reflect based on
>sample data:
>
>A B C D F X POP
>2 0 0 7 2 0 D
>3 2 0 2 4 0 F
>6 3 2 0 0 0 A
>
>etc...
>
>If anyone can give me an idea of which route to follow I'd appreciate
>it.
>
>Regards
>
>Ben/ND