Swap column order in sort filter formula

[valmir]

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
	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM
1	4AB	Borra			3	3			PET	Abdul	António Nzayinawo		PLS	Abel	Abel Lukango
2	4AB	Cadiata			21				MAI	Abegá	Manuel Pedro Pacavira		DES	Belito Socola	Abelardo Gomes Socola
3	4AB	Campos	António Campos Calei	1	24		73		PLS	Abel	Abel Lukango		DES	Ali	Abengea Mambani Ali
4	4AB	Chara ?			2	1			INT	Abianda 1	Yann Junior Abianda 1		DES	Dadão Bile	Adão Francisco Congo Zalata
5	4AB	Dany Ribeiro	Daniel Mendes Ribeiro	6	26	1	8		KAB	Abianda 2	Yann Junior Abianda 2		PRI	Tony Cabaça	Adão Joaquim Bango Cabaça
6	4AB	Dário Amorim	Dário Félix Amorim			3			LIB	Adilson Kivão	Adilson Ernesto Kivão		CAA	Maninho	Adelino Wima Calunhi António
7	4AB	Dudú Jack	Domingos Monteiro Jack	1	8	4	74		CAA	Adilson Manuel	Adilson Joaquim Manuel		SAG	Lelas 1	Adérito Yandelela Chissoca 1
8	4AB	Edgard	Edgard Arnaud Afane		18				ACA	Adó Pena	António Joaquim Barros Pena		MAI	Lelas 2	Adérito Yandelela Chissoca 2
9	4AB	Edi			1				ASA	Ady ?			INT	Neblú	Adilson Cipriano da Cruz
10	4AB	Geúda	Gelson L. da S. Teles		8	7			BEN	Ady Paulo	Agostinho Domingos Paulo		LIB	Adilson Kivão	Adilson Ernesto Kivão
11	4AB	Gui	Manuel Porfírio Pompílio de Matos		6				PLS	Agoya	Augusto Makanja		CAA	Adilson Manuel	Adilson Joaquim Manuel
12	4AB	Hippi	Domingos Paulo João José	1	15	1	75		ACA	Aires	Ludgero Aires Cachicote da Rocha		SAG	Denilson 1	Adolfo da Silva Catemba 1
13	4AB	Ivanilson				5			MAI	Alex	Alexandre G. Mário Miguel		POR	Denilson 2	Adolfo da Silva Catemba 2
14	4AB	Jairzinho	Jair Sebastião de Castro		8	5			POR	Alexandre	Alexandre Domingos Cristóvão M'Futila		PRO	Yano	Adriano Belmiro Duarte Nicolau
15	4AB	Jesse				3			DES	Ali	Abengea Mambani Ali		DES	Kumaca	Adriano da Costa Mateus Alberto
16	4AB	Kandú	Déo Kanda	3	14	9	27		PRO	Almeida	José Xavier Fernando		ASA	Fofó	Afonso Sebastião Cabungula
17	4AB	Kizombé	Alberto dos Santos Domingos		16	1			KAB	Amarildo 1	Amarildo Eugénio Augusto Miranda 1		INT	Mabululu	Agostinho Cristóvão Paciência
18	4AB	Lara ?				1			ASA	Amarildo 2	Amarildo Eugénio Augusto Miranda 2		BEN	Ady Paulo	Agostinho Domingos Paulo
19	4AB	Lelo	José Lelo Sevo Barros	1	14		76		BEN	Amaro	Amândio Manuel Filipe da Costa		KAB	Emilson	Aires Emilson Gonzaga Zeca
20	4AB	Manucho Muquengue	Mariano Simão Muquengue	2	15	11	46		BEN	Amido Baldé	Amido Baldé		MAI	Pai da Bola	Albano Camuele Sumano
21	4AB	Matamba	Matamba Paulino Sousa	1	27		77		PRO	Anastácio 1	Anastácio Manuel da Costa 1		PRI	Milambo	Albert Milambo-Mutamba
22	4AB	Paito	Manuel Gaspar Fernandes		1	5			ASA	Anastácio 2	Anastácio Manuel da Costa 2		PRI	Fissy	Alberto Álvaro Paca
23	4AB	Palucho	Paulo Pereira da Silva	1	6	13	78		BEN	Anderson			4AB	Kizombé	Alberto dos Santos Domingos
24	4AB	Pilola	José Olívio Andrade Pereira	3	11		28		INT	António	Tomás Chilombo Cambuque Sapalo		SAG	Rolli	Alentua Tangala Rolli
25	4AB	Prazeres	Prazeres Chijica M. Dala		3	1			SAG	Ary Calvete	Helmut Ariadne Figueiredo Calvete		POR	Alexandre	Alexandre Domingos Cristóvão M'Futila
26	4AB	Rochana	Panda Marcelo José		23				PET	Ary Oliveira	Ariclene Assunção Oliveira		MAI	Alex	Alexandre G. Mário Miguel
27	4AB	Rui ?			8	2			PRI	Ary Papel	Manuel David Afonso		DES	Chiwe	Alfredo Cassinda Calunganga
28	4AB	Tony Augusto	António Pedro Augusto		7				PLS	Atunako	Eboue Manzambi Atunako		LIB	Fredy	Alfredo Kulembe Ribeiro
29	4AB	Vander	Vander Ramos Pinto		19				PRO	Avex 1	Avelino Eduardo António Craque 1		ASA	Miro	Almiro Edson Daniel Lobo
30	4AB	Xavier	Xavier Eduardo Vicente Vunge	1	23		79		DES	Avex 2	Avelino Eduardo António Craque 2		BEN	Amaro	Amândio Manuel Filipe da Costa
31	4AB								SAG	Ayala	Mário Álvaro Agostinho		KAB	Amarildo 1	Amarildo Eugénio Augusto Miranda 1
32	4AB								PET	Azulão	Tiago Lima Leal		ASA	Amarildo 2	Amarildo Eugénio Augusto Miranda 2
33	4AB								ASA	Babacar	Babacar Fall		BEN	Amido Baldé	Amido Baldé
34	4AB								BEN	Baby	Valdemar Denso António		PRO	Anastácio 1	Anastácio Manuel da Costa 1
35	4AB								PLS	Badrick	Badrick Paulo Madadi		ASA	Anastácio 2	Anastácio Manuel da Costa 2
36	4AB								PET	Balacai	Evaristo Maurício Pascoal		ASA	Guelor	Anderson Benjamim Monteiro
37	4AB								PLS	Barrezó	Joveth Adão		BEN	Toy	André Augusto Miranda Kongo
38	4AB								INT	Bartolo	Bartolomeu Nguia Baptista		INT	Mfede	Anthony Kevin Mfede Junior
39	ACA	Adó Pena	António Joaquim Barros Pena	1	13	2	80		INT	Bebé 1	Odimir Abreu Gabriel Breganha 1		4AB	Campos	António Campos Calei
40	ACA	Aires	Ludgero Aires Cachicote da Rocha		1				ASA	Bebé 2	Odimir Abreu Gabriel Breganha 2		PRO	Silva Anato	António da Silva Anato
STATS2

