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?