How to order (sort) the extent List below from descending to ascending to put in a non sequence cells?

[Lacan]

Hi Guys,

How to order (sort) the extent List below from descending to ascending to add it in a non sequence cells?

Extent List:

294 Lx - Amadora (MÁgua - Sul)
295 Lx - Amadora (MÁgua - Norte)
296 Lx - Amadora (Venteira)
297 Lx - Amadora (Alfragide, ÁLivres)
298 Lx - Amadora (Falagueira, VNova)
…

Non sequence cells:

C1
C3
C5
C7
C9
....

Thank you very much.

 

[ajetrumpet]

I doubt you can use excel's features for this. and if that's true, then you have to use code. do you know anything about arrays? you can put them into an array, and then sort them accordingly. this is some code that I have on my github page that serves a purpose like that:

'Sort a 1 Dimensional Array
Function SortArray(ArrayToSort() As Variant) As Variant
     
    Dim First           As Integer
    Dim Last            As Integer
    Dim i               As Integer
    Dim j               As Integer
    Dim Temp            As String
     
    First = LBound(ArrayToSort)
    Last = UBound(ArrayToSort)
    For i = First To Last - 1
        For j = i + 1 To Last
            If ArrayToSort(i) > ArrayToSort(j) Then
                Temp = ArrayToSort(j)
                ArrayToSort(j) = ArrayToSort(i)
                ArrayToSort(i) = Temp
            End If
        Next j
    Next i
    
        For i = 1 To UBound(ArrayToSort)
            Debug.Print ArrayToSort(i)
        Next i

End Function

 

[Peter_SSs]

How to order (sort) the extent List below from descending to ascending to add it in a non sequence cells?

A little more clarification might help.

1. Exactly what do you mean by "from descending to ascending"? I ask because that list already is in ascending order. Could you show us what you would want in each of C1, C3, C5, C7 and C9 for that sample data & give any further clarification about the sorting ascending/descending?
   
   
2. Is your original data always in ascending order to start with?
   
   
3. Where is the original data located? That is, what cell is the first value in the list in?
   
   
4. If there is a choice, would you be looking for a solution that is ..
   • Manual?
   • Formulas?
   • VBA code?
   • Power query?

 

[Lacan]

Dear my friends.

First, thanks for your first appreciation.

Im sorry if my question wasnt very clear.

Please let me clarify:

1.Have this extent list of data more or less than 31 rows:

2.Want to put in ascending order like this:

Is there a way more faster than to copy it one by one?

Again, thank you ver much.

 

[Peter_SSs]

Your sample data is already in ascending order which is why I asked ..

Is your original data always in ascending order to start with?

.. what is the answer to that question?

 

[sandy666]

