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.

 

[Lacan]

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)),""),"")

Works Perfect!

Thank you very much.

One last help Guys:

1. Have this massive extent data List:

2. With a formula from the reference cell S2 want to select the match names and sort it from the size from ascending to descending (like the example in the photo):

Thanks again.

 

[Peter_SSs]

Works Perfect!

Thank you very much.

You're welcome.

nd sort it from the size from ascending to descending (like the example in the photo):

Very difficult with the data you have. Excel will sort D10 before D5 because they are text entries and the first 2 characters of D10 come before the first 2 characters of D5

Would it be possible for your 'Size' data to be constructed with the same number of characters? So Instead of say
D1
D7
D10

could you have

D01
D07
D10

Then they could be sorted ascending or descending.

 

[Lacan]

The challenge is that the list have an extent of names with sizes. How to put a “0” between “D” and “number” in each one in order to do that?

Thanks again.

 

[Peter_SSs]

Could we have the sample data in a form that can be copied for testing? eg XL2BB

 

[Lacan]

Could we have the sample data in a form that can be copied for testing? eg XL2BB

Sure:

Book1
	A	B	C	D	E	F	G	H
1	Nome	Brick	Size					299 Lx - Amadora (MÁgua - Sul)
2	SOUL	299 Lx - Amadora (MÁgua - Sul)	-		JACK	D10
3	BROWN	299 Lx - Amadora (MÁgua - Sul)	D1		WHITE	D5
4	GIRASSOL	303 Lx - Amadora (Falagueira, VNova)	D7		BROWN	D1
5	BRAZ DA SILVA	303 Lx - Amadora (Falagueira, VNova)	D1		SOUL	-
6	PROD	301 Lx - Amadora (Venteira)	D1
7	SMASH	301 Lx - Amadora (Venteira)	D2
8	MARISOL	303 Lx - Amadora (Falagueira, VNova)	D10
9	BOADICEA	301 Lx - Amadora (Venteira)	D9
10	NAXOS	301 Lx - Amadora (Venteira)	D3
11	JACK	299 Lx - Amadora (MÁgua - Sul)	D10
12	WHITE	299 Lx - Amadora (MÁgua - Sul)	D5
13	MARTIM MONIZ	303 Lx - Amadora (Falagueira, VNova)	D3
Folha2

 

[Peter_SSs]

See if this does what you want.
D2 id copied down to the end of the data to standardise the sizes.
E2 is copied down as far as you might ever need.
F2 is entered in that cell only and should "splill" down to the other required cells.

Book1
	A	B	C	D	E	F	G	H
1	Nome	Brick	Size					299 Lx - Amadora (MÁgua - Sul)
2	SOUL	299 Lx - Amadora (MÁgua - Sul)	-	-	JACK	D10
3	BROWN	299 Lx - Amadora (MÁgua - Sul)	D1	D01	WHITE	D05
4	GIRASSOL	303 Lx - Amadora (Falagueira, VNova)	D7	D07	BROWN	D01
5	BRAZ DA SILVA	303 Lx - Amadora (Falagueira, VNova)	D1	D01	SOUL	-
6	PROD	301 Lx - Amadora (Venteira)	D1	D01
7	SMASH	301 Lx - Amadora (Venteira)	D2	D02
8	MARISOL	303 Lx - Amadora (Falagueira, VNova)	D10	D10
9	BOADICEA	301 Lx - Amadora (Venteira)	D9	D09
10	NAXOS	301 Lx - Amadora (Venteira)	D3	D03
11	JACK	299 Lx - Amadora (MÁgua - Sul)	D10	D10
12	WHITE	299 Lx - Amadora (MÁgua - Sul)	D5	D05
13	MARTIM MONIZ	303 Lx - Amadora (Falagueira, VNova)	D3	D03
14
Sheet2 (2)

