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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,560
Office Version
  1. 365
Platform
  1. Windows
I don't fully understand. Can you make a slightly smaller sample set (should only need 15 or so rows) and post that taking into consideration the following..
  • Include the results, manually entered, exactly as you want them to appear and make it clear which columns they are. Since there are no headings, I don't know what are full names and what are nick names.
  • If CB:CE are being used as part of this process I would like them to be unhidden.
  • But you can hide any columns that are not part of this process (eg it looks like CE:CI at least are not relevant to this part of your requirement?)
  • In your sample for the forum in columns BZ & Ca copy & paste special (values) so we don't get all those INDIRECT formulas in your post.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

valmir

Board Regular
Joined
Feb 10, 2021
Messages
79
Office Version
  1. 365
Platform
  1. Windows
I don't fully understand. Can you make a slightly smaller sample set (should only need 15 or so rows) and post that taking into consideration the following..
  • Include the results, manually entered, exactly as you want them to appear and make it clear which columns they are. Since there are no headings, I don't know what are full names and what are nick names.
  • If CB:CE are being used as part of this process I would like them to be unhidden.
  • But you can hide any columns that are not part of this process (eg it looks like CE:CI at least are not relevant to this part of your requirement?)
  • In your sample for the forum in columns BZ & Ca copy & paste special (values) so we don't get all those INDIRECT formulas in your post.
Cell Formulas
RangeFormula
BZ1BZ1=INDIRECT("4AB!B5")
CA1CA1=INDIRECT("4AB!C5")
CB1CB1=INDIRECT("BY1")
CC1CC1=INDIRECT("CA1")
CD1CD1=INDIRECT("BZ1")
BZ2BZ2=INDIRECT("4AB!B6")
CA2CA2=INDIRECT("4AB!C6")
CB2CB2=INDIRECT("BY2")
CC2CC2=INDIRECT("CA2")
CD2CD2=INDIRECT("BZ2")
BZ3BZ3=INDIRECT("4AB!B7")
CA3CA3=INDIRECT("4AB!C7")
CB3CB3=INDIRECT("BY3")
CC3CC3=INDIRECT("CA3")
CD3CD3=INDIRECT("BZ3")
BZ4BZ4=INDIRECT("4AB!B8")
CA4CA4=INDIRECT("4AB!C8")
CB4CB4=INDIRECT("BY4")
CC4CC4=INDIRECT("CA4")
CD4CD4=INDIRECT("BZ4")
BZ5BZ5=INDIRECT("4AB!B9")
CA5CA5=INDIRECT("4AB!C9")
CB5CB5=INDIRECT("BY5")
CC5CC5=INDIRECT("CA5")
CD5CD5=INDIRECT("BZ5")
BZ6BZ6=INDIRECT("4AB!B10")
CA6CA6=INDIRECT("4AB!C10")
CB6CB6=INDIRECT("BY6")
CC6CC6=INDIRECT("CA6")
CD6CD6=INDIRECT("BZ6")
BZ7BZ7=INDIRECT("4AB!B11")
CA7CA7=INDIRECT("4AB!C11")
CB7CB7=INDIRECT("BY7")
CC7CC7=INDIRECT("CA7")
CD7CD7=INDIRECT("BZ7")
BZ8BZ8=INDIRECT("4AB!B12")
CA8CA8=INDIRECT("4AB!C12")
CB8CB8=INDIRECT("BY8")
CC8CC8=INDIRECT("CA8")
CD8CD8=INDIRECT("BZ8")
BZ9BZ9=INDIRECT("4AB!B13")
CA9CA9=INDIRECT("4AB!C13")
CB9CB9=INDIRECT("BY9")
CC9CC9=INDIRECT("CA9")
CD9CD9=INDIRECT("BZ9")
BZ10BZ10=INDIRECT("4AB!B14")
CA10CA10=INDIRECT("4AB!C14")
CB10CB10=INDIRECT("BY10")
CC10CC10=INDIRECT("CA10")
CD10CD10=INDIRECT("BZ10")
BZ11BZ11=INDIRECT("4AB!B15")
CA11CA11=INDIRECT("4AB!C15")
CB11CB11=INDIRECT("BY11")
CC11CC11=INDIRECT("CA11")
CD11CD11=INDIRECT("BZ11")
BZ12BZ12=INDIRECT("4AB!B16")
CA12CA12=INDIRECT("4AB!C16")
CB12CB12=INDIRECT("BY12")
CC12CC12=INDIRECT("CA12")
CD12CD12=INDIRECT("BZ12")
BZ13BZ13=INDIRECT("4AB!B17")
CA13CA13=INDIRECT("4AB!C17")
CB13CB13=INDIRECT("BY13")
CC13CC13=INDIRECT("CA13")
CD13CD13=INDIRECT("BZ13")
BZ14BZ14=INDIRECT("4AB!B18")
CA14CA14=INDIRECT("4AB!C18")
CB14CB14=INDIRECT("BY14")
CC14CC14=INDIRECT("CA14")
CD14CD14=INDIRECT("BZ14")
BZ15BZ15=INDIRECT("4AB!B19")
CA15CA15=INDIRECT("4AB!C19")
CB15CB15=INDIRECT("BY15")
CC15CC15=INDIRECT("CA15")
CD15CD15=INDIRECT("BZ15")
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
CD2:CD608Expression=CE2=0textNO
CD1Expression=CE1=0textNO
BY1Expression=BZ1=0textNO
BZ2:BZ608Expression=CA2=0textNO
BZ1Expression=CA1=0textNO
CA1:CA608Cell Value=0textNO
B12,CC1:CC607Cell Value=0textNO
 