Cell Formulas
Range	Formula
BZ1	BZ1	=INDIRECT("4AB!B5")
CA1	CA1	=INDIRECT("4AB!C5")
CB1	CB1	=INDIRECT("4AB!E5")
CC1	CC1	=INDIRECT("4AB!F5")
CD1	CD1	=INDIRECT("4AB!G5")
CE1:CE40	CE1	=IF($CB1=0,"",IF($CB1>0,RANK(CB1,$CB$1:$CB$608)+COUNTIF($CB$1:CB1,CB1)-1))
BZ2	BZ2	=INDIRECT("4AB!B6")
CA2	CA2	=INDIRECT("4AB!C6")
CB2	CB2	=INDIRECT("4AB!E6")
CC2	CC2	=INDIRECT("4AB!F6")
CD2	CD2	=INDIRECT("4AB!G6")
BZ3	BZ3	=INDIRECT("4AB!B7")
CA3	CA3	=INDIRECT("4AB!C7")
CB3	CB3	=INDIRECT("4AB!E7")
CC3	CC3	=INDIRECT("4AB!F7")
CD3	CD3	=INDIRECT("4AB!G7")
BZ4	BZ4	=INDIRECT("4AB!B8")
CA4	CA4	=INDIRECT("4AB!C8")
CB4	CB4	=INDIRECT("4AB!E8")
CC4	CC4	=INDIRECT("4AB!F8")
CD4	CD4	=INDIRECT("4AB!G8")
BZ5	BZ5	=INDIRECT("4AB!B9")
CA5	CA5	=INDIRECT("4AB!C9")
CB5	CB5	=INDIRECT("4AB!E9")
CC5	CC5	=INDIRECT("4AB!F9")
CD5	CD5	=INDIRECT("4AB!G9")
BZ6	BZ6	=INDIRECT("4AB!B10")
CA6	CA6	=INDIRECT("4AB!C10")
CB6	CB6	=INDIRECT("4AB!E10")
CC6	CC6	=INDIRECT("4AB!F10")
CD6	CD6	=INDIRECT("4AB!G10")
BZ7	BZ7	=INDIRECT("4AB!B11")
CA7	CA7	=INDIRECT("4AB!C11")
CB7	CB7	=INDIRECT("4AB!E11")
CC7	CC7	=INDIRECT("4AB!F11")
CD7	CD7	=INDIRECT("4AB!G11")
BZ8	BZ8	=INDIRECT("4AB!B12")
CA8	CA8	=INDIRECT("4AB!C12")
CB8	CB8	=INDIRECT("4AB!E12")
CC8	CC8	=INDIRECT("4AB!F12")
CD8	CD8	=INDIRECT("4AB!G12")
BZ9	BZ9	=INDIRECT("4AB!B13")
CA9	CA9	=INDIRECT("4AB!C13")
CB9	CB9	=INDIRECT("4AB!E13")
CC9	CC9	=INDIRECT("4AB!F13")
CD9	CD9	=INDIRECT("4AB!G13")
BZ10	BZ10	=INDIRECT("4AB!B14")
CA10	CA10	=INDIRECT("4AB!C14")
CB10	CB10	=INDIRECT("4AB!E14")
CC10	CC10	=INDIRECT("4AB!F14")
CD10	CD10	=INDIRECT("4AB!G14")
BZ11	BZ11	=INDIRECT("4AB!B15")
CA11	CA11	=INDIRECT("4AB!C15")
CB11	CB11	=INDIRECT("4AB!E15")
CC11	CC11	=INDIRECT("4AB!F15")
CD11	CD11	=INDIRECT("4AB!G15")
BZ12	BZ12	=INDIRECT("4AB!B16")
CA12	CA12	=INDIRECT("4AB!C16")
CB12	CB12	=INDIRECT("4AB!E16")
CC12	CC12	=INDIRECT("4AB!F16")
CD12	CD12	=INDIRECT("4AB!G16")
BZ13	BZ13	=INDIRECT("4AB!B17")
CA13	CA13	=INDIRECT("4AB!C17")
CB13	CB13	=INDIRECT("4AB!E17")
CC13	CC13	=INDIRECT("4AB!F17")
CD13	CD13	=INDIRECT("4AB!G17")
BZ14	BZ14	=INDIRECT("4AB!B18")
CA14	CA14	=INDIRECT("4AB!C18")
CB14	CB14	=INDIRECT("4AB!E18")
CC14	CC14	=INDIRECT("4AB!F18")
CD14	CD14	=INDIRECT("4AB!G18")
BZ15	BZ15	=INDIRECT("4AB!B19")
CA15	CA15	=INDIRECT("4AB!C19")
CB15	CB15	=INDIRECT("4AB!E19")
CC15	CC15	=INDIRECT("4AB!F19")
CD15	CD15	=INDIRECT("4AB!G19")
BZ16	BZ16	=INDIRECT("4AB!B20")
CA16	CA16	=INDIRECT("4AB!C20")
CB16	CB16	=INDIRECT("4AB!E20")
CC16	CC16	=INDIRECT("4AB!F20")
CD16	CD16	=INDIRECT("4AB!G20")
BZ17	BZ17	=INDIRECT("4AB!B21")
CA17	CA17	=INDIRECT("4AB!C21")
CB17	CB17	=INDIRECT("4AB!E21")
CC17	CC17	=INDIRECT("4AB!F21")
CD17	CD17	=INDIRECT("4AB!G21")
BZ18	BZ18	=INDIRECT("4AB!B22")
CA18	CA18	=INDIRECT("4AB!C22")
CB18	CB18	=INDIRECT("4AB!E22")
CC18	CC18	=INDIRECT("4AB!F22")
CD18	CD18	=INDIRECT("4AB!G22")
BZ19	BZ19	=INDIRECT("4AB!B23")
CA19	CA19	=INDIRECT("4AB!C23")
CB19	CB19	=INDIRECT("4AB!E23")
CC19	CC19	=INDIRECT("4AB!F23")
CD19	CD19	=INDIRECT("4AB!G23")
BZ20	BZ20	=INDIRECT("4AB!B24")
CA20	CA20	=INDIRECT("4AB!C24")
CB20	CB20	=INDIRECT("4AB!E24")
CC20	CC20	=INDIRECT("4AB!F24")
CD20	CD20	=INDIRECT("4AB!G24")
BZ21	BZ21	=INDIRECT("4AB!B25")
CA21	CA21	=INDIRECT("4AB!C25")
CB21	CB21	=INDIRECT("4AB!E25")
CC21	CC21	=INDIRECT("4AB!F25")
CD21	CD21	=INDIRECT("4AB!G25")
BZ22	BZ22	=INDIRECT("4AB!B26")
CA22	CA22	=INDIRECT("4AB!C26")
CB22	CB22	=INDIRECT("4AB!E26")
CC22	CC22	=INDIRECT("4AB!F26")
CD22	CD22	=INDIRECT("4AB!G26")
BZ23	BZ23	=INDIRECT("4AB!B27")
CA23	CA23	=INDIRECT("4AB!C27")
CB23	CB23	=INDIRECT("4AB!E27")
CC23	CC23	=INDIRECT("4AB!F27")
CD23	CD23	=INDIRECT("4AB!G27")
BZ24	BZ24	=INDIRECT("4AB!B28")
CA24	CA24	=INDIRECT("4AB!C28")
CB24	CB24	=INDIRECT("4AB!E28")
CC24	CC24	=INDIRECT("4AB!F28")
CD24	CD24	=INDIRECT("4AB!G28")
BZ25	BZ25	=INDIRECT("4AB!B29")
CA25	CA25	=INDIRECT("4AB!C29")
CB25	CB25	=INDIRECT("4AB!E29")
CC25	CC25	=INDIRECT("4AB!F29")
CD25	CD25	=INDIRECT("4AB!G29")
BZ26	BZ26	=INDIRECT("4AB!B30")
CA26	CA26	=INDIRECT("4AB!C30")
CB26	CB26	=INDIRECT("4AB!E30")
CC26	CC26	=INDIRECT("4AB!F30")
CD26	CD26	=INDIRECT("4AB!G30")
BZ27	BZ27	=INDIRECT("4AB!B31")
CA27	CA27	=INDIRECT("4AB!C31")
CB27	CB27	=INDIRECT("4AB!E31")
CC27	CC27	=INDIRECT("4AB!F31")
CD27	CD27	=INDIRECT("4AB!G31")
BZ28	BZ28	=INDIRECT("4AB!B32")
CA28	CA28	=INDIRECT("4AB!C32")
CB28	CB28	=INDIRECT("4AB!E32")
CC28	CC28	=INDIRECT("4AB!F32")
CD28	CD28	=INDIRECT("4AB!G32")
BZ29	BZ29	=INDIRECT("4AB!B33")
CA29	CA29	=INDIRECT("4AB!C33")
CB29	CB29	=INDIRECT("4AB!E33")
CC29	CC29	=INDIRECT("4AB!F33")
CD29	CD29	=INDIRECT("4AB!G33")
BZ30	BZ30	=INDIRECT("4AB!B34")
CA30	CA30	=INDIRECT("4AB!C34")
CB30	CB30	=INDIRECT("4AB!E34")
CC30	CC30	=INDIRECT("4AB!F34")
CD30	CD30	=INDIRECT("4AB!G34")
BZ31	BZ31	=INDIRECT("4AB!B35")
CA31	CA31	=INDIRECT("4AB!C35")
CB31	CB31	=INDIRECT("4AB!E35")
CC31	CC31	=INDIRECT("4AB!F35")
CD31	CD31	=INDIRECT("4AB!G35")
BZ32	BZ32	=INDIRECT("4AB!B36")
CA32	CA32	=INDIRECT("4AB!C36")
CB32	CB32	=INDIRECT("4AB!E36")
CC32	CC32	=INDIRECT("4AB!F36")
CD32	CD32	=INDIRECT("4AB!G36")
BZ33	BZ33	=INDIRECT("4AB!B37")
CA33	CA33	=INDIRECT("4AB!C37")
CB33	CB33	=INDIRECT("4AB!E37")
CC33	CC33	=INDIRECT("4AB!F37")
CD33	CD33	=INDIRECT("4AB!G37")
BZ34	BZ34	=INDIRECT("4AB!B38")
CA34	CA34	=INDIRECT("4AB!C38")
CB34	CB34	=INDIRECT("4AB!E38")
CC34	CC34	=INDIRECT("4AB!F38")
CD34	CD34	=INDIRECT("4AB!G38")
BZ35	BZ35	=INDIRECT("4AB!B39")
CA35	CA35	=INDIRECT("4AB!C39")
CB35	CB35	=INDIRECT("4AB!E39")
CC35	CC35	=INDIRECT("4AB!F39")
CD35	CD35	=INDIRECT("4AB!G39")
BZ36	BZ36	=INDIRECT("4AB!B40")
CA36	CA36	=INDIRECT("4AB!C40")
CB36	CB36	=INDIRECT("4AB!E40")
CC36	CC36	=INDIRECT("4AB!F40")
CD36	CD36	=INDIRECT("4AB!G40")
BZ37	BZ37	=INDIRECT("4AB!B41")
CA37	CA37	=INDIRECT("4AB!C41")
CB37	CB37	=INDIRECT("4AB!E41")
CC37	CC37	=INDIRECT("4AB!F41")
CD37	CD37	=INDIRECT("4AB!G41")
BZ38	BZ38	=INDIRECT("4AB!B42")
CA38	CA38	=INDIRECT("4AB!C42")
CB38	CB38	=INDIRECT("4AB!E42")
CC38	CC38	=INDIRECT("4AB!F42")
CD38	CD38	=INDIRECT("4AB!G42")
BZ39	BZ39	=INDIRECT("ACA!B5")
CA39	CA39	=INDIRECT("ACA!C5")
CB39	CB39	=INDIRECT("ACA!E5")
CC39	CC39	=INDIRECT("ACA!F5")
CD39	CD39	=INDIRECT("ACA!G5")
BZ40	BZ40	=INDIRECT("ACA!B6")
CA40	CA40	=INDIRECT("ACA!C6")
CB40	CB40	=INDIRECT("ACA!E6")
CC40	CC40	=INDIRECT("ACA!F6")
CD40	CD40	=INDIRECT("ACA!G6")
CG1:CI501	CG1	=SORT(FILTER(BY1:CA608,BZ1:BZ608<>0),2,1)
CK1:CM478	CK1	=SORT(FILTER(BY1:CA608,CA1:CA608<>0),3,1)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
BY1	Expression	=BZ1=0	text	NO
BZ2:BZ608	Expression	=CA2=0	text	NO
BZ1	Expression	=CA1=0	text	NO
CA1:CA608	Cell Value	=0	text	NO
CI1:CI608	Cell Value	=0	text	NO
CH1:CH608	Expression	=CI1=0	text	NO
CB1:CD608	Cell Value	=0	text	NO

 