with Power Query aka Get&Transform

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Suffix = Table.TransformColumns(Table.Sort(Source,{{"Raw", Order.Ascending}}), {{"Raw", each _ & "#", type text}}),
    Result = Table.RemoveLastN(Table.ExpandListColumn(Table.TransformColumns(Suffix, {{"Raw", Splitter.SplitTextByDelimiter("#", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Raw"),1)
in
    Result

 

[Peter_SSs]

Peter_SSs said:

Is your original data always in ascending order to start with?

.. what is the answer to that question?

Okay, let's cover both cases.
If the data is not already sorted ascending, or you are unsure, then you can use the formula in column C.
If the data will always be already sorted ascending then you can use the slightly simpler formula in column D.

Book1
	A	B	C	D
1	294 Lx - Amadora (MÁgua - Sul)		294 Lx - Amadora (MÁgua - Sul)	294 Lx - Amadora (MÁgua - Sul)
2	295 Lx - Amadora (MÁgua - Norte)
3	296 Lx - Amadora (Venteira)		295 Lx - Amadora (MÁgua - Norte)	295 Lx - Amadora (MÁgua - Norte)
4	297 Lx - Amadora (Alfragide, ÁLivres)
5	298 Lx - Amadora (Falagueira, VNova)		296 Lx - Amadora (Venteira)	296 Lx - Amadora (Venteira)
6
7			297 Lx - Amadora (Alfragide, ÁLivres)	297 Lx - Amadora (Alfragide, ÁLivres)
8
9			298 Lx - Amadora (Falagueira, VNova)	298 Lx - Amadora (Falagueira, VNova)
10
11
12
Sort & Space

Cell Formulas
Range	Formula
C1:C12	C1	=IFERROR(IF(MOD(ROWS(C$1:C1),2),INDEX(SORT(A$1:A$5,1,1),INT((ROWS(C$1:C1)+1)/2)),""),"")
D1:D12	D1	=IFERROR(IF(MOD(ROWS(D$1:D1),2),INDEX(A$1:A$5,INT((ROWS(D$1:D1)+1)/2)),""),"")

 

[Lacan]

Okay, let's cover both cases.
If the data is not already sorted ascending, or you are unsure, then you can use the formula in column C.
If the data will always be already sorted ascending then you can use the slightly simpler formula in column D.

Book1
	A	B	C	D
1	294 Lx - Amadora (MÁgua - Sul)		294 Lx - Amadora (MÁgua - Sul)	294 Lx - Amadora (MÁgua - Sul)
2	295 Lx - Amadora (MÁgua - Norte)
3	296 Lx - Amadora (Venteira)		295 Lx - Amadora (MÁgua - Norte)	295 Lx - Amadora (MÁgua - Norte)
4	297 Lx - Amadora (Alfragide, ÁLivres)
5	298 Lx - Amadora (Falagueira, VNova)		296 Lx - Amadora (Venteira)	296 Lx - Amadora (Venteira)
6
7			297 Lx - Amadora (Alfragide, ÁLivres)	297 Lx - Amadora (Alfragide, ÁLivres)
8
9			298 Lx - Amadora (Falagueira, VNova)	298 Lx - Amadora (Falagueira, VNova)
10
11
12
Sort & Space

Cell Formulas
Range	Formula
C1:C12	C1	=IFERROR(IF(MOD(ROWS(C$1:C1),2),INDEX(SORT(A$1:A$5,1,1),INT((ROWS(C$1:C1)+1)/2)),""),"")
D1:D12	D1	=IFERROR(IF(MOD(ROWS(D$1:D1),2),INDEX(A$1:A$5,INT((ROWS(D$1:D1)+1)/2)),""),"")

Works wonderful, however can you make the formula with 10 blank rows between instead of one blank row, like that:

Again, thank you very much.

 

[sandy666]

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Suffix = Table.TransformColumns(Table.Sort(Source,{{"Raw", Order.Ascending}}), {{"Raw", each _ & "##########", type text}}),
    Result = Table.RemoveLastN(Table.ExpandListColumn(Table.TransformColumns(Suffix, {{"Raw", Splitter.SplitTextByAnyDelimiter({"#"}, QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Raw"),10)
in
    Result

 

[Peter_SSs]

can you make the formula with 10 blank rows between instead of one blank row,

Sure ..

Book1
	A	B	C
1	294 Lx - Amadora (MÁgua - Sul)		294 Lx - Amadora (MÁgua - Sul)
2	295 Lx - Amadora (MÁgua - Norte)
3	296 Lx - Amadora (Venteira)
4	297 Lx - Amadora (Alfragide, ÁLivres)
5	298 Lx - Amadora (Falagueira, VNova)
6
7
8
9
10
11
12			295 Lx - Amadora (MÁgua - Norte)
13
14
15
16
17
18
19
20
21
22
23			296 Lx - Amadora (Venteira)
24
Sort & Space

Cell Formulas
Range	Formula
C1:C24	C1	=IFERROR(IF(MOD(ROWS(C$1:C1),11)=1,INDEX(SORT(A$1:A$5,1,1),INT((ROWS(C$1:C1)+10)/11)),""),"")