Cell Formulas
Range	Formula
F2	F2	=SORT(FILTER(D$2:D$13,B$2:B$13=H$1),,-1)
E2:E7	E2	=IF(F2="","",INDEX(A:A,AGGREGATE(14,6,ROW(A$2:A$13)/((B$2:B$13=H$1)*(D$2:D$13=F2)),COUNTIF(F$2:F2,F2))))
D2:D13	D2	=LEFT(C2,1)&TEXT(MID(C2,2,3),"00")

 

[Lacan]

See if this does what you want.
D2 id copied down to the end of the data to standardise the sizes.
E2 is copied down as far as you might ever need.
F2 is entered in that cell only and should "splill" down to the other required cells.

Book1
	A	B	C	D	E	F	G	H
1	Nome	Brick	Size					299 Lx - Amadora (MÁgua - Sul)
2	SOUL	299 Lx - Amadora (MÁgua - Sul)	-	-	JACK	D10
3	BROWN	299 Lx - Amadora (MÁgua - Sul)	D1	D01	WHITE	D05
4	GIRASSOL	303 Lx - Amadora (Falagueira, VNova)	D7	D07	BROWN	D01
5	BRAZ DA SILVA	303 Lx - Amadora (Falagueira, VNova)	D1	D01	SOUL	-
6	PROD	301 Lx - Amadora (Venteira)	D1	D01
7	SMASH	301 Lx - Amadora (Venteira)	D2	D02
8	MARISOL	303 Lx - Amadora (Falagueira, VNova)	D10	D10
9	BOADICEA	301 Lx - Amadora (Venteira)	D9	D09
10	NAXOS	301 Lx - Amadora (Venteira)	D3	D03
11	JACK	299 Lx - Amadora (MÁgua - Sul)	D10	D10
12	WHITE	299 Lx - Amadora (MÁgua - Sul)	D5	D05
13	MARTIM MONIZ	303 Lx - Amadora (Falagueira, VNova)	D3	D03
14
Sheet2 (2)

Cell Formulas
Range	Formula
F2	F2	=SORT(FILTER(D$2:D$13,B$2:B$13=H$1),,-1)
E2:E7	E2	=IF(F2="","",INDEX(A:A,AGGREGATE(14,6,ROW(A$2:A$13)/((B$2:B$13=H$1)*(D$2:D$13=F2)),COUNTIF(F$2:F2,F2))))
D2:D13	D2	=LEFT(C2,1)&TEXT(MID(C2,2,3),"00")

Works Amazing my Friend.

Thank you very much.

 

[Lacan]

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)),""),"")

There is one especific cell there has to have 12 blank rows between - how is the formula for this one only?

Thanks again.

 

[Peter_SSs]

Works Amazing my Friend.

Thank you very much.

You're welcome.

There is one especific cell there has to have 12 blank rows between

I don't know what you mean by this.

 

[Lacan]

You're welcome.

I don't know what you mean by this.

Have this data:

Book1
	J	K	L
2	299 Lx - Amadora (MÁgua - Sul)		299 Lx - Amadora (MÁgua - Sul)
3	300 Lx - Amadora (MÁgua - Norte)
4	301 Lx - Amadora (Venteira)
5	302 Lx - Amadora (Alfragide, ÁLivres)
6	303 Lx - Amadora (Falagueira, VNova)
7
8
9
10
11
12
13			300 Lx - Amadora (MÁgua - Norte)
14
15
16
17
18
19
20
21
22
23
24			301 Lx - Amadora (Venteira)
Folha1

Cell Formulas
Range	Formula
L2:L24	L2	=IFERROR(IF(MOD(ROWS(L$2:L2),11)=1,INDEX(SORT($J$2:$J$6,1,1),INT((ROWS(L$2:L2)+10)/11)),""),"")

I´m using this formula in cell L2: =SE.ERRO(SE(RESTO(LINS(L$2:L2);11)=1;ÍNDICE(ORDENAR($J$2:$J$6;1;1);INT((LINS(L$2:L2)+10)/11));"");"")

However with formula want to put this data "301 Lx - Amadora (Venteira)" in this specific cell L28 instead of cell L24.

How can I do that?

Thank you very much.