[Peter_SSs]

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.

 

[valmir]

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.

Stats 2016.xlsx
	BY	BZ	CA	CB	CC	CD													CQ	CR	CS	CT
1	4AB	Borra		4AB		Borra														4AB		Borra
2	4AB	Cadiata		4AB		Cadiata														4AB		Cadiata
3	4AB	Campos	António Campos Calei	4AB	António Campos Calei	Campos														4AB	António Campos Calei	Campos
4	4AB	Chara ?		4AB		Chara ?														4AB		Chara ?
5	4AB	Dany Ribeiro	Daniel Mendes Ribeiro	4AB	Daniel Mendes Ribeiro	Dany Ribeiro														4AB	Daniel Mendes Ribeiro	Dany Ribeiro
6	4AB	Dário Amorim	Dário Félix Amorim	4AB	Dário Félix Amorim	Dário Amorim														4AB	Dário Félix Amorim	Dário Amorim
7	4AB	Dudú Jack	Domingos Monteiro Jack	4AB	Domingos Monteiro Jack	Dudú Jack														4AB	Domingos Monteiro Jack	Dudú Jack
8	4AB	Edgard	Edgard Arnaud Afane	4AB	Edgard Arnaud Afane	Edgard														4AB	Edgard Arnaud Afane	Edgard
9	4AB	Edi		4AB		Edi														4AB		Edi
10	4AB	Geúda	Gelson L. da S. Teles	4AB	Gelson L. da S. Teles	Geúda														4AB	Gelson L. da S. Teles	Geúda
11	4AB	Gui	Manuel Porfírio Pompílio de Matos	4AB	Manuel Porfírio Pompílio de Matos	Gui														4AB	Manuel Porfírio Pompílio de Matos	Gui
12	4AB	Hippi	Domingos Paulo João José	4AB	Domingos Paulo João José	Hippi														4AB	Domingos Paulo João José	Hippi
13	4AB	Ivanilson		4AB		Ivanilson														4AB		Ivanilson
14	4AB	Jairzinho	Jair Sebastião de Castro	4AB	Jair Sebastião de Castro	Jairzinho														4AB	Jair Sebastião de Castro	Jairzinho
15	4AB	Jesse		4AB		Jesse														4AB		Jesse
STATS2

