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
Bookmarks