Formula to Sort a Subset of Conesecutive Positioned Values of an Array with Constant Values?

MEUserII

Board Regular
Joined
Oct 27, 2017
Messages
55
I am looking to figure out in Excel 2016 how to write a formula that will sort a subset of values in consecutive position within a larger array with constant values (zeros), but keep those zeros in their same positions thus keeping the number of values in the array the same?

Moreover, an actual example would be the following:
R/C
A
B
C
D
E
1
{0,0,0,44,31,25,53,12,0,0,0,0}
=>
{0,0,0,12,25,31,44,53,0,0,0,0}


2
3
4
5

<tbody>
</tbody>

Where A1={0,0,0,44,31,25,53,12,0,0,0,0}
What Excel formula would I use that would take the array in A1 and sort only the non-zero values (44,31,25,53,12) and keep the same number of zeros in their same positions, so as to return the desired result that I've entered in to C1?
Where C1={0,0,0,12,25,31,44,53,0,0,0,0}
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,919
Office Version
2007
Platform
Windows
Use this UDF

Code:
Function sort_nums(cell As String)
  Dim arrList As Object, a As Variant, i As Long
  Dim cad As String, aux1 As String, c As Variant, aux2 As String, seg As Boolean
  Set arrList = CreateObject("System.Collections.ArrayList")
  cad = Mid(cell, 2, Len(cell) - 2)
  For Each c In Split(cad, ",")
    If c = 0 Then
      If seg = False Then
        aux1 = aux1 & "0,"
      Else
        aux2 = aux2 & "0,"
      End If
    Else
      seg = True
      arrList.Add c
    End If
  Next
  arrList.Sort
  If aux2 <> "" Then aux2 = Left(aux2, Len(aux2) - 1)
  sort_nums = "{" & aux1 & Join(arrList.toArray, ",") & "," & aux2 & "}"
End Function
HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use TextOnly just like it was a built-in Excel function. For example,

In cell C1:

=sort_nums(A1)
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,800
Office Version
365
Platform
Windows
1. Are any 0s that exist always only at the start and end like your sample or can they be scattered throughout? For example, could you have the following and if so, what is the desired result?
{0,0,4,3,5,0,0,8,1,4,7,0}
(A few more samples and results might help)

2. How many numbers could there be in the array. Your one example has 12 but can that vary up/down a lot?

3. Is your Excel 2016 a stand-alone version or through Office 365?
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,919
Office Version
2007
Platform
Windows
Some adjustments

Code:
Function sort_nums(cell As String)
  Dim arrList As Object, cad As String, aux1 As String, c As Variant, aux2 As String
  Set arrList = CreateObject("System.Collections.ArrayList")
  cad = Mid(cell, 2, Len(cell) - 2)
  For Each c In Split(cad, ",")
    If c = 0 Then
      If arrList.Count = 0 Then aux1 = aux1 & "0," Else aux2 = aux2 & "0,"
    Else
      arrList.Add c
    End If
  Next
  arrList.Sort
  If aux2 <> "" Then aux2 = "," & Left(aux2, Len(aux2) - 1)
  sort_nums = "{" & aux1 & Join(arrList.toArray, ",") & aux2 & "}"
End Function
 

Forum statistics

Threads
1,078,435
Messages
5,340,261
Members
399,361
Latest member
Linford

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top