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

L

Lacan

Board Regular
Joined
Oct 5, 2016
Messages
163
Office Version
  1. 365
Platform
  1. Windows
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.
 
Sort by date Sort by votes
Peter_SSs said:
Sure ..

Book1
ABC
1294 Lx - Amadora (MÁgua - Sul)294 Lx - Amadora (MÁgua - Sul)
2295 Lx - Amadora (MÁgua - Norte) 
3296 Lx - Amadora (Venteira) 
4297 Lx - Amadora (Alfragide, ÁLivres) 
5298 Lx - Amadora (Falagueira, VNova) 
6 
7 
8 
9 
10 
11 
12295 Lx - Amadora (MÁgua - Norte)
13 
14 
15 
16 
17 
18 
19 
20 
21 
22 
23296 Lx - Amadora (Venteira)
24 
Sort & Space
Cell Formulas
RangeFormula
C1:C24C1=IFERROR(IF(MOD(ROWS(C$1:C1),11)=1,INDEX(SORT(A$1:A$5,1,1),INT((ROWS(C$1:C1)+10)/11)),""),"")
Click to expand...

Works Perfect!

Thank you very much.

One last help Guys:

1. Have this massive extent data List:

1579952502311.png



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):

1579952604088.png



Thanks again.
 
Upvote 0

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Lacan said:
Works Perfect!

Thank you very much.
Click to expand...
You're welcome.

Lacan said:
nd sort it from the size from ascending to descending (like the example in the photo):
Click to expand...
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.
 
Upvote 0
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.
 
Upvote 0
Could we have the sample data in a form that can be copied for testing? eg XL2BB
 
Upvote 0
Peter_SSs said:
Could we have the sample data in a form that can be copied for testing? eg XL2BB
Click to expand...

Sure:

Book1
ABCDEFGH
1NomeBrickSize299 Lx - Amadora (MÁgua - Sul)
2SOUL299 Lx - Amadora (MÁgua - Sul)-JACKD10
3BROWN299 Lx - Amadora (MÁgua - Sul)D1WHITED5
4GIRASSOL303 Lx - Amadora (Falagueira, VNova)D7BROWND1
5BRAZ DA SILVA303 Lx - Amadora (Falagueira, VNova)D1SOUL-
6PROD301 Lx - Amadora (Venteira)D1
7SMASH301 Lx - Amadora (Venteira)D2
8MARISOL303 Lx - Amadora (Falagueira, VNova)D10
9BOADICEA301 Lx - Amadora (Venteira)D9
10NAXOS301 Lx - Amadora (Venteira)D3
11JACK299 Lx - Amadora (MÁgua - Sul)D10
12WHITE299 Lx - Amadora (MÁgua - Sul)D5
13MARTIM MONIZ303 Lx - Amadora (Falagueira, VNova)D3
Folha2
 
Upvote 0
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
ABCDEFGH
1NomeBrickSize299 Lx - Amadora (MÁgua - Sul)
2SOUL299 Lx - Amadora (MÁgua - Sul)--JACKD10
3BROWN299 Lx - Amadora (MÁgua - Sul)D1D01WHITED05
4GIRASSOL303 Lx - Amadora (Falagueira, VNova)D7D07BROWND01
5BRAZ DA SILVA303 Lx - Amadora (Falagueira, VNova)D1D01SOUL-
6PROD301 Lx - Amadora (Venteira)D1D01 
7SMASH301 Lx - Amadora (Venteira)D2D02 
8MARISOL303 Lx - Amadora (Falagueira, VNova)D10D10
9BOADICEA301 Lx - Amadora (Venteira)D9D09
10NAXOS301 Lx - Amadora (Venteira)D3D03
11JACK299 Lx - Amadora (MÁgua - Sul)D10D10
12WHITE299 Lx - Amadora (MÁgua - Sul)D5D05
13MARTIM MONIZ303 Lx - Amadora (Falagueira, VNova)D3D03
14
Sheet2 (2)
Cell Formulas
RangeFormula
F2F2=SORT(FILTER(D$2:D$13,B$2:B$13=H$1),,-1)
E2:E7E2=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:D13D2=LEFT(C2,1)&TEXT(MID(C2,2,3),"00")
 