Cell Formulas
Range	Formula
BZ1	BZ1	=INDIRECT("4AB!B5")
CA1	CA1	=INDIRECT("4AB!C5")
CB1	CB1	=INDIRECT("BY1")
CC1	CC1	=INDIRECT("CA1")
CD1	CD1	=INDIRECT("BZ1")
BZ2	BZ2	=INDIRECT("4AB!B6")
CA2	CA2	=INDIRECT("4AB!C6")
CB2	CB2	=INDIRECT("BY2")
CC2	CC2	=INDIRECT("CA2")
CD2	CD2	=INDIRECT("BZ2")
BZ3	BZ3	=INDIRECT("4AB!B7")
CA3	CA3	=INDIRECT("4AB!C7")
CB3	CB3	=INDIRECT("BY3")
CC3	CC3	=INDIRECT("CA3")
CD3	CD3	=INDIRECT("BZ3")
BZ4	BZ4	=INDIRECT("4AB!B8")
CA4	CA4	=INDIRECT("4AB!C8")
CB4	CB4	=INDIRECT("BY4")
CC4	CC4	=INDIRECT("CA4")
CD4	CD4	=INDIRECT("BZ4")
BZ5	BZ5	=INDIRECT("4AB!B9")
CA5	CA5	=INDIRECT("4AB!C9")
CB5	CB5	=INDIRECT("BY5")
CC5	CC5	=INDIRECT("CA5")
CD5	CD5	=INDIRECT("BZ5")
BZ6	BZ6	=INDIRECT("4AB!B10")
CA6	CA6	=INDIRECT("4AB!C10")
CB6	CB6	=INDIRECT("BY6")
CC6	CC6	=INDIRECT("CA6")
CD6	CD6	=INDIRECT("BZ6")
BZ7	BZ7	=INDIRECT("4AB!B11")
CA7	CA7	=INDIRECT("4AB!C11")
CB7	CB7	=INDIRECT("BY7")
CC7	CC7	=INDIRECT("CA7")
CD7	CD7	=INDIRECT("BZ7")
BZ8	BZ8	=INDIRECT("4AB!B12")
CA8	CA8	=INDIRECT("4AB!C12")
CB8	CB8	=INDIRECT("BY8")
CC8	CC8	=INDIRECT("CA8")
CD8	CD8	=INDIRECT("BZ8")
BZ9	BZ9	=INDIRECT("4AB!B13")
CA9	CA9	=INDIRECT("4AB!C13")
CB9	CB9	=INDIRECT("BY9")
CC9	CC9	=INDIRECT("CA9")
CD9	CD9	=INDIRECT("BZ9")
BZ10	BZ10	=INDIRECT("4AB!B14")
CA10	CA10	=INDIRECT("4AB!C14")
CB10	CB10	=INDIRECT("BY10")
CC10	CC10	=INDIRECT("CA10")
CD10	CD10	=INDIRECT("BZ10")
BZ11	BZ11	=INDIRECT("4AB!B15")
CA11	CA11	=INDIRECT("4AB!C15")
CB11	CB11	=INDIRECT("BY11")
CC11	CC11	=INDIRECT("CA11")
CD11	CD11	=INDIRECT("BZ11")
BZ12	BZ12	=INDIRECT("4AB!B16")
CA12	CA12	=INDIRECT("4AB!C16")
CB12	CB12	=INDIRECT("BY12")
CC12	CC12	=INDIRECT("CA12")
CD12	CD12	=INDIRECT("BZ12")
BZ13	BZ13	=INDIRECT("4AB!B17")
CA13	CA13	=INDIRECT("4AB!C17")
CB13	CB13	=INDIRECT("BY13")
CC13	CC13	=INDIRECT("CA13")
CD13	CD13	=INDIRECT("BZ13")
BZ14	BZ14	=INDIRECT("4AB!B18")
CA14	CA14	=INDIRECT("4AB!C18")
CB14	CB14	=INDIRECT("BY14")
CC14	CC14	=INDIRECT("CA14")
CD14	CD14	=INDIRECT("BZ14")
BZ15	BZ15	=INDIRECT("4AB!B19")
CA15	CA15	=INDIRECT("4AB!C19")
CB15	CB15	=INDIRECT("BY15")
CC15	CC15	=INDIRECT("CA15")
CD15	CD15	=INDIRECT("BZ15")
CR1:CT608	CR1	=SORT(FILTER(CB1:CD608,CB1:CB608<>0),1,1)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
CR1:CT608	Cell Value	=0	text	NO
CD2:CD608	Expression	=CE2=0	text	NO
CD1	Expression	=CE1=0	text	NO
BY1	Expression	=BZ1=0	text	NO
BZ2:BZ608	Expression	=CA2=0	text	NO
BZ1	Expression	=CA1=0	text	NO
CA1:CA608	Cell Value	=0	text	NO
B12,CC1:CC607	Cell Value	=0	text	NO

 

