sort index sequence function showing zero values first

valmir

Board Regular
Joined
Feb 10, 2021
Messages
235
Office Version
  1. 365
Platform
  1. Windows
Hello everyone
I need help fixing this formula:
=SORT(INDEX(CG1#;SEQUENCE(ROWS(CG1#));{1\3\2});2;1)
As shown below, it is showing the zero values first on the alphabetically sorted column (middle column).
The sorting is supposed to start on row 24.
Thanks
Stats 2016 - Copy.xlsx
CSCTCU
1ASA0Ady ?
2BEN0Anderson
3PRO0Belito ?
44AB0Borra
54AB0Cadiata
64AB0Chara ?
7PLS0Chico Bel
8SAG0Didí
9PLS0Dji
104AB0Edi
11MAI0Fernandinho
12MAI0Isaías ?
134AB0Ivanilson
144AB0Jesse
15ASA0Kelly
164AB0Lara ?
17MAI0Mano ?
18PLS0Mayala
19POR0Miguel ?
20ACA0Nzuzi
214AB0Rui ?
22ASA0Tucho ?
23LIB0Yuri ?
24PLSAbel LukangoAbel
25DESAbelardo Gomes SocolaBelito Socola
26DESAbengea Mambani AliAli
27DESAdão Francisco Congo ZalataDadão Bile
28PRIAdão Joaquim Bango CabaçaTony Cabaça
29CAAAdelino Wima Calunhi AntónioManinho
30MAIAdérito Yandelela ChissocaLelas
31SAGAdérito Yandelela ChissocaLelas
32INTAdilson Cipriano da CruzNeblú
33LIBAdilson Ernesto KivãoAdilson Kivão
34CAAAdilson Joaquim ManuelAdilson Manuel
35PORAdolfo da Silva CatembaDenilson
36SAGAdolfo da Silva CatembaDenilson
37PROAdriano Belmiro Duarte NicolauYano
38DESAdriano da Costa Mateus AlbertoKumaca
39ASAAfonso Sebastião CabungulaFofó
STATS2
 
My solution works on the spilled range that your formula works on.
I created some scrap data in G1 to represent your data in CG1 & then the formula in L1 is to replace the formula you posted in your op.
In my particular case the column to follow up is CG and on account of language settings I changed the formula to:
=SORT(SORTBY(FILTER(CG1#;INDEX(CG1#;;3)<>0);{1;3;2});2;1)
I am getting a value error. Can you please tell me why?
 
Upvote 0

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
It may need to be
Excel Formula:
=SORT(SORTBY(FILTER(CG1#;INDEX(CG1#;;3)<>0);{1\3\2});2;1)
 
Upvote 0
Solution
Glad we could help & thanks for the feedback.
 
Upvote 0
Glad we could help & thanks for the feedback.
Before I came to you guys for help I actually paid in another website and they couldn't solve my problem. I hope I could some how donate at least for the website.
 
Upvote 0
Thanks for the offer, however there is no way to donate to the site.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

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