Results 1 to 16 of 16

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

Threaded View

  1. #15
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

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

    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
    Last edited by sanram; 09-12-2016 at 08:11 PM. Reason: Wrong code added before

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Need formula to return value from table based on varying order of column headers
    By ncpcpa in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-13-2015, 02:44 PM
  2. How to return column headers for where certain values appear in each row
    By gc-spurs in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 03-16-2014, 07:05 PM
  3. Finding the lowest 2 values across two rows, return row headers
    By ronanmagee in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-04-2013, 06:08 AM
  4. Return Headers Based On Two Values
    By just2cruz in forum Excel General
    Replies: 6
    Last Post: 06-16-2013, 08:31 PM
  5. Return Headers Based On Two Values
    By just2cruz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-16-2013, 03:03 PM
  6. Concatenate matched values and separate by comma
    By scoffman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-15-2012, 10:36 AM
  7. Replies: 2
    Last Post: 05-09-2012, 01:30 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1