valmir

Board Regular
Joined
Feb 10, 2021
Messages
79
Office Version
  1. 365
Platform
  1. Windows
CB:CE are being used as source columns. The order of the columns is just as it is. The main criteria is for CS column (names) to be alphabetically sorted with the corresponding matches in CR (team names) and CT (nick names). Example for the first row (alphabetically sorted): CR1 should be 4AB, CS1 Alberto dos Santos Domingos and CT1 Kizombé
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,560
Office Version
  1. 365
Platform
  1. Windows
Include the results, manually entered, exactly as you want them to appear and make it clear which columns they are.
I can't see that anywhere?


In your sample for the forum in columns BZ & Ca copy & paste special (values) so we don't get all those INDIRECT formulas in your post.
Unfortunately that doesn't seem to have happened either. :(

There is also no need for the Conditional Formatting information to be posted. You can avoid that here:

1617011211922.png
 

valmir

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

ADVERTISEMENT

I can't see that anywhere?



Unfortunately that doesn't seem to have happened either. :(

There is also no need for the Conditional Formatting information to be posted. You can avoid that here:

View attachment 35522
I don't really know what you mean by "include the results manually entered". to make it simpler I just included the source column and I want those three columns in that same order with the values in the middle column to be alphabetically sorted based on the values in the first column. In the example given, there are only two values in the first column (4AB and ACA but in the whole list I have 16 in total, so the names in the middle column should be alphabetically sorted for 4AB and then for ACA and so on). I am sorry that I am not so wise as to understand what you are asking me to do.
Cell Formulas
RangeFormula
CB1CB1=INDIRECT("BY1")
CC1CC1=INDIRECT("CA1")
CD1CD1=INDIRECT("BZ1")
CB2CB2=INDIRECT("BY2")
CC2CC2=INDIRECT("CA2")
CD2CD2=INDIRECT("BZ2")
CB3CB3=INDIRECT("BY3")
CC3CC3=INDIRECT("CA3")
CD3CD3=INDIRECT("BZ3")
CB4CB4=INDIRECT("BY4")
CC4CC4=INDIRECT("CA4")
CD4CD4=INDIRECT("BZ4")
CB5CB5=INDIRECT("BY5")
CC5CC5=INDIRECT("CA5")
CD5CD5=INDIRECT("BZ5")
CB6CB6=INDIRECT("BY6")
CC6CC6=INDIRECT("CA6")
CD6CD6=INDIRECT("BZ6")
CB7CB7=INDIRECT("BY7")
CC7CC7=INDIRECT("CA7")
CD7CD7=INDIRECT("BZ7")
CB8CB8=INDIRECT("BY8")
CC8CC8=INDIRECT("CA8")
CD8CD8=INDIRECT("BZ8")
CB9CB9=INDIRECT("BY9")
CC9CC9=INDIRECT("CA9")
CD9CD9=INDIRECT("BZ9")
CB10CB10=INDIRECT("BY10")
CC10CC10=INDIRECT("CA10")
CD10CD10=INDIRECT("BZ10")
CB11CB11=INDIRECT("BY11")
CC11CC11=INDIRECT("CA11")
CD11CD11=INDIRECT("BZ11")
CB12CB12=INDIRECT("BY12")
CC12CC12=INDIRECT("CA12")
CD12CD12=INDIRECT("BZ12")
CB13CB13=INDIRECT("BY13")
CC13CC13=INDIRECT("CA13")
CD13CD13=INDIRECT("BZ13")
CB14CB14=INDIRECT("BY14")
CC14CC14=INDIRECT("CA14")
CD14CD14=INDIRECT("BZ14")
CB15CB15=INDIRECT("BY15")
CC15CC15=INDIRECT("CA15")
CD15CD15=INDIRECT("BZ15")
CB16CB16=INDIRECT("BY16")
CC16CC16=INDIRECT("CA16")
CD16CD16=INDIRECT("BZ16")
CB17CB17=INDIRECT("BY17")
CC17CC17=INDIRECT("CA17")
CD17CD17=INDIRECT("BZ17")
CB18CB18=INDIRECT("BY18")
CC18CC18=INDIRECT("CA18")
CD18CD18=INDIRECT("BZ18")
CB19CB19=INDIRECT("BY19")
CC19CC19=INDIRECT("CA19")
CD19CD19=INDIRECT("BZ19")
CB20CB20=INDIRECT("BY20")
CC20CC20=INDIRECT("CA20")
CD20CD20=INDIRECT("BZ20")
CB21CB21=INDIRECT("BY21")
CC21CC21=INDIRECT("CA21")
CD21CD21=INDIRECT("BZ21")
CB22CB22=INDIRECT("BY22")
CC22CC22=INDIRECT("CA22")
CD22CD22=INDIRECT("BZ22")
CB23CB23=INDIRECT("BY23")
CC23CC23=INDIRECT("CA23")
CD23CD23=INDIRECT("BZ23")
CB24CB24=INDIRECT("BY24")
CC24CC24=INDIRECT("CA24")
CD24CD24=INDIRECT("BZ24")
CB25CB25=INDIRECT("BY25")
CC25CC25=INDIRECT("CA25")
CD25CD25=INDIRECT("BZ25")
CB26CB26=INDIRECT("BY26")
CC26CC26=INDIRECT("CA26")
CD26CD26=INDIRECT("BZ26")
CB27CB27=INDIRECT("BY27")
CC27CC27=INDIRECT("CA27")
CD27CD27=INDIRECT("BZ27")
CB28CB28=INDIRECT("BY28")
CC28CC28=INDIRECT("CA28")
CD28CD28=INDIRECT("BZ28")
CB29CB29=INDIRECT("BY29")
CC29CC29=INDIRECT("CA29")
CD29CD29=INDIRECT("BZ29")
CB30CB30=INDIRECT("BY30")
CC30CC30=INDIRECT("CA30")
CD30CD30=INDIRECT("BZ30")
CB31CB31=INDIRECT("BY31")
CC31CC31=INDIRECT("CA31")
CD31CD31=INDIRECT("BZ31")
CB32CB32=INDIRECT("BY32")
CC32CC32=INDIRECT("CA32")
CD32CD32=INDIRECT("BZ32")
CB33CB33=INDIRECT("BY33")
CC33CC33=INDIRECT("CA33")
CD33CD33=INDIRECT("BZ33")
CB34CB34=INDIRECT("BY34")
CC34CC34=INDIRECT("CA34")
CD34CD34=INDIRECT("BZ34")
CB35CB35=INDIRECT("BY35")
CC35CC35=INDIRECT("CA35")
CD35CD35=INDIRECT("BZ35")
CB36CB36=INDIRECT("BY36")
CC36CC36=INDIRECT("CA36")
CD36CD36=INDIRECT("BZ36")
CB37CB37=INDIRECT("BY37")
CC37CC37=INDIRECT("CA37")
CD37CD37=INDIRECT("BZ37")
CB38CB38=INDIRECT("BY38")
CC38CC38=INDIRECT("CA38")
CD38CD38=INDIRECT("BZ38")
CB39CB39=INDIRECT("BY39")
CC39CC39=INDIRECT("CA39")
CD39CD39=INDIRECT("BZ39")
CB40CB40=INDIRECT("BY40")
CC40CC40=INDIRECT("CA40")
CD40CD40=INDIRECT("BZ40")
CB41CB41=INDIRECT("BY41")
CC41CC41=INDIRECT("CA41")
CD41CD41=INDIRECT("BZ41")
CB42CB42=INDIRECT("BY42")
CC42CC42=INDIRECT("CA42")
CD42CD42=INDIRECT("BZ42")
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,560
Office Version
  1. 365
