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

MEUserII

Board Regular
Joined
Oct 27, 2017
Messages
65
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}
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. 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
48,479
Office Version
  1. 365
Platform
  1. 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
12,619
Office Version
  1. 2007
Platform
  1. 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
 

MEUserII

Board Regular
Joined
Oct 27, 2017
Messages
65

ADVERTISEMENT

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?
For reference: a_1, a_2, a_3,...,a_n; represent the natural numbers (whole numbers not including zero).

1) I am only dealing with arrays of the form: {0, 0, 0, . . . , 0, a_1, a_2, a_3, . . . , a_n, 0, 0, 0, . . . , 0}; at the moment; but eventually, I would like to also find a solution to the array you posted about: {0, 0, 4, 3, 5, 0, 0, 8, 1, 4, 7, 0} which would be turned in to {0, 0, 1, 3, 4, 0, 0, 4, 5, 7, 8, 0} as the desired final result.

2) I would say the amount of numbers in the arrays would vary from 3 (of the form {0, a, 0} ) to 100 ( of the form {0, 0, 0, . . . , 0, a_1, a_2, a_3, . . . , a_n, 0, 0, 0, . . . , 0} ).

3) I am using the stand-alone version of Excel 2016 (specifically, part of the Microsoft Office Professional Plus 2016 suite)

@Peter_SSs, Would you have any formula suggestions here?

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
This looks good for VBA; how would I translate these VBA commands in to a single Excel formula?

I have been trying for a significant amount of time about two months to try and solve this on my own, but all I've come up with are just some ideas, but no concrete solution. As a result, I am asking for help here because this is one of the first Excel formulas I haven't been able to figure out at all.

Some ideas:
As another attempt look at the following tables where I have entered the values of the array: {0;0;0;44;31;25;53;12;0;0;0;0}; in column A.

R/C​
A​
B​
C​
D​
1​
0​
{0;0;0;44;31;25;53;12;0;0;0;0}​
<= Original array as range​
2​
0​
{1;2;3;0;0;0;0;0;9;10;11;12}​
<= Positions of zeros in array​
3​
0​
{0;0;0;4;5;6;7;8;0;0;0;0}​
<= Positions of ones in array​
4​
44​
{0;0;0;0;0;0;0;12;25;31;44;53}​
<= Original Array Sorted​
5​
31​
6​
25​
{0;0;0;12;25;31;44;53;0;0;0;0}​
<= Desired Final Array
7​
53​
8​
12​
9​
0​
10​
0​
11​
0​
12​
0​
Where the formulas entered in: B1:B4 are listed below:

C1: =$A$1:$A$12
C2: =--(($A$1:$A$12)=(0) )
C3: =--(($A$1:$A$12)>(1) )
C4: =SMALL(($A$1:$A$12), (ROW($A$1:INDIRECT((("$")&("A") )&(("$")&(ROWS($A$1:$A$12) ) ) ) ) ) )

So, what Excel formula will give me the desired final array entered in C6?
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,479
Office Version
  1. 365
Platform
  1. Windows
3) I am using the stand-alone version of Excel 2016 (specifically, part of the Microsoft Office Professional Plus 2016 suite)
Then as you don't have the TEXTJOIN function I don't think what you are asking is possible with standard worksheet formulas - at least not without helper cells. Perhaps somebody will prove me wrong?

(Even with the TEXTJOIN function this may not be possible - I haven't actually attempted it.)
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Agree with peter, I haven't seen anything similar with a single formula, that's why my attempt with a UDF.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,479
Office Version
  1. 365
Platform
  1. Windows
.. unless splitting into individual cells and using helpers is any use to you ..
Here I have allowed for up to 14 items in the array but you could increase that by expanding the green and blue sections/formulas.

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1{0;0;0;44;31;25;53;12;0;0;0;0}00044312553120000  00012253144530000  
Sort Array
Cell Formulas
RangeFormula
B1:O1B1=IFERROR(REPLACE(REPLACE($A1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"{",";"),"}",";"),";","#",COLUMNS($B:B)+1)),LEN($A1),""),1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"{",";"),"}",";"),";","#",COLUMNS($B:B))),"")+0,"")
Q1:AD1Q1=IF(N(B1)=0,B1,AGGREGATE(15,6,$B1:$O1,COUNTIF($B1:$O1,0)+COUNTIF($B1:B1,">0")))
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,609
Office Version
  1. 365
Platform
  1. Windows
The samples in post 5 look as if the OP is trying to sort an actual array rather than a text string resembling an array.

This appears to work with 0's at the start and / or end of the array, but not in the middle. Array confirmed, SUM is used purely to preserve the array for evaluation.

=SUM(SMALL(A1:A12,IF(A1:A12>0,ROW(A1:A12)-MATCH(TRUE,A1:A12>0,0)+COUNTIF(A1:A12,0)+1,1)))
 

Watch MrExcel Video

Forum statistics

Threads
1,129,479
Messages
5,636,575
Members
416,925
Latest member
malamutus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top