Swap column order in sort filter formula

valmir

Board Regular
Joined
Feb 10, 2021
Messages
79
Office Version
  1. 365
Platform
  1. Windows
Dear Sirs
I have sorted the data in columns from CK (source BY), CL (source BZ) and CM (source CA) with the following criteria:
names in column CM sorted alphabetically with the corresponding matches in CK and CL. The current order of values is CK, CL, CM. I want to change it to CK values to come first, followed by CM and finally CL.
Thanks
Stats 2016.xlsx
BYBZCACBCCCDCECFCGCHCICJCKCLCM
14ABBorra  33 PETAbdulAntónio NzayinawoPLSAbelAbel Lukango
24ABCadiata  21  MAIAbegáManuel Pedro PacaviraDESBelito SocolaAbelardo Gomes Socola
34ABCamposAntónio Campos Calei124 73PLSAbelAbel LukangoDESAliAbengea Mambani Ali
44ABChara ?  21 INTAbianda 1Yann Junior Abianda 1DESDadão BileAdão Francisco Congo Zalata
54ABDany RibeiroDaniel Mendes Ribeiro62618KABAbianda 2Yann Junior Abianda 2PRITony CabaçaAdão Joaquim Bango Cabaça
64ABDário AmorimDário Félix Amorim  3 LIBAdilson KivãoAdilson Ernesto KivãoCAAManinhoAdelino Wima Calunhi António
74ABDudú JackDomingos Monteiro Jack18474CAAAdilson ManuelAdilson Joaquim ManuelSAGLelas 1Adérito Yandelela Chissoca 1
84ABEdgardEdgard Arnaud Afane 18  ACAAdó PenaAntónio Joaquim Barros PenaMAILelas 2Adérito Yandelela Chissoca 2
94ABEdi  1  ASAAdy ?INTNeblúAdilson Cipriano da Cruz
104ABGeúdaGelson L. da S. Teles 87 BENAdy PauloAgostinho Domingos PauloLIBAdilson KivãoAdilson Ernesto Kivão
114ABGuiManuel Porfírio Pompílio de Matos 6  PLSAgoyaAugusto MakanjaCAAAdilson ManuelAdilson Joaquim Manuel
124ABHippiDomingos Paulo João José115175ACAAiresLudgero Aires Cachicote da RochaSAGDenilson 1Adolfo da Silva Catemba 1
134ABIvanilson   5 MAIAlexAlexandre G. Mário MiguelPORDenilson 2Adolfo da Silva Catemba 2
144ABJairzinhoJair Sebastião de Castro 85 PORAlexandreAlexandre Domingos Cristóvão M'FutilaPROYanoAdriano Belmiro Duarte Nicolau
154ABJesse   3 DESAliAbengea Mambani AliDESKumacaAdriano da Costa Mateus Alberto
164ABKandúDéo Kanda314927PROAlmeidaJosé Xavier FernandoASAFofóAfonso Sebastião Cabungula
174ABKizombéAlberto dos Santos Domingos 161 KABAmarildo 1Amarildo Eugénio Augusto Miranda 1INTMabululuAgostinho Cristóvão Paciência
184ABLara ?   1 ASAAmarildo 2Amarildo Eugénio Augusto Miranda 2BENAdy PauloAgostinho Domingos Paulo
194ABLeloJosé Lelo Sevo Barros114 76BENAmaroAmândio Manuel Filipe da CostaKABEmilsonAires Emilson Gonzaga Zeca
204ABManucho MuquengueMariano Simão Muquengue2151146BENAmido BaldéAmido BaldéMAIPai da BolaAlbano Camuele Sumano
214ABMatambaMatamba Paulino Sousa127 77PROAnastácio 1Anastácio Manuel da Costa 1PRIMilamboAlbert Milambo-Mutamba
224ABPaitoManuel Gaspar Fernandes 15 ASAAnastácio 2Anastácio Manuel da Costa 2PRIFissyAlberto Álvaro Paca
234ABPaluchoPaulo Pereira da Silva161378BENAnderson4ABKizombéAlberto dos Santos Domingos
244ABPilolaJosé Olívio Andrade Pereira311 28INTAntónioTomás Chilombo Cambuque SapaloSAGRolliAlentua Tangala Rolli
254ABPrazeresPrazeres Chijica M. Dala 31 SAGAry CalveteHelmut Ariadne Figueiredo CalvetePORAlexandreAlexandre Domingos Cristóvão M'Futila
264ABRochanaPanda Marcelo José 23  PETAry OliveiraAriclene Assunção OliveiraMAIAlexAlexandre G. Mário Miguel
274ABRui ?  82 PRIAry PapelManuel David AfonsoDESChiweAlfredo Cassinda Calunganga
284ABTony AugustoAntónio Pedro Augusto 7  PLSAtunakoEboue Manzambi AtunakoLIBFredyAlfredo Kulembe Ribeiro
294ABVanderVander Ramos Pinto 19  PROAvex 1Avelino Eduardo António Craque 1ASAMiroAlmiro Edson Daniel Lobo
304ABXavierXavier Eduardo Vicente Vunge123 79DESAvex 2Avelino Eduardo António Craque 2BENAmaroAmândio Manuel Filipe da Costa
314AB      SAGAyalaMário Álvaro AgostinhoKABAmarildo 1Amarildo Eugénio Augusto Miranda 1
324AB      PETAzulãoTiago Lima LealASAAmarildo 2Amarildo Eugénio Augusto Miranda 2
334AB      ASABabacarBabacar FallBENAmido BaldéAmido Baldé
344AB      BENBabyValdemar Denso AntónioPROAnastácio 1Anastácio Manuel da Costa 1
354AB      PLSBadrickBadrick Paulo MadadiASAAnastácio 2Anastácio Manuel da Costa 2
364AB      PETBalacaiEvaristo Maurício PascoalASAGuelorAnderson Benjamim Monteiro
374AB      PLSBarrezóJoveth AdãoBENToyAndré Augusto Miranda Kongo
384AB      INTBartoloBartolomeu Nguia BaptistaINTMfedeAnthony Kevin Mfede Junior
39ACAAdó PenaAntónio Joaquim Barros Pena113280INTBebé 1Odimir Abreu Gabriel Breganha 14ABCamposAntónio Campos Calei
40ACAAiresLudgero Aires Cachicote da Rocha 1  ASABebé 2Odimir Abreu Gabriel Breganha 2PROSilva AnatoAntónio da Silva Anato
STATS2
Cell Formulas
RangeFormula
BZ1BZ1=INDIRECT("4AB!B5")
CA1CA1=INDIRECT("4AB!C5")
CB1CB1=INDIRECT("4AB!E5")
CC1CC1=INDIRECT("4AB!F5")
CD1CD1=INDIRECT("4AB!G5")
CE1:CE40CE1=IF($CB1=0,"",IF($CB1>0,RANK(CB1,$CB$1:$CB$608)+COUNTIF($CB$1:CB1,CB1)-1))
BZ2BZ2=INDIRECT("4AB!B6")
CA2CA2=INDIRECT("4AB!C6")
CB2CB2=INDIRECT("4AB!E6")
CC2CC2=INDIRECT("4AB!F6")
CD2CD2=INDIRECT("4AB!G6")
BZ3BZ3=INDIRECT("4AB!B7")
CA3CA3=INDIRECT("4AB!C7")
CB3CB3=INDIRECT("4AB!E7")
CC3CC3=INDIRECT("4AB!F7")
CD3CD3=INDIRECT("4AB!G7")
BZ4BZ4=INDIRECT("4AB!B8")
CA4CA4=INDIRECT("4AB!C8")
CB4CB4=INDIRECT("4AB!E8")
CC4CC4=INDIRECT("4AB!F8")
CD4CD4=INDIRECT("4AB!G8")
BZ5BZ5=INDIRECT("4AB!B9")
CA5CA5=INDIRECT("4AB!C9")
CB5CB5=INDIRECT("4AB!E9")
CC5CC5=INDIRECT("4AB!F9")
CD5CD5=INDIRECT("4AB!G9")
BZ6BZ6=INDIRECT("4AB!B10")
CA6CA6=INDIRECT("4AB!C10")
CB6CB6=INDIRECT("4AB!E10")
CC6CC6=INDIRECT("4AB!F10")
CD6CD6=INDIRECT("4AB!G10")
BZ7BZ7=INDIRECT("4AB!B11")
CA7CA7=INDIRECT("4AB!C11")
CB7CB7=INDIRECT("4AB!E11")
CC7CC7=INDIRECT("4AB!F11")
CD7CD7=INDIRECT("4AB!G11")
BZ8BZ8=INDIRECT("4AB!B12")
CA8CA8=INDIRECT("4AB!C12")
CB8CB8=INDIRECT("4AB!E12")
CC8CC8=INDIRECT("4AB!F12")
CD8CD8=INDIRECT("4AB!G12")
BZ9BZ9=INDIRECT("4AB!B13")
CA9CA9=INDIRECT("4AB!C13")
CB9CB9=INDIRECT("4AB!E13")
CC9CC9=INDIRECT("4AB!F13")
CD9CD9=INDIRECT("4AB!G13")
BZ10BZ10=INDIRECT("4AB!B14")
CA10CA10=INDIRECT("4AB!C14")
CB10CB10=INDIRECT("4AB!E14")
CC10CC10=INDIRECT("4AB!F14")
CD10CD10=INDIRECT("4AB!G14")
BZ11BZ11=INDIRECT("4AB!B15")
CA11CA11=INDIRECT("4AB!C15")
CB11CB11=INDIRECT("4AB!E15")
CC11CC11=INDIRECT("4AB!F15")
CD11CD11=INDIRECT("4AB!G15")
BZ12BZ12=INDIRECT("4AB!B16")
CA12CA12=INDIRECT("4AB!C16")
CB12CB12=INDIRECT("4AB!E16")
CC12CC12=INDIRECT("4AB!F16")
CD12CD12=INDIRECT("4AB!G16")
BZ13BZ13=INDIRECT("4AB!B17")
CA13CA13=INDIRECT("4AB!C17")
CB13CB13=INDIRECT("4AB!E17")
CC13CC13=INDIRECT("4AB!F17")
CD13CD13=INDIRECT("4AB!G17")
BZ14BZ14=INDIRECT("4AB!B18")
CA14CA14=INDIRECT("4AB!C18")
CB14CB14=INDIRECT("4AB!E18")
CC14CC14=INDIRECT("4AB!F18")
CD14CD14=INDIRECT("4AB!G18")
BZ15BZ15=INDIRECT("4AB!B19")
CA15CA15=INDIRECT("4AB!C19")
CB15CB15=INDIRECT("4AB!E19")
CC15CC15=INDIRECT("4AB!F19")
CD15CD15=INDIRECT("4AB!G19")
BZ16BZ16=INDIRECT("4AB!B20")
CA16CA16=INDIRECT("4AB!C20")
CB16CB16=INDIRECT("4AB!E20")
CC16CC16=INDIRECT("4AB!F20")
CD16CD16=INDIRECT("4AB!G20")
BZ17BZ17=INDIRECT("4AB!B21")
CA17CA17=INDIRECT("4AB!C21")
CB17CB17=INDIRECT("4AB!E21")
CC17CC17=INDIRECT("4AB!F21")
CD17CD17=INDIRECT("4AB!G21")
BZ18BZ18=INDIRECT("4AB!B22")
CA18CA18=INDIRECT("4AB!C22")
CB18CB18=INDIRECT("4AB!E22")
CC18CC18=INDIRECT("4AB!F22")
CD18CD18=INDIRECT("4AB!G22")
BZ19BZ19=INDIRECT("4AB!B23")
CA19CA19=INDIRECT("4AB!C23")
CB19CB19=INDIRECT("4AB!E23")
CC19CC19=INDIRECT("4AB!F23")
CD19CD19=INDIRECT("4AB!G23")
BZ20BZ20=INDIRECT("4AB!B24")
CA20CA20=INDIRECT("4AB!C24")
CB20CB20=INDIRECT("4AB!E24")
CC20CC20=INDIRECT("4AB!F24")
CD20CD20=INDIRECT("4AB!G24")
BZ21BZ21=INDIRECT("4AB!B25")
CA21CA21=INDIRECT("4AB!C25")
CB21CB21=INDIRECT("4AB!E25")
CC21CC21=INDIRECT("4AB!F25")
CD21CD21=INDIRECT("4AB!G25")
BZ22BZ22=INDIRECT("4AB!B26")
CA22CA22=INDIRECT("4AB!C26")
CB22CB22=INDIRECT("4AB!E26")
CC22CC22=INDIRECT("4AB!F26")
CD22CD22=INDIRECT("4AB!G26")
BZ23BZ23=INDIRECT("4AB!B27")
CA23CA23=INDIRECT("4AB!C27")
CB23CB23=INDIRECT("4AB!E27")
CC23CC23=INDIRECT("4AB!F27")
CD23CD23=INDIRECT("4AB!G27")
BZ24BZ24=INDIRECT("4AB!B28")
CA24CA24=INDIRECT("4AB!C28")
CB24CB24=INDIRECT("4AB!E28")
CC24CC24=INDIRECT("4AB!F28")
CD24CD24=INDIRECT("4AB!G28")
BZ25BZ25=INDIRECT("4AB!B29")
CA25CA25=INDIRECT("4AB!C29")
CB25CB25=INDIRECT("4AB!E29")
CC25CC25=INDIRECT("4AB!F29")
CD25CD25=INDIRECT("4AB!G29")
BZ26BZ26=INDIRECT("4AB!B30")
CA26CA26=INDIRECT("4AB!C30")
CB26CB26=INDIRECT("4AB!E30")
CC26CC26=INDIRECT("4AB!F30")
CD26CD26=INDIRECT("4AB!G30")
BZ27BZ27=INDIRECT("4AB!B31")
CA27CA27=INDIRECT("4AB!C31")
CB27CB27=INDIRECT("4AB!E31")
CC27CC27=INDIRECT("4AB!F31")
CD27CD27=INDIRECT("4AB!G31")
BZ28BZ28=INDIRECT("4AB!B32")
CA28CA28=INDIRECT("4AB!C32")
CB28CB28=INDIRECT("4AB!E32")
CC28CC28=INDIRECT("4AB!F32")
CD28CD28=INDIRECT("4AB!G32")
BZ29BZ29=INDIRECT("4AB!B33")
CA29CA29=INDIRECT("4AB!C33")
CB29CB29=INDIRECT("4AB!E33")
CC29CC29=INDIRECT("4AB!F33")
CD29CD29=INDIRECT("4AB!G33")
BZ30BZ30=INDIRECT("4AB!B34")
CA30CA30=INDIRECT("4AB!C34")
CB30CB30=INDIRECT("4AB!E34")
CC30CC30=INDIRECT("4AB!F34")
CD30CD30=INDIRECT("4AB!G34")
BZ31BZ31=INDIRECT("4AB!B35")
CA31CA31=INDIRECT("4AB!C35")
CB31CB31=INDIRECT("4AB!E35")
CC31CC31=INDIRECT("4AB!F35")
CD31CD31=INDIRECT("4AB!G35")
BZ32BZ32=INDIRECT("4AB!B36")
CA32CA32=INDIRECT("4AB!C36")
CB32CB32=INDIRECT("4AB!E36")
CC32CC32=INDIRECT("4AB!F36")
CD32CD32=INDIRECT("4AB!G36")
BZ33BZ33=INDIRECT("4AB!B37")
CA33CA33=INDIRECT("4AB!C37")
CB33CB33=INDIRECT("4AB!E37")
CC33CC33=INDIRECT("4AB!F37")
CD33CD33=INDIRECT("4AB!G37")
BZ34BZ34=INDIRECT("4AB!B38")
CA34CA34=INDIRECT("4AB!C38")
CB34CB34=INDIRECT("4AB!E38")
CC34CC34=INDIRECT("4AB!F38")
CD34CD34=INDIRECT("4AB!G38")
BZ35BZ35=INDIRECT("4AB!B39")
CA35CA35=INDIRECT("4AB!C39")
CB35CB35=INDIRECT("4AB!E39")
CC35CC35=INDIRECT("4AB!F39")
CD35CD35=INDIRECT("4AB!G39")
BZ36BZ36=INDIRECT("4AB!B40")
CA36CA36=INDIRECT("4AB!C40")
CB36CB36=INDIRECT("4AB!E40")
CC36CC36=INDIRECT("4AB!F40")
CD36CD36=INDIRECT("4AB!G40")
BZ37BZ37=INDIRECT("4AB!B41")
CA37CA37=INDIRECT("4AB!C41")
CB37CB37=INDIRECT("4AB!E41")
CC37CC37=INDIRECT("4AB!F41")
CD37CD37=INDIRECT("4AB!G41")
BZ38BZ38=INDIRECT("4AB!B42")
CA38CA38=INDIRECT("4AB!C42")
CB38CB38=INDIRECT("4AB!E42")
CC38CC38=INDIRECT("4AB!F42")
CD38CD38=INDIRECT("4AB!G42")
BZ39BZ39=INDIRECT("ACA!B5")
CA39CA39=INDIRECT("ACA!C5")
CB39CB39=INDIRECT("ACA!E5")
CC39CC39=INDIRECT("ACA!F5")
CD39CD39=INDIRECT("ACA!G5")
BZ40BZ40=INDIRECT("ACA!B6")
CA40CA40=INDIRECT("ACA!C6")
CB40CB40=INDIRECT("ACA!E6")
CC40CC40=INDIRECT("ACA!F6")
CD40CD40=INDIRECT("ACA!G6")
CG1:CI501CG1=SORT(FILTER(BY1:CA608,BZ1:BZ608<>0),2,1)
CK1:CM478CK1=SORT(FILTER(BY1:CA608,CA1:CA608<>0),3,1)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
BY1Expression=BZ1=0textNO
BZ2:BZ608Expression=CA2=0textNO
BZ1Expression=CA1=0textNO
CA1:CA608Cell Value=0textNO
CI1:CI608Cell Value=0textNO
CH1:CH608Expression=CI1=0textNO
CB1:CD608Cell Value=0textNO
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,580
Office Version
  1. 365