[valmir]

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]

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:

 

[valmir]

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.

Stats 2016.xlsx
	CB	CC	CD					CI
1	4AB		Borra
2	4AB		Cadiata
3	4AB	António Campos Calei	Campos
4	4AB		Chara ?
5	4AB	Daniel Mendes Ribeiro	Dany Ribeiro
6	4AB	Dário Félix Amorim	Dário Amorim
7	4AB	Domingos Monteiro Jack	Dudú Jack
8	4AB	Edgard Arnaud Afane	Edgard
9	4AB		Edi
10	4AB	Gelson L. da S. Teles	Geúda
11	4AB	Manuel Porfírio Pompílio de Matos	Gui
12	4AB	Domingos Paulo João José	Hippi
13	4AB		Ivanilson
14	4AB	Jair Sebastião de Castro	Jairzinho
15	4AB		Jesse
16	4AB	Déo Kanda	Kandú
17	4AB	Alberto dos Santos Domingos	Kizombé
18	4AB		Lara ?
19	4AB	José Lelo Sevo Barros	Lelo
20	4AB	Mariano Simão Muquengue	Manucho Muquengue
21	4AB	Matamba Paulino Sousa	Matamba
22	4AB	Manuel Gaspar Fernandes	Paito
23	4AB	Paulo Pereira da Silva	Palucho
24	4AB	José Olívio Andrade Pereira	Pilola
25	4AB	Prazeres Chijica M. Dala	Prazeres
26	4AB	Panda Marcelo José	Rochana
27	4AB		Rui ?
28	4AB	António Pedro Augusto	Tony Augusto
29	4AB	Vander Ramos Pinto	Vander
30	4AB	Xavier Eduardo Vicente Vunge	Xavier
31	4AB
32	4AB
33	4AB
34	4AB
35	4AB
36	4AB
37	4AB
38	4AB
39	ACA	António Joaquim Barros Pena	Adó Pena
40	ACA	Ludgero Aires Cachicote da Rocha	Aires
41	ACA	Cláudio Ricardo Cunha Borges 1	Borges 1
42	ACA	Mário Rui de Abreu 1	Cabibi Abreu 1
STATS2

