Formula for placing ascending and descending numbers

[satish gubbi]

Hi

I have column A with variable (assorted) numbers, I need a formula to count column A (excluding Heading) and place ascending numbers from above to middle cell and descending numbers from below to top in column B.

I am attaching herewith the example file for quick reference.

Thanks in advance

[Paul_Hossler]

I can do it with a macro

Maybe some of the formula experts can do it with worksheet formulas

This is an array entered formula, that is you select the group of cells and when you enter the formula you use control+shift+enter which adds the {} for you. You do not enter them

Capture.JPG

Option Explicit
Function SortAndSplit(r As Range) As Variant
    Dim r1 As Range
    Dim v As Variant
    Dim temp As Double
    Dim i As Long, j As Long
    Dim O() As Variant
    
    Application.Volatile
    
    'put data into array
    v = Application.WorksheetFunction.Transpose(r.Cells(2, 1).Resize(r.Rows.Count - 1, 1).Value)
    'bubble sort
    For i = LBound(v) To UBound(v) - 1
        For j = i + 1 To UBound(v)
            If v(j) > v(i) Then
                temp = v(i)
                v(i) = v(j)
                v(j) = temp
            End If
        Next j
    Next i
    'create output array
    ReDim O(0 To UBound(v), 0 To 1)
    
    'add headers
    O(0, 0) = r.Cells(1, 1).Value
    O(0, 1) = "Asc/Des"
    
    'put the sorted values in first
    For i = LBound(v) To UBound(v)
        O(i, 0) = v(i)
    Next I
    
    For i = 1 To UBound(v) \ 2 ' integer division
         O(i, 1) = i
         O(UBound(v) - i + 1, 1) = I
     Next
    
    'odd number enteries
    If UBound(v) Mod 2 = 1 Then
        O((UBound(v) \ 2) + 1, 1) = (UBound(v) \ 2) + 1
    End If
    SortAndSplit = O
End Function

[p45cal]

See attached. Probably more convoluted than it need be.

[satish gubbi]

Thank you very much Paul,

Macro working as intended.

[satish gubbi]

Thank you very much P45cal, Formula is working as intended.

[Paul_Hossler]

Just a minor note - P45cal's WS formulas (I know some smart worksheet formula person could do it) assume that the values are already sorted high to low