Platform
  1. Windows
Is there really no headings on the data?

Do you have the LET() function?
 

valmir

Board Regular
Joined
Feb 10, 2021
Messages
79
Office Version
  1. 365
Platform
  1. Windows
Is there really no headings on the data?

Do you have the LET() function?
There are no headings on the data and I am sorry but I don't know about the LET() function. I am using excel 365
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,580
Office Version
  1. 365
Platform
  1. Windows
I don't know about the LET() function. I am using excel 365
At this stage some people with 365 have the LET function and some don't. To check, you can click the 'Insert function' icon (fx) beside the formula bar & look in the 'Text' category to see if it is your list.

1616977376466.png


In any case it turns out that you don't actually need it for this so you could try this.

valmir.xlsm
BYBZCACJCKCLCM
14ABBorra4ABAlberto dos Santos DomingosKizombé
24ABCadiata4ABAntónio Campos CaleiCampos
34ABCamposAntónio Campos CaleiACAAntónio Joaquim Barros PenaAdó Pena
44ABChara ?4ABAntónio Pedro AugustoTony Augusto
54ABDany RibeiroDaniel Mendes Ribeiro4ABDaniel Mendes RibeiroDany Ribeiro
64ABDário AmorimDário Félix Amorim4ABDário Félix AmorimDário Amorim
74ABDudú JackDomingos Monteiro Jack4ABDéo KandaKandú
84ABEdgardEdgard Arnaud Afane4ABDomingos Monteiro JackDudú Jack
94ABEdi4ABDomingos Paulo João JoséHippi
104ABGeúdaGelson L. da S. Teles4ABEdgard Arnaud AfaneEdgard
114ABGuiManuel Porfírio Pompílio de Matos4ABGelson L. da S. TelesGeúda
124ABHippiDomingos Paulo João José4ABJair Sebastião de CastroJairzinho
134ABIvanilson4ABJosé Lelo Sevo BarrosLelo
144ABJairzinhoJair Sebastião de Castro4ABJosé Olívio Andrade PereiraPilola
154ABJesseACALudgero Aires Cachicote da RochaAires
164ABKandúDéo Kanda4ABManuel Gaspar FernandesPaito
174ABKizombéAlberto dos Santos Domingos4ABManuel Porfírio Pompílio de MatosGui
184ABLara ?4ABMariano Simão MuquengueManucho Muquengue
194ABLeloJosé Lelo Sevo Barros4ABMatamba Paulino SousaMatamba
204ABManucho MuquengueMariano Simão Muquengue4ABPanda Marcelo JoséRochana
214ABMatambaMatamba Paulino Sousa4ABPaulo Pereira da SilvaPalucho
224ABPaitoManuel Gaspar Fernandes4ABPrazeres Chijica M. DalaPrazeres
234ABPaluchoPaulo Pereira da Silva4ABVander Ramos PintoVander
244ABPilolaJosé Olívio Andrade Pereira4ABXavier Eduardo Vicente VungeXavier
254ABPrazeresPrazeres Chijica M. Dala
264ABRochanaPanda Marcelo José
274ABRui ?
284ABTony AugustoAntónio Pedro Augusto
294ABVanderVander Ramos Pinto
304ABXavierXavier Eduardo Vicente Vunge
314AB
324AB
334AB
344AB
354AB
364AB
374AB
384AB
39ACAAdó PenaAntónio Joaquim Barros Pena
40ACAAiresLudgero Aires Cachicote da Rocha
Sheet1
Cell Formulas
RangeFormula
CK1:CM24CK1=SORTBY(SORT(FILTER(BY1:CA608,CA1:CA608<>0),3,1),{1,3,2})
Dynamic array formulas.
 