Upvote 0
Peter_SSs said:
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
ABCDEFGH
1NomeBrickSize299 Lx - Amadora (MÁgua - Sul)
2SOUL299 Lx - Amadora (MÁgua - Sul)--JACKD10
3BROWN299 Lx - Amadora (MÁgua - Sul)D1D01WHITED05
4GIRASSOL303 Lx - Amadora (Falagueira, VNova)D7D07BROWND01
5BRAZ DA SILVA303 Lx - Amadora (Falagueira, VNova)D1D01SOUL-
6PROD301 Lx - Amadora (Venteira)D1D01 
7SMASH301 Lx - Amadora (Venteira)D2D02 
8MARISOL303 Lx - Amadora (Falagueira, VNova)D10D10
9BOADICEA301 Lx - Amadora (Venteira)D9D09
10NAXOS301 Lx - Amadora (Venteira)D3D03
11JACK299 Lx - Amadora (MÁgua - Sul)D10D10
12WHITE299 Lx - Amadora (MÁgua - Sul)D5D05
13MARTIM MONIZ303 Lx - Amadora (Falagueira, VNova)D3D03
14
Sheet2 (2)
Cell Formulas
RangeFormula
F2F2=SORT(FILTER(D$2:D$13,B$2:B$13=H$1),,-1)
E2:E7E2=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:D13D2=LEFT(C2,1)&TEXT(MID(C2,2,3),"00")
Click to expand...

Works Amazing my Friend.

Thank you very much.
 
Upvote 0
Peter_SSs said:
Sure ..

Book1
ABC
1294 Lx - Amadora (MÁgua - Sul)294 Lx - Amadora (MÁgua - Sul)
2295 Lx - Amadora (MÁgua - Norte) 
3296 Lx - Amadora (Venteira) 
4297 Lx - Amadora (Alfragide, ÁLivres) 
5298 Lx - Amadora (Falagueira, VNova) 
6 
7 
8 
9 
10 
11 
12295 Lx - Amadora (MÁgua - Norte)
13 
14 
15 
16 
17 
18 
19 
20 
21 
22 
23296 Lx - Amadora (Venteira)
24 
Sort & Space
Cell Formulas
RangeFormula
C1:C24C1=IFERROR(IF(MOD(ROWS(C$1:C1),11)=1,INDEX(SORT(A$1:A$5,1,1),INT((ROWS(C$1:C1)+10)/11)),""),"")
Click to expand...

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

Thanks again.
 
Upvote 0
Peter_SSs said:
You're welcome.

I don't know what you mean by this.
Click to expand...

Have this data:

Book1
JKL
2299 Lx - Amadora (MÁgua - Sul)299 Lx - Amadora (MÁgua - Sul)
3300 Lx - Amadora (MÁgua - Norte) 
4301 Lx - Amadora (Venteira) 
5302 Lx - Amadora (Alfragide, ÁLivres) 
6303 Lx - Amadora (Falagueira, VNova) 
7 
8 
9 
10 
11 
12 
13300 Lx - Amadora (MÁgua - Norte)
14 
15 
16 
17 
18 
19 
20 
21 
22 
23 
24301 Lx - Amadora (Venteira)
Folha1
Cell Formulas
RangeFormula
L2:L24L2=IFERROR(IF(MOD(ROWS(L$2:L2),11)=1,INDEX(SORT($J$2:$J$6,1,1),INT((ROWS(L$2:L2)+10)/11)),""),"")



1580077604686.png


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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Very simple and easy question: - How to use Indirect Function + Xlookup with 2 Criteria?
Replies
2
Views
312
Lacan
L
L
  • Question
How to make a unique List with Dynamic Formula and sort it from descending to ascending?
Replies
8
Views
396
J.Ty.
J.Ty.
L
  • Solved
How to lookup for one criteria and return data that aren´t in the same main table (columns and rows) below?
Replies
4
Views
347
Lacan
L
D
  • Question
How can I sort worksheets in ascending/descending order based on the value of the same cell in each sheet?
Replies
5
Views
2K
Comisar
C

Forum statistics

Threads
1,215,352
Messages
6,124,451
Members
449,161
Latest member
NHOJ

Share this page

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
Back
Top