top 3 and last 3 values in a table should return row headers concatenate with comma

Dear Experts / Members,

I have a table with values (rows and columns), from the table I have to find top - 3 which should return their row heading with in a cell concatenated with comma for the 3 values and also for last 3 values.

Tried with large, index, match, offset but, no luck.

Please find attached the image for better understanding.
Untitled.png

Thanks in advance

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context. Pictures are rarely much use and not many of us are prepared to recreate a workbook for testing when you already have one.

Deleted!!!!!!!!!!!!

Originally Posted by shukla.ankur281190

Deleted!!!!!!!!!!!!

Does that mean you have a solution? In which case would you care to share it with the rest of the forum members?

Dear experts,
My apology for the inconvenience and or not followed the forum rule. Will make sure, from next time it won't repeat.

Please find below the table and the output.

Source Scale-1 Scale-2 Scale-3 Scale-4 Scale-5 Output
Wave 77 94 77 77 79 Best 3 Scale-2, Scale-3, Scale-1
Rock 76 76 76 76 76 Worse 3 Scale-2, Scale-3, Scale-5
Build 76 76 93 76 76
N-wave 89 70 70 89 70
N-build 72 72 72 72 72

I couldnt able to attach a file, please guide.

I find how to upload the file, once again please accept my apology for wasting many experts time.

I have attached the workbook.

Thanks in advance.

Getting your question to this point has made my brain hurt. I haven't even tried to concatenate the results.

Can you live with this? They are array formulae.



Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

No doubt a VBA person will now come along, write 10 lines of code and make everything perfect. But this is a start???!!!

Hi Glenn,

I am sorry for that.
You are awesome with the formula, Mr. Glenn. No words to express the gratitude.
To say frank, I am not that good at the formulas so, understanding the solution you have provided will have crack my mind.

Thanks, Glenn.
As you said, if someone comes up with coding that too helps.

Enjoy getting a sore brain. I am away from my PC for a while. I just thought of a possible problem. Is it possible to have two equal values in the same COLUMN? If so, I will need to make a modification.

Glenn, as of now my criteria is to have 3 unique results in one cell. What you have asked is out of box for me. But, it sounds cool to have equal values in same column. Hope, this time you won't hurt your brain.

My mistake. there's no issue here. It's working fine for highest and lowest 3, irrespective of whether duplicates are in columns or rows.

See attached file...

No VBA Guru response do far. Here's a cheat to get to the same place....

You can use this code for that if you want to avoid duplicates :

Sub TopBelow()
    Dim C As Range
    Dim arr As New Collection
    Dim i As Long
    Dim SArray() As Variant
    
    Set arr = New Collection
    On Error Resume Next

    For Each C In Range("B2:F6")
        arr.Add C.Value, CStr(C.Value)
    Next C
    
    ReDim SArray(arr.Count)
    For i = 1 To arr.Count
        SArray(i) = arr(i)
    Next i
    
    TFirst = WorksheetFunction.Large(SArray, 1)
    TSecond = WorksheetFunction.Large(SArray, 2)
    TThird = WorksheetFunction.Large(SArray, 3)
    
    BFirst = WorksheetFunction.Small(SArray, 1)
    BSecond = WorksheetFunction.Small(SArray, 2)
    BThird = WorksheetFunction.Small(SArray, 3)
    
    For Each C In Range("B2:F6")
        If C.Value = TFirst Then
            If TFirstCol = "" Then TFirstCol = C.Column
        End If
        If C.Value = TSecond Then
            If TSecondCol = "" Then TSecondCol = C.Column
        End If
        If C.Value = TThird Then
            If TThirdCol = "" Then TThirdCol = C.Column
        End If
        If C.Value = BFirst Then
            If BFirstCol = "" Then BFirstCol = C.Column
        End If
        If C.Value = BSecond Then
            If BSecondCol = "" Then BSecondCol = C.Column
        End If
        If C.Value = BThird Then
            If BThirdCol = "" Then BThirdCol = C.Column
        End If
    Next C
    
    Range("I2") = Cells(1, TFirstCol) & ", " & Cells(1, TSecondCol) & ", " & Cells(1, TThirdCol)
    Range("I3") = Cells(1, BFirstCol) & ", " & Cells(1, BSecondCol) & ", " & Cells(1, BThirdCol)
End Sub

Also the code without avoiding the duplicates :

Sub TopBelow()
    Dim C As Range
    
    TFirst = WorksheetFunction.Large(Range("B2:F6"), 1)
    TSecond = WorksheetFunction.Large(Range("B2:F6"), 2)
    TThird = WorksheetFunction.Large(Range("B2:F6"), 3)
    
    BFirst = WorksheetFunction.Small(Range("B2:F6"), 1)
    BSecond = WorksheetFunction.Small(Range("B2:F6"), 2)
    BThird = WorksheetFunction.Small(Range("B2:F6"), 3)
    
    For Each C In Range("B2:F6")
        If C.Value = TFirst Then
            If TFirstCol = "" Then
                TFirstCol = C.Column
            Else
                TFi = TFi + 1
            End If
        End If
        If C.Value = TSecond Then
            If TFirst = TSecond Then
                If TFi > 0 Then
                    If TSecondCol = "" Then
                        TSecondCol = C.Column
                    Else
                        TSi = TSi + 1
                    End If
                End If
            Else
                If TSecondCol = "" Then
                    TSecondCol = C.Column
                Else
                    TSi = TSi + 1
                End If
            End If
        End If
        If C.Value = TThird Then
            If TSecond = TThird Then
                If TSi > 0 Then
                    If TThirdCol = "" Then TThirdCol = C.Column
                End If
            Else
                If TThirdCol = "" Then TThirdCol = C.Column
            End If
        End If
        If C.Value = BFirst Then
            If BFirstCol = "" Then
                BFirstCol = C.Column
            Else
                BFi = BFi + 1
            End If
        End If
        If C.Value = BSecond Then
            If BFirst = BSecond Then
                If BFi > 0 Then
                    If BSecondCol = "" Then
                        BSecondCol = C.Column
                    Else
                        BSi = BSi + 1
                    End If
                End If
            Else
                If BSecondCol = "" Then
                    BSecondCol = C.Column
                Else
                    BSi = BSi + 1
                End If
            End If
        End If
        If C.Value = BThird Then
            If BSecond = BThird Then
                If BSi > 0 Then
                    If BThirdCol = "" Then BThirdCol = C.Column
                End If
            Else
                If BThirdCol = "" Then BThirdCol = C.Column
            End If
        End If
    Next C
    
    Range("I2") = Cells(1, TFirstCol) & ", " & Cells(1, TSecondCol) & ", " & Cells(1, TThirdCol)
    Range("I3") = Cells(1, BFirstCol) & ", " & Cells(1, BSecondCol) & ", " & Cells(1, BThirdCol)
End Sub