valmir

Board Regular
Joined
Feb 10, 2021
Messages
79
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

=SORTBY(SORT(FILTER(BY1:CA608,CA1:CA608<>0),3,1),{1,3,2})
I don't know why I am getting a value error: "a value used in the formula is of the wrong data type". I had to switch colons to semi-colons
 

valmir

Board Regular
Joined
Feb 10, 2021
Messages
79
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Presumably you meant that you had to switch commas to semicolons?
That is related to regional/language settings. See more here: Excel List separator | Exceljet.

Once you had changed to semicolons, did it produce the results that you want?
yes, that's right I even tried not changing but it doesn't even recognize as a formula
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,580
Office Version
  1. 365
Platform
  1. Windows
yes, that's right
What is right?
- That you meant switch commas, or
- That it is related to settings, or
- It produced the correct results once changed?

Is your problem resolved?

If not, what is your usual formula list separator? That is, would you use
=SUM(1,2,3)
or
=SUM(1;2;3)

Can you copy/paste your previous working formula from CK1 directly here rather than using XL2BB?
 

valmir

Board Regular
Joined
Feb 10, 2021
Messages
79
Office Version
  1. 365
Platform
  1. Windows
Because of language settings I always have to use semicolons. Otherwise, the formula returns an error and shows me exactly where I have to change. I did a small trick even though I wish I had solved without that. This is what I did: I added three columns in CB, CC, and CD in the order I want and I applied the same formula. Now I would like help in another issue. In columns CR to CT I would like to have all names sorted alphabetically in column CS based on CR, also with the corresponding match in CT in the order as it is. Problem is: 1: names in CS are not sorted alphabetically. 2: I would like the sorting to ignore blanks on CS
Please take a look:
Stats 2016.xlsx
BYBZCACECFCGCHCICJCKCLCMCNCOCPCQCRCSCT
14ABBorra  33 PETAbdulAntónio NzayinawoPLSAbel LukangoAbel4ABBorra
24ABCadiata  21  MAIAbegáManuel Pedro PacaviraDESAbelardo Gomes SocolaBelito Socola4ABCadiata
34ABCamposAntónio Campos Calei124 73PLSAbelAbel LukangoDESAbengea Mambani AliAli4ABAntónio Campos CaleiCampos
44ABChara ?  21 INTAbianda 1Yann Junior Abianda 1DESAdão Francisco Congo ZalataDadão Bile4ABChara ?
54ABDany RibeiroDaniel Mendes Ribeiro62618KABAbianda 2Yann Junior Abianda 2PRIAdão Joaquim Bango CabaçaTony Cabaça4ABDaniel Mendes RibeiroDany Ribeiro
64ABDário AmorimDário Félix Amorim  3 LIBAdilson KivãoAdilson Ernesto KivãoCAAAdelino Wima Calunhi AntónioManinho4ABDário Félix AmorimDário Amorim
74ABDudú JackDomingos Monteiro Jack18474CAAAdilson ManuelAdilson Joaquim ManuelSAGAdérito Yandelela Chissoca 1Lelas 14ABDomingos Monteiro JackDudú Jack
84ABEdgardEdgard Arnaud Afane 18  ACAAdó PenaAntónio Joaquim Barros PenaMAIAdérito Yandelela Chissoca 2Lelas 24ABEdgard Arnaud AfaneEdgard
94ABEdi  1  ASAAdy ?INTAdilson Cipriano da CruzNeblú4ABEdi
104ABGeúdaGelson L. da S. Teles 87 BENAdy PauloAgostinho Domingos PauloLIBAdilson Ernesto KivãoAdilson Kivão4ABGelson L. da S. TelesGeúda
114ABGuiManuel Porfírio Pompílio de Matos 6  PLSAgoyaAugusto MakanjaCAAAdilson Joaquim ManuelAdilson Manuel4ABManuel Porfírio Pompílio de MatosGui
124ABHippiDomingos Paulo João José115175ACAAiresLudgero Aires Cachicote da RochaSAGAdolfo da Silva Catemba 1Denilson 14ABDomingos Paulo João JoséHippi
134ABIvanilson   5 MAIAlexAlexandre G. Mário MiguelPORAdolfo da Silva Catemba 2Denilson 24ABIvanilson
144ABJairzinhoJair Sebastião de Castro 85 PORAlexandreAlexandre Domingos Cristóvão M'FutilaPROAdriano Belmiro Duarte NicolauYano4ABJair Sebastião de CastroJairzinho
154ABJesse   3 DESAliAbengea Mambani AliDESAdriano da Costa Mateus AlbertoKumaca4ABJesse
164ABKandúDéo Kanda314927PROAlmeidaJosé Xavier FernandoASAAfonso Sebastião CabungulaFofó4ABDéo KandaKandú
174ABKizombéAlberto dos Santos Domingos 161 KABAmarildo 1Amarildo Eugénio Augusto Miranda 1INTAgostinho Cristóvão PaciênciaMabululu4ABAlberto dos Santos DomingosKizombé
184ABLara ?   1 ASAAmarildo 2Amarildo Eugénio Augusto Miranda 2BENAgostinho Domingos PauloAdy Paulo4ABLara ?
194ABLeloJosé Lelo Sevo Barros114 76BENAmaroAmândio Manuel Filipe da CostaKABAires Emilson Gonzaga ZecaEmilson4ABJosé Lelo Sevo BarrosLelo
204ABManucho MuquengueMariano Simão Muquengue2151146BENAmido BaldéAmido BaldéMAIAlbano Camuele SumanoPai da Bola4ABMariano Simão MuquengueManucho Muquengue
214ABMatambaMatamba Paulino Sousa127 77PROAnastácio 1Anastácio Manuel da Costa 1PRIAlbert Milambo-MutambaMilambo4ABMatamba Paulino SousaMatamba
224ABPaitoManuel Gaspar Fernandes 15 ASAAnastácio 2Anastácio Manuel da Costa 2PRIAlberto Álvaro PacaFissy4ABManuel Gaspar FernandesPaito
234ABPaluchoPaulo Pereira da Silva161378BENAnderson4ABAlberto dos Santos DomingosKizombé4ABPaulo Pereira da SilvaPalucho
244ABPilolaJosé Olívio Andrade Pereira311 28INTAntónioTomás Chilombo Cambuque SapaloSAGAlentua Tangala RolliRolli4ABJosé Olívio Andrade PereiraPilola
254ABPrazeresPrazeres Chijica M. Dala 31 SAGAry CalveteHelmut Ariadne Figueiredo CalvetePORAlexandre Domingos Cristóvão M'FutilaAlexandre4ABPrazeres Chijica M. DalaPrazeres
264ABRochanaPanda Marcelo José 23  PETAry OliveiraAriclene Assunção OliveiraMAIAlexandre G. Mário MiguelAlex4ABPanda Marcelo JoséRochana
274ABRui ?  82 PRIAry PapelManuel David AfonsoDESAlfredo Cassinda CalungangaChiwe4ABRui ?
284ABTony AugustoAntónio Pedro Augusto 7  PLSAtunakoEboue Manzambi AtunakoLIBAlfredo Kulembe RibeiroFredy4ABAntónio Pedro AugustoTony Augusto
294ABVanderVander Ramos Pinto 19  PROAvex 1Avelino Eduardo António Craque 1ASAAlmiro Edson Daniel LoboMiro4ABVander Ramos PintoVander
304ABXavierXavier Eduardo Vicente Vunge123 79DESAvex 2Avelino Eduardo António Craque 2BENAmândio Manuel Filipe da CostaAmaro4ABXavier Eduardo Vicente VungeXavier
314AB      SAGAyalaMário Álvaro AgostinhoKABAmarildo Eugénio Augusto Miranda 1Amarildo 14AB
324AB      PETAzulãoTiago Lima LealASAAmarildo Eugénio Augusto Miranda 2Amarildo 24AB
334AB      ASABabacarBabacar FallBENAmido BaldéAmido Baldé4AB
344AB      BENBabyValdemar Denso AntónioPROAnastácio Manuel da Costa 1Anastácio 14AB
354AB      PLSBadrickBadrick Paulo MadadiASAAnastácio Manuel da Costa 2Anastácio 24AB
364AB      PETBalacaiEvaristo Maurício PascoalASAAnderson Benjamim MonteiroGuelor4AB
374AB      PLSBarrezóJoveth AdãoBENAndré Augusto Miranda KongoToy4AB
384AB      INTBartoloBartolomeu Nguia BaptistaINTAnthony Kevin Mfede JuniorMfede4AB
39ACAAdó PenaAntónio Joaquim Barros Pena113280INTBebé 1Odimir Abreu Gabriel Breganha 14ABAntónio Campos CaleiCamposACAAntónio Joaquim Barros PenaAdó Pena
40ACAAiresLudgero Aires Cachicote da Rocha 1  ASABebé 2Odimir Abreu Gabriel Breganha 2PROAntónio da Silva AnatoSilva AnatoACALudgero Aires Cachicote da RochaAires
41ACABorges 1Cláudio Ricardo Cunha Borges 118181PETBeboManuel João Miguel da CostaSAGAntónio Gonçalo CassuleGomito CassuleACACláudio Ricardo Cunha Borges 1Borges 1
42ACACabibi Abreu 1Mário Rui de Abreu 1  1 DESBebuchoMárcio Sakuala Ramalho HenriquesACAAntónio Joaquim Barros PenaAdó PenaACAMário Rui de Abreu 1Cabibi Abreu 1
43ACACamotaJúlio Camota Marcelino Tito 21 PROBelito ?PROAntónio Luís dos Santos SerradoLunguinhaACAJúlio Camota Marcelino TitoCamota
44ACAChabalalaGaspar Necas Fortunato34629DESBelito SocolaAbelardo Gomes SocolaSAGAntónio Manuel da Silva OliveiraOliveiraACAGaspar Necas FortunatoChabalala
45ACAChilóFrancisco Ananias Orlando 115 BENBenaDiveluca Simão NascimentoPETAntónio NzayinawoAbdulACAFrancisco Ananias OrlandoChiló
46ACACláudioCláudio Américo Loi Sozinho118282SAGBeniBeni Kimbi Fua4ABAntónio Pedro AugustoTony AugustoACACláudio Américo Loi SozinhoCláudio
47ACADay DayZaldivar Doval Augusto Cambinda  1 PROBenvindo GarciaBenvindo Regresso Pontes GarciaINTAntónio Sapalo Lohoca JustoPatyACAZaldivar Doval Augusto CambindaDay Day
48ACADjoMartins Geovety P. Gervásio 8  PETBenvindo NsianfumuBenvindo Afonso NsianfumuPRIAntunes Sargento EkundiSargentoACAMartins Geovety P. GervásioDjo
49ACAEltonElton Ernesto de Carvalho  4 CAABenyTeodoro Edvaldo Rita TchissinguiPETAriclene Assunção OliveiraAry OliveiraACAElton Ernesto de CarvalhoElton
50ACAFaniJosé Afonso dos Santos Fernando 21  MAIBernardoPatrick Kifu ApatakiPORArmando Domingos MariaLindalaACAJosé Afonso dos Santos FernandoFani
STATS2
Cell Formulas
RangeFormula
BZ1BZ1=INDIRECT("4AB!B5")
CA1CA1=INDIRECT("4AB!C5")
CE1CE1=INDIRECT("4AB!E5")
CF1CF1=INDIRECT("4AB!F5")
CG1CG1=INDIRECT("4AB!G5")
CH1:CH50CH1=IF($CE1=0,"",IF($CE1>0,RANK(CE1,$CE$1:$CE$608)+COUNTIF($CE$1:CE1,CE1)-1))
BZ2BZ2=INDIRECT("4AB!B6")
CA2CA2=INDIRECT("4AB!C6")
CE2CE2=INDIRECT("4AB!E6")
CF2CF2=INDIRECT("4AB!F6")
CG2CG2=INDIRECT("4AB!G6")
BZ3BZ3=INDIRECT("4AB!B7")
CA3CA3=INDIRECT("4AB!C7")
CE3CE3=INDIRECT("4AB!E7")
CF3CF3=INDIRECT("4AB!F7")
CG3CG3=INDIRECT("4AB!G7")
BZ4BZ4=INDIRECT("4AB!B8")
CA4CA4=INDIRECT("4AB!C8")
CE4CE4=INDIRECT("4AB!E8")
CF4CF4=INDIRECT("4AB!F8")
CG4CG4=INDIRECT("4AB!G8")
BZ5BZ5=INDIRECT("4AB!B9")
CA5CA5=INDIRECT("4AB!C9")
CE5CE5=INDIRECT("4AB!E9")
CF5CF5=INDIRECT("4AB!F9")
CG5CG5=INDIRECT("4AB!G9")
BZ6BZ6=INDIRECT("4AB!B10")
CA6CA6=INDIRECT("4AB!C10")
CE6CE6=INDIRECT("4AB!E10")
CF6CF6=INDIRECT("4AB!F10")
CG6CG6=INDIRECT("4AB!G10")
BZ7BZ7=INDIRECT("4AB!B11")
CA7CA7=INDIRECT("4AB!C11")
CE7CE7=INDIRECT("4AB!E11")
CF7CF7=INDIRECT("4AB!F11")
CG7CG7=INDIRECT("4AB!G11")
BZ8BZ8=INDIRECT("4AB!B12")
CA8CA8=INDIRECT("4AB!C12")
CE8CE8=INDIRECT("4AB!E12")
CF8CF8=INDIRECT("4AB!F12")
CG8CG8=INDIRECT("4AB!G12")
BZ9BZ9=INDIRECT("4AB!B13")
CA9CA9=INDIRECT("4AB!C13")
CE9CE9=INDIRECT("4AB!E13")
CF9CF9=INDIRECT("4AB!F13")
CG9CG9=INDIRECT("4AB!G13")
BZ10BZ10=INDIRECT("4AB!B14")
CA10CA10=INDIRECT("4AB!C14")
CE10CE10=INDIRECT("4AB!E14")
CF10CF10=INDIRECT("4AB!F14")
CG10CG10=INDIRECT("4AB!G14")
BZ11BZ11=INDIRECT("4AB!B15")
CA11CA11=INDIRECT("4AB!C15")
CE11CE11=INDIRECT("4AB!E15")
CF11CF11=INDIRECT("4AB!F15")
CG11CG11=INDIRECT("4AB!G15")
BZ12BZ12=INDIRECT("4AB!B16")
CA12CA12=INDIRECT("4AB!C16")
CE12CE12=INDIRECT("4AB!E16")
CF12CF12=INDIRECT("4AB!F16")
CG12CG12=INDIRECT("4AB!G16")
BZ13BZ13=INDIRECT("4AB!B17")
CA13CA13=INDIRECT("4AB!C17")
CE13CE13=INDIRECT("4AB!E17")
CF13CF13=INDIRECT("4AB!F17")
CG13CG13=INDIRECT("4AB!G17")
BZ14BZ14=INDIRECT("4AB!B18")
CA14CA14=INDIRECT("4AB!C18")
CE14CE14=INDIRECT("4AB!E18")
CF14CF14=INDIRECT("4AB!F18")
CG14CG14=INDIRECT("4AB!G18")
BZ15BZ15=INDIRECT("4AB!B19")
CA15CA15=INDIRECT("4AB!C19")
CE15CE15=INDIRECT("4AB!E19")
CF15CF15=INDIRECT("4AB!F19")
CG15CG15=INDIRECT("4AB!G19")
BZ16BZ16=INDIRECT("4AB!B20")
CA16CA16=INDIRECT("4AB!C20")
CE16CE16=INDIRECT("4AB!E20")
CF16CF16=INDIRECT("4AB!F20")
CG16CG16=INDIRECT("4AB!G20")
BZ17BZ17=INDIRECT("4AB!B21")
CA17CA17=INDIRECT("4AB!C21")
CE17CE17=INDIRECT("4AB!E21")
CF17CF17=INDIRECT("4AB!F21")
CG17CG17=INDIRECT("4AB!G21")
BZ18BZ18=INDIRECT("4AB!B22")
CA18CA18=INDIRECT("4AB!C22")
CE18CE18=INDIRECT("4AB!E22")
CF18CF18=INDIRECT("4AB!F22")
CG18CG18=INDIRECT("4AB!G22")
BZ19BZ19=INDIRECT("4AB!B23")
CA19CA19=INDIRECT("4AB!C23")
CE19CE19=INDIRECT("4AB!E23")
CF19CF19=INDIRECT("4AB!F23")
CG19CG19=INDIRECT("4AB!G23")
BZ20BZ20=INDIRECT("4AB!B24")
CA20CA20=INDIRECT("4AB!C24")
CE20CE20=INDIRECT("4AB!E24")
CF20CF20=INDIRECT("4AB!F24")
CG20CG20=INDIRECT("4AB!G24")
BZ21BZ21=INDIRECT("4AB!B25")
CA21CA21=INDIRECT("4AB!C25")
CE21CE21=INDIRECT("4AB!E25")
CF21CF21=INDIRECT("4AB!F25")
CG21CG21=INDIRECT("4AB!G25")
BZ22BZ22=INDIRECT("4AB!B26")
CA22CA22=INDIRECT("4AB!C26")
CE22CE22=INDIRECT("4AB!E26")
CF22CF22=INDIRECT("4AB!F26")
CG22CG22=INDIRECT("4AB!G26")
BZ23BZ23=INDIRECT("4AB!B27")
CA23CA23=INDIRECT("4AB!C27")
CE23CE23=INDIRECT("4AB!E27")
CF23CF23=INDIRECT("4AB!F27")
CG23CG23=INDIRECT("4AB!G27")
BZ24BZ24=INDIRECT("4AB!B28")
CA24CA24=INDIRECT("4AB!C28")
CE24CE24=INDIRECT("4AB!E28")
CF24CF24=INDIRECT("4AB!F28")
CG24CG24=INDIRECT("4AB!G28")
BZ25BZ25=INDIRECT("4AB!B29")
CA25CA25=INDIRECT("4AB!C29")
CE25CE25=INDIRECT("4AB!E29")
CF25CF25=INDIRECT("4AB!F29")
CG25CG25=INDIRECT("4AB!G29")
BZ26BZ26=INDIRECT("4AB!B30")
CA26CA26=INDIRECT("4AB!C30")
CE26CE26=INDIRECT("4AB!E30")
CF26CF26=INDIRECT("4AB!F30")
CG26CG26=INDIRECT("4AB!G30")
BZ27BZ27=INDIRECT("4AB!B31")
CA27CA27=INDIRECT("4AB!C31")
CE27CE27=INDIRECT("4AB!E31")
CF27CF27=INDIRECT("4AB!F31")
CG27CG27=INDIRECT("4AB!G31")
BZ28BZ28=INDIRECT("4AB!B32")
CA28CA28=INDIRECT("4AB!C32")
CE28CE28=INDIRECT("4AB!E32")
CF28CF28=INDIRECT("4AB!F32")
CG28CG28=INDIRECT("4AB!G32")
BZ29BZ29=INDIRECT("4AB!B33")
CA29CA29=INDIRECT("4AB!C33")
CE29CE29=INDIRECT("4AB!E33")
CF29CF29=INDIRECT("4AB!F33")
CG29CG29=INDIRECT("4AB!G33")
BZ30BZ30=INDIRECT("4AB!B34")
CA30CA30=INDIRECT("4AB!C34")
CE30CE30=INDIRECT("4AB!E34")
CF30CF30=INDIRECT("4AB!F34")
CG30CG30=INDIRECT("4AB!G34")
BZ31BZ31=INDIRECT("4AB!B35")
CA31CA31=INDIRECT("4AB!C35")
CE31CE31=INDIRECT("4AB!E35")
CF31CF31=INDIRECT("4AB!F35")
CG31CG31=INDIRECT("4AB!G35")
BZ32BZ32=INDIRECT("4AB!B36")
CA32CA32=INDIRECT("4AB!C36")
CE32CE32=INDIRECT("4AB!E36")
CF32CF32=INDIRECT("4AB!F36")
CG32CG32=INDIRECT("4AB!G36")
BZ33BZ33=INDIRECT("4AB!B37")
CA33CA33=INDIRECT("4AB!C37")
CE33CE33=INDIRECT("4AB!E37")
CF33CF33=INDIRECT("4AB!F37")
CG33CG33=INDIRECT("4AB!G37")
BZ34BZ34=INDIRECT("4AB!B38")
CA34CA34=INDIRECT("4AB!C38")
CE34CE34=INDIRECT("4AB!E38")
CF34CF34=INDIRECT("4AB!F38")
CG34CG34=INDIRECT("4AB!G38")
BZ35BZ35=INDIRECT("4AB!B39")
CA35CA35=INDIRECT("4AB!C39")
CE35CE35=INDIRECT("4AB!E39")
CF35CF35=INDIRECT("4AB!F39")
CG35CG35=INDIRECT("4AB!G39")
BZ36BZ36=INDIRECT("4AB!B40")
CA36CA36=INDIRECT("4AB!C40")
CE36CE36=INDIRECT("4AB!E40")
CF36CF36=INDIRECT("4AB!F40")
CG36CG36=INDIRECT("4AB!G40")
BZ37BZ37=INDIRECT("4AB!B41")
CA37CA37=INDIRECT("4AB!C41")
CE37CE37=INDIRECT("4AB!E41")
CF37CF37=INDIRECT("4AB!F41")
CG37CG37=INDIRECT("4AB!G41")
BZ38BZ38=INDIRECT("4AB!B42")
CA38CA38=INDIRECT("4AB!C42")
CE38CE38=INDIRECT("4AB!E42")
CF38CF38=INDIRECT("4AB!F42")
CG38CG38=INDIRECT("4AB!G42")
BZ39BZ39=INDIRECT("ACA!B5")
CA39CA39=INDIRECT("ACA!C5")
CE39CE39=INDIRECT("ACA!E5")
CF39CF39=INDIRECT("ACA!F5")
CG39CG39=INDIRECT("ACA!G5")
BZ40BZ40=INDIRECT("ACA!B6")
CA40CA40=INDIRECT("ACA!C6")
CE40CE40=INDIRECT("ACA!E6")
CF40CF40=INDIRECT("ACA!F6")
CG40CG40=INDIRECT("ACA!G6")
BZ41BZ41=INDIRECT("ACA!B7")
CA41CA41=INDIRECT("ACA!C7")
CE41CE41=INDIRECT("ACA!E7")
CF41CF41=INDIRECT("ACA!F7")
CG41CG41=INDIRECT("ACA!G7")
BZ42BZ42=INDIRECT("ACA!B8")
CA42CA42=INDIRECT("ACA!C8")
CE42CE42=INDIRECT("ACA!E8")
CF42CF42=INDIRECT("ACA!F8")
CG42CG42=INDIRECT("ACA!G8")
BZ43BZ43=INDIRECT("ACA!B9")
CA43CA43=INDIRECT("ACA!C9")
CE43CE43=INDIRECT("ACA!E9")
CF43CF43=INDIRECT("ACA!F9")
CG43CG43=INDIRECT("ACA!G9")
BZ44BZ44=INDIRECT("ACA!B10")
CA44CA44=INDIRECT("ACA!C10")
CE44CE44=INDIRECT("ACA!E10")
CF44CF44=INDIRECT("ACA!F10")
CG44CG44=INDIRECT("ACA!G10")
BZ45BZ45=INDIRECT("ACA!B11")
CA45CA45=INDIRECT("ACA!C11")
CE45CE45=INDIRECT("ACA!E11")
CF45CF45=INDIRECT("ACA!F11")
CG45CG45=INDIRECT("ACA!G11")
BZ46BZ46=INDIRECT("ACA!B12")
CA46CA46=INDIRECT("ACA!C12")
CE46CE46=INDIRECT("ACA!E12")
CF46CF46=INDIRECT("ACA!F12")
CG46CG46=INDIRECT("ACA!G12")
BZ47BZ47=INDIRECT("ACA!B13")
CA47CA47=INDIRECT("ACA!C13")
CE47CE47=INDIRECT("ACA!E13")
CF47CF47=INDIRECT("ACA!F13")
CG47CG47=INDIRECT("ACA!G13")
BZ48BZ48=INDIRECT("ACA!B14")
CA48CA48=INDIRECT("ACA!C14")
CE48CE48=INDIRECT("ACA!E14")
CF48CF48=INDIRECT("ACA!F14")
CG48CG48=INDIRECT("ACA!G14")
BZ49BZ49=INDIRECT("ACA!B15")
CA49CA49=INDIRECT("ACA!C15")
CE49CE49=INDIRECT("ACA!E15")
CF49CF49=INDIRECT("ACA!F15")
CG49CG49=INDIRECT("ACA!G15")
BZ50BZ50=INDIRECT("ACA!B16")
CA50CA50=INDIRECT("ACA!C16")
CE50CE50=INDIRECT("ACA!E16")
CF50CF50=INDIRECT("ACA!F16")
CG50CG50=INDIRECT("ACA!G16")
CJ1:CL501CJ1=SORT(FILTER(BY1:CA608,BZ1:BZ608<>0),2,1)
CN1:CP478CN1=SORT(FILTER(CB1:CD608,CC1:CC608<>0),2,1)
CR1:CT608CR1=SORT(FILTER(CB1:CD608,CB1:CB608<>0),1,1)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
CR1:CT608Cell Value=0textNO
BY1Expression=BZ1=0textNO
BZ2:BZ608Expression=CA2=0textNO
BZ1Expression=CA1=0textNO
CA1:CA608Cell Value=0textNO
CL1:CL608Cell Value=0textNO
CK1:CK608Expression=CL1=0textNO
CE1:CG608Cell Value=0textNO
B12,CC1:CC607Cell Value=0textNO
 

valmir

Board Regular
Joined
Feb 10, 2021
Messages
79
Office Version
  1. 365
Platform
  1. Windows
In other words, I have 16 teams on BY, so I would like to have the full names at each team to be sorted alphabetically with the corresponding nick names
 

Watch MrExcel Video

Forum statistics

Threads
1,130,257
Messages
5,641,168
Members
417,195
Latest member
Vishal kumar

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
Top