Platform
  1. Windows
I don't really know what you mean by "include the results manually entered"
I mean, if I gave you a formula that did exactly what you wanted, what values would be in what cells?
Don't describe that in words, actually put the values in the cells in your worksheet yourself and then post the results.

.. and please, if posting XL2BB again, first copy CB1:CD42 and Paste Special - Values so we don't have all those INDIRECT formulas.
 

valmir

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

ADVERTISEMENT

I mean, if I gave you a formula that did exactly what you wanted, what values would be in what cells?
Don't describe that in words, actually put the values in the cells in your worksheet yourself and then post the results.

.. and please, if posting XL2BB again, first copy CB1:CD42 and Paste Special - Values so we don't have all those INDIRECT formulas.
Ok Sir I got it now
Stats 2016.xlsx
CICJCKCLCMCN
14AB0Borra4ABAlberto dos Santos DomingosKizombé
24AB0Cadiata4ABAntónio Campos CaleiCampos
34ABAntónio Campos CaleiCampos4ABAntónio Pedro AugustoTony Augusto
44AB0Chara ?4ABDaniel Mendes RibeiroDany Ribeiro
54ABDaniel Mendes RibeiroDany Ribeiro4ABDário Félix AmorimDário Amorim
64ABDário Félix AmorimDário Amorim4ABDéo KandaKandú
74ABDomingos Monteiro JackDudú Jack4ABDomingos Monteiro JackDudú Jack
84ABEdgard Arnaud AfaneEdgard4ABDomingos Paulo João JoséHippi
94AB0Edi4ABEdgard Arnaud AfaneEdgard
104ABGelson L. da S. TelesGeúda4ABGelson L. da S. TelesGeúda
114ABManuel Porfírio Pompílio de MatosGui4ABJair Sebastião de CastroJairzinho
124ABDomingos Paulo João JoséHippi4ABJosé Lelo Sevo BarrosLelo
134AB0Ivanilson4ABJosé Olívio Andrade PereiraPilola
144ABJair Sebastião de CastroJairzinho4ABManuel Gaspar FernandesPaito
154AB0Jesse4ABManuel Porfírio Pompílio de MatosGui
164ABDéo KandaKandú4ABMariano Simão MuquengueManucho Muquengue
174ABAlberto dos Santos DomingosKizombé4ABMatamba Paulino SousaMatamba
184AB0Lara ?4ABPanda Marcelo JoséRochana
194ABJosé Lelo Sevo BarrosLelo4ABPaulo Pereira da SilvaPalucho
204ABMariano Simão MuquengueManucho Muquengue4ABPrazeres Chijica M. DalaPrazeres
214ABMatamba Paulino SousaMatamba4ABVander Ramos PintoVander
224ABManuel Gaspar FernandesPaito4ABXavier Eduardo Vicente VungeXavier
234ABPaulo Pereira da SilvaPaluchoACAAntónio Joaquim Barros PenaAdó Pena
244ABJosé Olívio Andrade PereiraPilolaACACláudio Américo Loi SozinhoCláudio
254ABPrazeres Chijica M. DalaPrazeresACACláudio Ricardo Cunha Borges 1Borges 1
264ABPanda Marcelo JoséRochanaACADomingos Zaya NunesGância
274AB0Rui ?ACAElton Ernesto de CarvalhoElton
284ABAntónio Pedro AugustoTony AugustoACAEvaristo Daniel KapungeLito Kapunge
294ABVander Ramos PintoVanderACAEzequiel Paulo JuliãoHigino
304ABXavier Eduardo Vicente VungeXavierACAFilipe Tomás João TchitungoFilipe Tchitungo
314AB00ACAFrancisco Ananias OrlandoChiló
324AB00ACAGaspar Necas FortunatoChabalala
334AB00ACAIbraime Barreto CassamáIbraime
344AB00ACAJorge Paulo Lima AlvesKadú
354AB00
364AB00
374AB00
384AB00
39ACAAntónio Joaquim Barros PenaAdó Pena
40ACALudgero Aires Cachicote da RochaAires
41ACACláudio Ricardo Cunha Borges 1Borges 1
STATS2
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,560
Office Version
  1. 365