Cell Formulas
Range	Formula
CB1	CB1	=INDIRECT("BY1")
CC1	CC1	=INDIRECT("CA1")
CD1	CD1	=INDIRECT("BZ1")
CB2	CB2	=INDIRECT("BY2")
CC2	CC2	=INDIRECT("CA2")
CD2	CD2	=INDIRECT("BZ2")
CB3	CB3	=INDIRECT("BY3")
CC3	CC3	=INDIRECT("CA3")
CD3	CD3	=INDIRECT("BZ3")
CB4	CB4	=INDIRECT("BY4")
CC4	CC4	=INDIRECT("CA4")
CD4	CD4	=INDIRECT("BZ4")
CB5	CB5	=INDIRECT("BY5")
CC5	CC5	=INDIRECT("CA5")
CD5	CD5	=INDIRECT("BZ5")
CB6	CB6	=INDIRECT("BY6")
CC6	CC6	=INDIRECT("CA6")
CD6	CD6	=INDIRECT("BZ6")
CB7	CB7	=INDIRECT("BY7")
CC7	CC7	=INDIRECT("CA7")
CD7	CD7	=INDIRECT("BZ7")
CB8	CB8	=INDIRECT("BY8")
CC8	CC8	=INDIRECT("CA8")
CD8	CD8	=INDIRECT("BZ8")
CB9	CB9	=INDIRECT("BY9")
CC9	CC9	=INDIRECT("CA9")
CD9	CD9	=INDIRECT("BZ9")
CB10	CB10	=INDIRECT("BY10")
CC10	CC10	=INDIRECT("CA10")
CD10	CD10	=INDIRECT("BZ10")
CB11	CB11	=INDIRECT("BY11")
CC11	CC11	=INDIRECT("CA11")
CD11	CD11	=INDIRECT("BZ11")
CB12	CB12	=INDIRECT("BY12")
CC12	CC12	=INDIRECT("CA12")
CD12	CD12	=INDIRECT("BZ12")
CB13	CB13	=INDIRECT("BY13")
CC13	CC13	=INDIRECT("CA13")
CD13	CD13	=INDIRECT("BZ13")
CB14	CB14	=INDIRECT("BY14")
CC14	CC14	=INDIRECT("CA14")
CD14	CD14	=INDIRECT("BZ14")
CB15	CB15	=INDIRECT("BY15")
CC15	CC15	=INDIRECT("CA15")
CD15	CD15	=INDIRECT("BZ15")
CB16	CB16	=INDIRECT("BY16")
CC16	CC16	=INDIRECT("CA16")
CD16	CD16	=INDIRECT("BZ16")
CB17	CB17	=INDIRECT("BY17")
CC17	CC17	=INDIRECT("CA17")
CD17	CD17	=INDIRECT("BZ17")
CB18	CB18	=INDIRECT("BY18")
CC18	CC18	=INDIRECT("CA18")
CD18	CD18	=INDIRECT("BZ18")
CB19	CB19	=INDIRECT("BY19")
CC19	CC19	=INDIRECT("CA19")
CD19	CD19	=INDIRECT("BZ19")
CB20	CB20	=INDIRECT("BY20")
CC20	CC20	=INDIRECT("CA20")
CD20	CD20	=INDIRECT("BZ20")
CB21	CB21	=INDIRECT("BY21")
CC21	CC21	=INDIRECT("CA21")
CD21	CD21	=INDIRECT("BZ21")
CB22	CB22	=INDIRECT("BY22")
CC22	CC22	=INDIRECT("CA22")
CD22	CD22	=INDIRECT("BZ22")
CB23	CB23	=INDIRECT("BY23")
CC23	CC23	=INDIRECT("CA23")
CD23	CD23	=INDIRECT("BZ23")
CB24	CB24	=INDIRECT("BY24")
CC24	CC24	=INDIRECT("CA24")
CD24	CD24	=INDIRECT("BZ24")
CB25	CB25	=INDIRECT("BY25")
CC25	CC25	=INDIRECT("CA25")
CD25	CD25	=INDIRECT("BZ25")
CB26	CB26	=INDIRECT("BY26")
CC26	CC26	=INDIRECT("CA26")
CD26	CD26	=INDIRECT("BZ26")
CB27	CB27	=INDIRECT("BY27")
CC27	CC27	=INDIRECT("CA27")
CD27	CD27	=INDIRECT("BZ27")
CB28	CB28	=INDIRECT("BY28")
CC28	CC28	=INDIRECT("CA28")
CD28	CD28	=INDIRECT("BZ28")
CB29	CB29	=INDIRECT("BY29")
CC29	CC29	=INDIRECT("CA29")
CD29	CD29	=INDIRECT("BZ29")
CB30	CB30	=INDIRECT("BY30")
CC30	CC30	=INDIRECT("CA30")
CD30	CD30	=INDIRECT("BZ30")
CB31	CB31	=INDIRECT("BY31")
CC31	CC31	=INDIRECT("CA31")
CD31	CD31	=INDIRECT("BZ31")
CB32	CB32	=INDIRECT("BY32")
CC32	CC32	=INDIRECT("CA32")
CD32	CD32	=INDIRECT("BZ32")
CB33	CB33	=INDIRECT("BY33")
CC33	CC33	=INDIRECT("CA33")
CD33	CD33	=INDIRECT("BZ33")
CB34	CB34	=INDIRECT("BY34")
CC34	CC34	=INDIRECT("CA34")
CD34	CD34	=INDIRECT("BZ34")
CB35	CB35	=INDIRECT("BY35")
CC35	CC35	=INDIRECT("CA35")
CD35	CD35	=INDIRECT("BZ35")
CB36	CB36	=INDIRECT("BY36")
CC36	CC36	=INDIRECT("CA36")
CD36	CD36	=INDIRECT("BZ36")
CB37	CB37	=INDIRECT("BY37")
CC37	CC37	=INDIRECT("CA37")
CD37	CD37	=INDIRECT("BZ37")
CB38	CB38	=INDIRECT("BY38")
CC38	CC38	=INDIRECT("CA38")
CD38	CD38	=INDIRECT("BZ38")
CB39	CB39	=INDIRECT("BY39")
CC39	CC39	=INDIRECT("CA39")
CD39	CD39	=INDIRECT("BZ39")
CB40	CB40	=INDIRECT("BY40")
CC40	CC40	=INDIRECT("CA40")
CD40	CD40	=INDIRECT("BZ40")
CB41	CB41	=INDIRECT("BY41")
CC41	CC41	=INDIRECT("CA41")
CD41	CD41	=INDIRECT("BZ41")
CB42	CB42	=INDIRECT("BY42")
CC42	CC42	=INDIRECT("CA42")
CD42	CD42	=INDIRECT("BZ42")

 

