PDA

View Full Version : [SOLVED] Formula for placing ascending and descending numbers



satish gubbi
02-02-2017, 11:58 PM
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
02-03-2017, 09:21 AM
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

18236



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
02-03-2017, 12:46 PM
See attached. Probably more convoluted than it need be.

satish gubbi
02-04-2017, 12:44 AM
Thank you very much Paul,

Macro working as intended.

satish gubbi
02-04-2017, 12:45 AM
Thank you very much P45cal, Formula is working as intended.

Paul_Hossler
02-05-2017, 11:10 AM
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