Platform
  1. Windows
That data and results don't seem to make complete sense.
Column CL has 12 rows with ACA but column CI only has 3 rows. However, see if this does what you want.

valmir.xlsm
CICJCKCLCMCN
14AB0Borra4ABAlberto dos Santos DomingosKizombé
24AB0Cadiata4ABAntónio Campos CaleiCampos
34ABAntónio Campos CaleiCampos4ABAntónio Pedro AugustoTony Augusto
44AB0Chara ?4ABDaniel Mendes RibeiroDany Ribeiro
54ABDaniel Mendes RibeiroDany Ribeiro4ABDário Félix AmorimDário Amorim
64ABDário Félix AmorimDário Amorim4ABDéo KandaKandú
74ABDomingos Monteiro JackDudú Jack4ABDomingos Monteiro JackDudú Jack
84ABEdgard Arnaud AfaneEdgard4ABDomingos Paulo João JoséHippi
94AB0Edi4ABEdgard Arnaud AfaneEdgard
104ABGelson L. da S. TelesGeúda4ABGelson L. da S. TelesGeúda
114ABManuel Porfírio Pompílio de MatosGui4ABJair Sebastião de CastroJairzinho
124ABDomingos Paulo João JoséHippi4ABJosé Lelo Sevo BarrosLelo
134AB0Ivanilson4ABJosé Olívio Andrade PereiraPilola
144ABJair Sebastião de CastroJairzinho4ABManuel Gaspar FernandesPaito
154AB0Jesse4ABManuel Porfírio Pompílio de MatosGui
164ABDéo KandaKandú4ABMariano Simão MuquengueManucho Muquengue
174ABAlberto dos Santos DomingosKizombé4ABMatamba Paulino SousaMatamba
184AB0Lara ?4ABPanda Marcelo JoséRochana
194ABJosé Lelo Sevo BarrosLelo4ABPaulo Pereira da SilvaPalucho
204ABMariano Simão MuquengueManucho Muquengue4ABPrazeres Chijica M. DalaPrazeres
214ABMatamba Paulino SousaMatamba4ABVander Ramos PintoVander
224ABManuel Gaspar FernandesPaito4ABXavier Eduardo Vicente VungeXavier
234ABPaulo Pereira da SilvaPaluchoACAAntónio Joaquim Barros PenaAdó Pena
244ABJosé Olívio Andrade PereiraPilolaACACláudio Ricardo Cunha Borges 1Borges 1
254ABPrazeres Chijica M. DalaPrazeresACALudgero Aires Cachicote da RochaAires
264ABPanda Marcelo JoséRochana
274AB0Rui ?
284ABAntónio Pedro AugustoTony Augusto
294ABVander Ramos PintoVander
304ABXavier Eduardo Vicente VungeXavier
314AB00
324AB00
334AB00
344AB00
354AB00
364AB00
374AB00
384AB00
39ACAAntónio Joaquim Barros PenaAdó Pena
40ACALudgero Aires Cachicote da RochaAires
41ACACláudio Ricardo Cunha Borges 1Borges 1
Sheet2
Cell Formulas
RangeFormula
CL1:CN25CL1=SORT(FILTER(CI1:CK41,CJ1:CJ41<>0),{1,2})
Dynamic array formulas.
 
Solution

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,560
Office Version
  1. 365
Platform
  1. Windows
You are welcome. Glad we got there in the end. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,130
Messages
5,640,300
Members
417,135
Latest member
zeusmining

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