[Peter_SSs]

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]

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
	CI	CJ	CK	CL	CM	CN
1	4AB	0	Borra	4AB	Alberto dos Santos Domingos	Kizombé
2	4AB	0	Cadiata	4AB	António Campos Calei	Campos
3	4AB	António Campos Calei	Campos	4AB	António Pedro Augusto	Tony Augusto
4	4AB	0	Chara ?	4AB	Daniel Mendes Ribeiro	Dany Ribeiro
5	4AB	Daniel Mendes Ribeiro	Dany Ribeiro	4AB	Dário Félix Amorim	Dário Amorim
6	4AB	Dário Félix Amorim	Dário Amorim	4AB	Déo Kanda	Kandú
7	4AB	Domingos Monteiro Jack	Dudú Jack	4AB	Domingos Monteiro Jack	Dudú Jack
8	4AB	Edgard Arnaud Afane	Edgard	4AB	Domingos Paulo João José	Hippi
9	4AB	0	Edi	4AB	Edgard Arnaud Afane	Edgard
10	4AB	Gelson L. da S. Teles	Geúda	4AB	Gelson L. da S. Teles	Geúda
11	4AB	Manuel Porfírio Pompílio de Matos	Gui	4AB	Jair Sebastião de Castro	Jairzinho
12	4AB	Domingos Paulo João José	Hippi	4AB	José Lelo Sevo Barros	Lelo
13	4AB	0	Ivanilson	4AB	José Olívio Andrade Pereira	Pilola
14	4AB	Jair Sebastião de Castro	Jairzinho	4AB	Manuel Gaspar Fernandes	Paito
15	4AB	0	Jesse	4AB	Manuel Porfírio Pompílio de Matos	Gui
16	4AB	Déo Kanda	Kandú	4AB	Mariano Simão Muquengue	Manucho Muquengue
17	4AB	Alberto dos Santos Domingos	Kizombé	4AB	Matamba Paulino Sousa	Matamba
18	4AB	0	Lara ?	4AB	Panda Marcelo José	Rochana
19	4AB	José Lelo Sevo Barros	Lelo	4AB	Paulo Pereira da Silva	Palucho
20	4AB	Mariano Simão Muquengue	Manucho Muquengue	4AB	Prazeres Chijica M. Dala	Prazeres
21	4AB	Matamba Paulino Sousa	Matamba	4AB	Vander Ramos Pinto	Vander
22	4AB	Manuel Gaspar Fernandes	Paito	4AB	Xavier Eduardo Vicente Vunge	Xavier
23	4AB	Paulo Pereira da Silva	Palucho	ACA	António Joaquim Barros Pena	Adó Pena
24	4AB	José Olívio Andrade Pereira	Pilola	ACA	Cláudio Américo Loi Sozinho	Cláudio
25	4AB	Prazeres Chijica M. Dala	Prazeres	ACA	Cláudio Ricardo Cunha Borges 1	Borges 1
26	4AB	Panda Marcelo José	Rochana	ACA	Domingos Zaya Nunes	Gância
27	4AB	0	Rui ?	ACA	Elton Ernesto de Carvalho	Elton
28	4AB	António Pedro Augusto	Tony Augusto	ACA	Evaristo Daniel Kapunge	Lito Kapunge
29	4AB	Vander Ramos Pinto	Vander	ACA	Ezequiel Paulo Julião	Higino
30	4AB	Xavier Eduardo Vicente Vunge	Xavier	ACA	Filipe Tomás João Tchitungo	Filipe Tchitungo
31	4AB	0	0	ACA	Francisco Ananias Orlando	Chiló
32	4AB	0	0	ACA	Gaspar Necas Fortunato	Chabalala
33	4AB	0	0	ACA	Ibraime Barreto Cassamá	Ibraime
34	4AB	0	0	ACA	Jorge Paulo Lima Alves	Kadú
35	4AB	0	0
36	4AB	0	0
37	4AB	0	0
38	4AB	0	0
39	ACA	António Joaquim Barros Pena	Adó Pena
40	ACA	Ludgero Aires Cachicote da Rocha	Aires
41	ACA	Cláudio Ricardo Cunha Borges 1	Borges 1
STATS2

 

[Peter_SSs]

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
	CI	CJ	CK	CL	CM	CN
1	4AB	0	Borra	4AB	Alberto dos Santos Domingos	Kizombé
2	4AB	0	Cadiata	4AB	António Campos Calei	Campos
3	4AB	António Campos Calei	Campos	4AB	António Pedro Augusto	Tony Augusto
4	4AB	0	Chara ?	4AB	Daniel Mendes Ribeiro	Dany Ribeiro
5	4AB	Daniel Mendes Ribeiro	Dany Ribeiro	4AB	Dário Félix Amorim	Dário Amorim
6	4AB	Dário Félix Amorim	Dário Amorim	4AB	Déo Kanda	Kandú
7	4AB	Domingos Monteiro Jack	Dudú Jack	4AB	Domingos Monteiro Jack	Dudú Jack
8	4AB	Edgard Arnaud Afane	Edgard	4AB	Domingos Paulo João José	Hippi
9	4AB	0	Edi	4AB	Edgard Arnaud Afane	Edgard
10	4AB	Gelson L. da S. Teles	Geúda	4AB	Gelson L. da S. Teles	Geúda
11	4AB	Manuel Porfírio Pompílio de Matos	Gui	4AB	Jair Sebastião de Castro	Jairzinho
12	4AB	Domingos Paulo João José	Hippi	4AB	José Lelo Sevo Barros	Lelo
13	4AB	0	Ivanilson	4AB	José Olívio Andrade Pereira	Pilola
14	4AB	Jair Sebastião de Castro	Jairzinho	4AB	Manuel Gaspar Fernandes	Paito
15	4AB	0	Jesse	4AB	Manuel Porfírio Pompílio de Matos	Gui
16	4AB	Déo Kanda	Kandú	4AB	Mariano Simão Muquengue	Manucho Muquengue
17	4AB	Alberto dos Santos Domingos	Kizombé	4AB	Matamba Paulino Sousa	Matamba
18	4AB	0	Lara ?	4AB	Panda Marcelo José	Rochana
19	4AB	José Lelo Sevo Barros	Lelo	4AB	Paulo Pereira da Silva	Palucho
20	4AB	Mariano Simão Muquengue	Manucho Muquengue	4AB	Prazeres Chijica M. Dala	Prazeres
21	4AB	Matamba Paulino Sousa	Matamba	4AB	Vander Ramos Pinto	Vander
22	4AB	Manuel Gaspar Fernandes	Paito	4AB	Xavier Eduardo Vicente Vunge	Xavier
23	4AB	Paulo Pereira da Silva	Palucho	ACA	António Joaquim Barros Pena	Adó Pena
24	4AB	José Olívio Andrade Pereira	Pilola	ACA	Cláudio Ricardo Cunha Borges 1	Borges 1
25	4AB	Prazeres Chijica M. Dala	Prazeres	ACA	Ludgero Aires Cachicote da Rocha	Aires
26	4AB	Panda Marcelo José	Rochana
27	4AB	0	Rui ?
28	4AB	António Pedro Augusto	Tony Augusto
29	4AB	Vander Ramos Pinto	Vander
30	4AB	Xavier Eduardo Vicente Vunge	Xavier
31	4AB	0	0
32	4AB	0	0
33	4AB	0	0
34	4AB	0	0
35	4AB	0	0
36	4AB	0	0
37	4AB	0	0
38	4AB	0	0
39	ACA	António Joaquim Barros Pena	Adó Pena
40	ACA	Ludgero Aires Cachicote da Rocha	Aires
41	ACA	Cláudio Ricardo Cunha Borges 1	Borges 1
Sheet2

Cell Formulas
Range	Formula
CL1:CN25	CL1	=SORT(FILTER(CI1:CK41,CJ1:CJ41<>0),{1,2})
Dynamic array formulas.

 

[valmir]

=SORT(FILTER(CI1:CK41,CJ1:CJ41<>0),{1,2})

Yes sir. It worked perfectly. Thank you so much for your time and patience! ??

 

[Peter_SSs]

You are welcome. Glad we got there in the end.