Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: how do u reproduce a column heading elsewhere?

  1. #11
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Are you only checking one row at a time? If not, here is an amendment to my previous offering, which will list all the names having the MAX value.

    Code:
    Function MAXVALUE(Rng As Range)
    Dim maxval As Double, usedcell As Range
    Dim answer As String, x As Long
    
    x = Rng.Row
    maxval = WorksheetFunction.Max(Rng)
    For Each usedcell In Rng
        If IsNumeric(usedcell) Then
            If usedcell = maxval Then
                On Error Resume Next
                If WorksheetFunction.Search(Cells(x, usedcell.Column), answer, 1) = 0 Then
                    answer = answer & Cells(x, usedcell.Column) & ", "
                End If
                On Error GoTo 0
            End If
        End If
    Next usedcell
    
    MAXVALUE = Left(answer, Len(answer) - 2)
    
    End Function
    Note that this will only return one instance of each name. If Dave, for example, twice had the max, Dave would show only once in the result.

  2. #12
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This formula will show any and ALL headings that have the max value.

    With my formula I used a1,b1,c1 as headings and a2,b2,c2 as values.

    I'm sure there is a prettier way to do this with array formulas but this works.

    =IF(A2=(MAX(A2:C2)),A1,"") & " " & IF(B2=(MAX(A2:C2)),B1,"") & " " & IF(C2=(MAX(A2:C2)),C1,"")

  3. #13
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    Jay,

    I never looked into this thread because of the topic title -- thinking it was VBA.

    See also:

    http://www.mrexcel.com/board/viewtop...7334&forum=2&3

    Aladin

  4. #14
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Aladin,

    I remember that thread! Nice job. Very clever and effective.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •