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]

Is there really no headings on the data?

Do you have the LET() function?

 

[valmir]

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]

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.

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

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

Cell Formulas
Range	Formula
CK1:CM24	CK1	=SORTBY(SORT(FILTER(BY1:CA608,CA1:CA608<>0),3,1),{1,3,2})
Dynamic array formulas.

 

[valmir]

=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

 

[Peter_SSs]

I had to switch colons to semi-colons

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?

 

[valmir]

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]

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]

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
	BY	BZ	CA				CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT
1	4AB	Borra						3	3			PET	Abdul	António Nzayinawo		PLS	Abel Lukango	Abel		4AB		Borra
2	4AB	Cadiata						21				MAI	Abegá	Manuel Pedro Pacavira		DES	Abelardo Gomes Socola	Belito Socola		4AB		Cadiata
3	4AB	Campos	António Campos Calei				1	24		73		PLS	Abel	Abel Lukango		DES	Abengea Mambani Ali	Ali		4AB	António Campos Calei	Campos
4	4AB	Chara ?						2	1			INT	Abianda 1	Yann Junior Abianda 1		DES	Adão Francisco Congo Zalata	Dadão Bile		4AB		Chara ?
5	4AB	Dany Ribeiro	Daniel Mendes Ribeiro				6	26	1	8		KAB	Abianda 2	Yann Junior Abianda 2		PRI	Adão Joaquim Bango Cabaça	Tony Cabaça		4AB	Daniel Mendes Ribeiro	Dany Ribeiro
6	4AB	Dário Amorim	Dário Félix Amorim						3			LIB	Adilson Kivão	Adilson Ernesto Kivão		CAA	Adelino Wima Calunhi António	Maninho		4AB	Dário Félix Amorim	Dário Amorim
7	4AB	Dudú Jack	Domingos Monteiro Jack				1	8	4	74		CAA	Adilson Manuel	Adilson Joaquim Manuel		SAG	Adérito Yandelela Chissoca 1	Lelas 1		4AB	Domingos Monteiro Jack	Dudú Jack
8	4AB	Edgard	Edgard Arnaud Afane					18				ACA	Adó Pena	António Joaquim Barros Pena		MAI	Adérito Yandelela Chissoca 2	Lelas 2		4AB	Edgard Arnaud Afane	Edgard
9	4AB	Edi						1				ASA	Ady ?			INT	Adilson Cipriano da Cruz	Neblú		4AB		Edi
10	4AB	Geúda	Gelson L. da S. Teles					8	7			BEN	Ady Paulo	Agostinho Domingos Paulo		LIB	Adilson Ernesto Kivão	Adilson Kivão		4AB	Gelson L. da S. Teles	Geúda
11	4AB	Gui	Manuel Porfírio Pompílio de Matos					6				PLS	Agoya	Augusto Makanja		CAA	Adilson Joaquim Manuel	Adilson Manuel		4AB	Manuel Porfírio Pompílio de Matos	Gui
12	4AB	Hippi	Domingos Paulo João José				1	15	1	75		ACA	Aires	Ludgero Aires Cachicote da Rocha		SAG	Adolfo da Silva Catemba 1	Denilson 1		4AB	Domingos Paulo João José	Hippi
13	4AB	Ivanilson							5			MAI	Alex	Alexandre G. Mário Miguel		POR	Adolfo da Silva Catemba 2	Denilson 2		4AB		Ivanilson
14	4AB	Jairzinho	Jair Sebastião de Castro					8	5			POR	Alexandre	Alexandre Domingos Cristóvão M'Futila		PRO	Adriano Belmiro Duarte Nicolau	Yano		4AB	Jair Sebastião de Castro	Jairzinho
15	4AB	Jesse							3			DES	Ali	Abengea Mambani Ali		DES	Adriano da Costa Mateus Alberto	Kumaca		4AB		Jesse
16	4AB	Kandú	Déo Kanda				3	14	9	27		PRO	Almeida	José Xavier Fernando		ASA	Afonso Sebastião Cabungula	Fofó		4AB	Déo Kanda	Kandú
17	4AB	Kizombé	Alberto dos Santos Domingos					16	1			KAB	Amarildo 1	Amarildo Eugénio Augusto Miranda 1		INT	Agostinho Cristóvão Paciência	Mabululu		4AB	Alberto dos Santos Domingos	Kizombé
18	4AB	Lara ?							1			ASA	Amarildo 2	Amarildo Eugénio Augusto Miranda 2		BEN	Agostinho Domingos Paulo	Ady Paulo		4AB		Lara ?
19	4AB	Lelo	José Lelo Sevo Barros				1	14		76		BEN	Amaro	Amândio Manuel Filipe da Costa		KAB	Aires Emilson Gonzaga Zeca	Emilson		4AB	José Lelo Sevo Barros	Lelo
20	4AB	Manucho Muquengue	Mariano Simão Muquengue				2	15	11	46		BEN	Amido Baldé	Amido Baldé		MAI	Albano Camuele Sumano	Pai da Bola		4AB	Mariano Simão Muquengue	Manucho Muquengue
21	4AB	Matamba	Matamba Paulino Sousa				1	27		77		PRO	Anastácio 1	Anastácio Manuel da Costa 1		PRI	Albert Milambo-Mutamba	Milambo		4AB	Matamba Paulino Sousa	Matamba
22	4AB	Paito	Manuel Gaspar Fernandes					1	5			ASA	Anastácio 2	Anastácio Manuel da Costa 2		PRI	Alberto Álvaro Paca	Fissy		4AB	Manuel Gaspar Fernandes	Paito
23	4AB	Palucho	Paulo Pereira da Silva				1	6	13	78		BEN	Anderson			4AB	Alberto dos Santos Domingos	Kizombé		4AB	Paulo Pereira da Silva	Palucho
24	4AB	Pilola	José Olívio Andrade Pereira				3	11		28		INT	António	Tomás Chilombo Cambuque Sapalo		SAG	Alentua Tangala Rolli	Rolli		4AB	José Olívio Andrade Pereira	Pilola
25	4AB	Prazeres	Prazeres Chijica M. Dala					3	1			SAG	Ary Calvete	Helmut Ariadne Figueiredo Calvete		POR	Alexandre Domingos Cristóvão M'Futila	Alexandre		4AB	Prazeres Chijica M. Dala	Prazeres
26	4AB	Rochana	Panda Marcelo José					23				PET	Ary Oliveira	Ariclene Assunção Oliveira		MAI	Alexandre G. Mário Miguel	Alex		4AB	Panda Marcelo José	Rochana
27	4AB	Rui ?						8	2			PRI	Ary Papel	Manuel David Afonso		DES	Alfredo Cassinda Calunganga	Chiwe		4AB		Rui ?
28	4AB	Tony Augusto	António Pedro Augusto					7				PLS	Atunako	Eboue Manzambi Atunako		LIB	Alfredo Kulembe Ribeiro	Fredy		4AB	António Pedro Augusto	Tony Augusto
29	4AB	Vander	Vander Ramos Pinto					19				PRO	Avex 1	Avelino Eduardo António Craque 1		ASA	Almiro Edson Daniel Lobo	Miro		4AB	Vander Ramos Pinto	Vander
30	4AB	Xavier	Xavier Eduardo Vicente Vunge				1	23		79		DES	Avex 2	Avelino Eduardo António Craque 2		BEN	Amândio Manuel Filipe da Costa	Amaro		4AB	Xavier Eduardo Vicente Vunge	Xavier
31	4AB											SAG	Ayala	Mário Álvaro Agostinho		KAB	Amarildo Eugénio Augusto Miranda 1	Amarildo 1		4AB
32	4AB											PET	Azulão	Tiago Lima Leal		ASA	Amarildo Eugénio Augusto Miranda 2	Amarildo 2		4AB
33	4AB											ASA	Babacar	Babacar Fall		BEN	Amido Baldé	Amido Baldé		4AB
34	4AB											BEN	Baby	Valdemar Denso António		PRO	Anastácio Manuel da Costa 1	Anastácio 1		4AB
35	4AB											PLS	Badrick	Badrick Paulo Madadi		ASA	Anastácio Manuel da Costa 2	Anastácio 2		4AB
36	4AB											PET	Balacai	Evaristo Maurício Pascoal		ASA	Anderson Benjamim Monteiro	Guelor		4AB
37	4AB											PLS	Barrezó	Joveth Adão		BEN	André Augusto Miranda Kongo	Toy		4AB
38	4AB											INT	Bartolo	Bartolomeu Nguia Baptista		INT	Anthony Kevin Mfede Junior	Mfede		4AB
39	ACA	Adó Pena	António Joaquim Barros Pena				1	13	2	80		INT	Bebé 1	Odimir Abreu Gabriel Breganha 1		4AB	António Campos Calei	Campos		ACA	António Joaquim Barros Pena	Adó Pena
40	ACA	Aires	Ludgero Aires Cachicote da Rocha					1				ASA	Bebé 2	Odimir Abreu Gabriel Breganha 2		PRO	António da Silva Anato	Silva Anato		ACA	Ludgero Aires Cachicote da Rocha	Aires
41	ACA	Borges 1	Cláudio Ricardo Cunha Borges 1				1	8	1	81		PET	Bebo	Manuel João Miguel da Costa		SAG	António Gonçalo Cassule	Gomito Cassule		ACA	Cláudio Ricardo Cunha Borges 1	Borges 1
42	ACA	Cabibi Abreu 1	Mário Rui de Abreu 1						1			DES	Bebucho	Márcio Sakuala Ramalho Henriques		ACA	António Joaquim Barros Pena	Adó Pena		ACA	Mário Rui de Abreu 1	Cabibi Abreu 1
43	ACA	Camota	Júlio Camota Marcelino Tito					2	1			PRO	Belito ?			PRO	António Luís dos Santos Serrado	Lunguinha		ACA	Júlio Camota Marcelino Tito	Camota
44	ACA	Chabalala	Gaspar Necas Fortunato				3	4	6	29		DES	Belito Socola	Abelardo Gomes Socola		SAG	António Manuel da Silva Oliveira	Oliveira		ACA	Gaspar Necas Fortunato	Chabalala
45	ACA	Chiló	Francisco Ananias Orlando					11	5			BEN	Bena	Diveluca Simão Nascimento		PET	António Nzayinawo	Abdul		ACA	Francisco Ananias Orlando	Chiló
46	ACA	Cláudio	Cláudio Américo Loi Sozinho				1	18	2	82		SAG	Beni	Beni Kimbi Fua		4AB	António Pedro Augusto	Tony Augusto		ACA	Cláudio Américo Loi Sozinho	Cláudio
47	ACA	Day Day	Zaldivar Doval Augusto Cambinda						1			PRO	Benvindo Garcia	Benvindo Regresso Pontes Garcia		INT	António Sapalo Lohoca Justo	Paty		ACA	Zaldivar Doval Augusto Cambinda	Day Day
48	ACA	Djo	Martins Geovety P. Gervásio					8				PET	Benvindo Nsianfumu	Benvindo Afonso Nsianfumu		PRI	Antunes Sargento Ekundi	Sargento		ACA	Martins Geovety P. Gervásio	Djo
49	ACA	Elton	Elton Ernesto de Carvalho						4			CAA	Beny	Teodoro Edvaldo Rita Tchissingui		PET	Ariclene Assunção Oliveira	Ary Oliveira		ACA	Elton Ernesto de Carvalho	Elton
50	ACA	Fani	José Afonso dos Santos Fernando					21				MAI	Bernardo	Patrick Kifu Apataki		POR	Armando Domingos Maria	Lindala		ACA	José Afonso dos Santos Fernando	Fani
STATS2

Cell Formulas
Range	Formula
BZ1	BZ1	=INDIRECT("4AB!B5")
CA1	CA1	=INDIRECT("4AB!C5")
CE1	CE1	=INDIRECT("4AB!E5")
CF1	CF1	=INDIRECT("4AB!F5")
CG1	CG1	=INDIRECT("4AB!G5")
CH1:CH50	CH1	=IF($CE1=0,"",IF($CE1>0,RANK(CE1,$CE$1:$CE$608)+COUNTIF($CE$1:CE1,CE1)-1))
BZ2	BZ2	=INDIRECT("4AB!B6")
CA2	CA2	=INDIRECT("4AB!C6")
CE2	CE2	=INDIRECT("4AB!E6")
CF2	CF2	=INDIRECT("4AB!F6")
CG2	CG2	=INDIRECT("4AB!G6")
BZ3	BZ3	=INDIRECT("4AB!B7")
CA3	CA3	=INDIRECT("4AB!C7")
CE3	CE3	=INDIRECT("4AB!E7")
CF3	CF3	=INDIRECT("4AB!F7")
CG3	CG3	=INDIRECT("4AB!G7")
BZ4	BZ4	=INDIRECT("4AB!B8")
CA4	CA4	=INDIRECT("4AB!C8")
CE4	CE4	=INDIRECT("4AB!E8")
CF4	CF4	=INDIRECT("4AB!F8")
CG4	CG4	=INDIRECT("4AB!G8")
BZ5	BZ5	=INDIRECT("4AB!B9")
CA5	CA5	=INDIRECT("4AB!C9")
CE5	CE5	=INDIRECT("4AB!E9")
CF5	CF5	=INDIRECT("4AB!F9")
CG5	CG5	=INDIRECT("4AB!G9")
BZ6	BZ6	=INDIRECT("4AB!B10")
CA6	CA6	=INDIRECT("4AB!C10")
CE6	CE6	=INDIRECT("4AB!E10")
CF6	CF6	=INDIRECT("4AB!F10")
CG6	CG6	=INDIRECT("4AB!G10")
BZ7	BZ7	=INDIRECT("4AB!B11")
CA7	CA7	=INDIRECT("4AB!C11")
CE7	CE7	=INDIRECT("4AB!E11")
CF7	CF7	=INDIRECT("4AB!F11")
CG7	CG7	=INDIRECT("4AB!G11")
BZ8	BZ8	=INDIRECT("4AB!B12")
CA8	CA8	=INDIRECT("4AB!C12")
CE8	CE8	=INDIRECT("4AB!E12")
CF8	CF8	=INDIRECT("4AB!F12")
CG8	CG8	=INDIRECT("4AB!G12")
BZ9	BZ9	=INDIRECT("4AB!B13")
CA9	CA9	=INDIRECT("4AB!C13")
CE9	CE9	=INDIRECT("4AB!E13")
CF9	CF9	=INDIRECT("4AB!F13")
CG9	CG9	=INDIRECT("4AB!G13")
BZ10	BZ10	=INDIRECT("4AB!B14")
CA10	CA10	=INDIRECT("4AB!C14")
CE10	CE10	=INDIRECT("4AB!E14")
CF10	CF10	=INDIRECT("4AB!F14")
CG10	CG10	=INDIRECT("4AB!G14")
BZ11	BZ11	=INDIRECT("4AB!B15")
CA11	CA11	=INDIRECT("4AB!C15")
CE11	CE11	=INDIRECT("4AB!E15")
CF11	CF11	=INDIRECT("4AB!F15")
CG11	CG11	=INDIRECT("4AB!G15")
BZ12	BZ12	=INDIRECT("4AB!B16")
CA12	CA12	=INDIRECT("4AB!C16")
CE12	CE12	=INDIRECT("4AB!E16")
CF12	CF12	=INDIRECT("4AB!F16")
CG12	CG12	=INDIRECT("4AB!G16")
BZ13	BZ13	=INDIRECT("4AB!B17")
CA13	CA13	=INDIRECT("4AB!C17")
CE13	CE13	=INDIRECT("4AB!E17")
CF13	CF13	=INDIRECT("4AB!F17")
CG13	CG13	=INDIRECT("4AB!G17")
BZ14	BZ14	=INDIRECT("4AB!B18")
CA14	CA14	=INDIRECT("4AB!C18")
CE14	CE14	=INDIRECT("4AB!E18")
CF14	CF14	=INDIRECT("4AB!F18")
CG14	CG14	=INDIRECT("4AB!G18")
BZ15	BZ15	=INDIRECT("4AB!B19")
CA15	CA15	=INDIRECT("4AB!C19")
CE15	CE15	=INDIRECT("4AB!E19")
CF15	CF15	=INDIRECT("4AB!F19")
CG15	CG15	=INDIRECT("4AB!G19")
BZ16	BZ16	=INDIRECT("4AB!B20")
CA16	CA16	=INDIRECT("4AB!C20")
CE16	CE16	=INDIRECT("4AB!E20")
CF16	CF16	=INDIRECT("4AB!F20")
CG16	CG16	=INDIRECT("4AB!G20")
BZ17	BZ17	=INDIRECT("4AB!B21")
CA17	CA17	=INDIRECT("4AB!C21")
CE17	CE17	=INDIRECT("4AB!E21")
CF17	CF17	=INDIRECT("4AB!F21")
CG17	CG17	=INDIRECT("4AB!G21")
BZ18	BZ18	=INDIRECT("4AB!B22")
CA18	CA18	=INDIRECT("4AB!C22")
CE18	CE18	=INDIRECT("4AB!E22")
CF18	CF18	=INDIRECT("4AB!F22")
CG18	CG18	=INDIRECT("4AB!G22")
BZ19	BZ19	=INDIRECT("4AB!B23")
CA19	CA19	=INDIRECT("4AB!C23")
CE19	CE19	=INDIRECT("4AB!E23")
CF19	CF19	=INDIRECT("4AB!F23")
CG19	CG19	=INDIRECT("4AB!G23")
BZ20	BZ20	=INDIRECT("4AB!B24")
CA20	CA20	=INDIRECT("4AB!C24")
CE20	CE20	=INDIRECT("4AB!E24")
CF20	CF20	=INDIRECT("4AB!F24")
CG20	CG20	=INDIRECT("4AB!G24")
BZ21	BZ21	=INDIRECT("4AB!B25")
CA21	CA21	=INDIRECT("4AB!C25")
CE21	CE21	=INDIRECT("4AB!E25")
CF21	CF21	=INDIRECT("4AB!F25")
CG21	CG21	=INDIRECT("4AB!G25")
BZ22	BZ22	=INDIRECT("4AB!B26")
CA22	CA22	=INDIRECT("4AB!C26")
CE22	CE22	=INDIRECT("4AB!E26")
CF22	CF22	=INDIRECT("4AB!F26")
CG22	CG22	=INDIRECT("4AB!G26")
BZ23	BZ23	=INDIRECT("4AB!B27")
CA23	CA23	=INDIRECT("4AB!C27")
CE23	CE23	=INDIRECT("4AB!E27")
CF23	CF23	=INDIRECT("4AB!F27")
CG23	CG23	=INDIRECT("4AB!G27")
BZ24	BZ24	=INDIRECT("4AB!B28")
CA24	CA24	=INDIRECT("4AB!C28")
CE24	CE24	=INDIRECT("4AB!E28")
CF24	CF24	=INDIRECT("4AB!F28")
CG24	CG24	=INDIRECT("4AB!G28")
BZ25	BZ25	=INDIRECT("4AB!B29")
CA25	CA25	=INDIRECT("4AB!C29")
CE25	CE25	=INDIRECT("4AB!E29")
CF25	CF25	=INDIRECT("4AB!F29")
CG25	CG25	=INDIRECT("4AB!G29")
BZ26	BZ26	=INDIRECT("4AB!B30")
CA26	CA26	=INDIRECT("4AB!C30")
CE26	CE26	=INDIRECT("4AB!E30")
CF26	CF26	=INDIRECT("4AB!F30")
CG26	CG26	=INDIRECT("4AB!G30")
BZ27	BZ27	=INDIRECT("4AB!B31")
CA27	CA27	=INDIRECT("4AB!C31")
CE27	CE27	=INDIRECT("4AB!E31")
CF27	CF27	=INDIRECT("4AB!F31")
CG27	CG27	=INDIRECT("4AB!G31")
BZ28	BZ28	=INDIRECT("4AB!B32")
CA28	CA28	=INDIRECT("4AB!C32")
CE28	CE28	=INDIRECT("4AB!E32")
CF28	CF28	=INDIRECT("4AB!F32")
CG28	CG28	=INDIRECT("4AB!G32")
BZ29	BZ29	=INDIRECT("4AB!B33")
CA29	CA29	=INDIRECT("4AB!C33")
CE29	CE29	=INDIRECT("4AB!E33")
CF29	CF29	=INDIRECT("4AB!F33")
CG29	CG29	=INDIRECT("4AB!G33")
BZ30	BZ30	=INDIRECT("4AB!B34")
CA30	CA30	=INDIRECT("4AB!C34")
CE30	CE30	=INDIRECT("4AB!E34")
CF30	CF30	=INDIRECT("4AB!F34")
CG30	CG30	=INDIRECT("4AB!G34")
BZ31	BZ31	=INDIRECT("4AB!B35")
CA31	CA31	=INDIRECT("4AB!C35")
CE31	CE31	=INDIRECT("4AB!E35")
CF31	CF31	=INDIRECT("4AB!F35")
CG31	CG31	=INDIRECT("4AB!G35")
BZ32	BZ32	=INDIRECT("4AB!B36")
CA32	CA32	=INDIRECT("4AB!C36")
CE32	CE32	=INDIRECT("4AB!E36")
CF32	CF32	=INDIRECT("4AB!F36")
CG32	CG32	=INDIRECT("4AB!G36")
BZ33	BZ33	=INDIRECT("4AB!B37")
CA33	CA33	=INDIRECT("4AB!C37")
CE33	CE33	=INDIRECT("4AB!E37")
CF33	CF33	=INDIRECT("4AB!F37")
CG33	CG33	=INDIRECT("4AB!G37")
BZ34	BZ34	=INDIRECT("4AB!B38")
CA34	CA34	=INDIRECT("4AB!C38")
CE34	CE34	=INDIRECT("4AB!E38")
CF34	CF34	=INDIRECT("4AB!F38")
CG34	CG34	=INDIRECT("4AB!G38")
BZ35	BZ35	=INDIRECT("4AB!B39")
CA35	CA35	=INDIRECT("4AB!C39")
CE35	CE35	=INDIRECT("4AB!E39")
CF35	CF35	=INDIRECT("4AB!F39")
CG35	CG35	=INDIRECT("4AB!G39")
BZ36	BZ36	=INDIRECT("4AB!B40")
CA36	CA36	=INDIRECT("4AB!C40")
CE36	CE36	=INDIRECT("4AB!E40")
CF36	CF36	=INDIRECT("4AB!F40")
CG36	CG36	=INDIRECT("4AB!G40")
BZ37	BZ37	=INDIRECT("4AB!B41")
CA37	CA37	=INDIRECT("4AB!C41")
CE37	CE37	=INDIRECT("4AB!E41")
CF37	CF37	=INDIRECT("4AB!F41")
CG37	CG37	=INDIRECT("4AB!G41")
BZ38	BZ38	=INDIRECT("4AB!B42")
CA38	CA38	=INDIRECT("4AB!C42")
CE38	CE38	=INDIRECT("4AB!E42")
CF38	CF38	=INDIRECT("4AB!F42")
CG38	CG38	=INDIRECT("4AB!G42")
BZ39	BZ39	=INDIRECT("ACA!B5")
CA39	CA39	=INDIRECT("ACA!C5")
CE39	CE39	=INDIRECT("ACA!E5")
CF39	CF39	=INDIRECT("ACA!F5")
CG39	CG39	=INDIRECT("ACA!G5")
BZ40	BZ40	=INDIRECT("ACA!B6")
CA40	CA40	=INDIRECT("ACA!C6")
CE40	CE40	=INDIRECT("ACA!E6")
CF40	CF40	=INDIRECT("ACA!F6")
CG40	CG40	=INDIRECT("ACA!G6")
BZ41	BZ41	=INDIRECT("ACA!B7")
CA41	CA41	=INDIRECT("ACA!C7")
CE41	CE41	=INDIRECT("ACA!E7")
CF41	CF41	=INDIRECT("ACA!F7")
CG41	CG41	=INDIRECT("ACA!G7")
BZ42	BZ42	=INDIRECT("ACA!B8")
CA42	CA42	=INDIRECT("ACA!C8")
CE42	CE42	=INDIRECT("ACA!E8")
CF42	CF42	=INDIRECT("ACA!F8")
CG42	CG42	=INDIRECT("ACA!G8")
BZ43	BZ43	=INDIRECT("ACA!B9")
CA43	CA43	=INDIRECT("ACA!C9")
CE43	CE43	=INDIRECT("ACA!E9")
CF43	CF43	=INDIRECT("ACA!F9")
CG43	CG43	=INDIRECT("ACA!G9")
BZ44	BZ44	=INDIRECT("ACA!B10")
CA44	CA44	=INDIRECT("ACA!C10")
CE44	CE44	=INDIRECT("ACA!E10")
CF44	CF44	=INDIRECT("ACA!F10")
CG44	CG44	=INDIRECT("ACA!G10")
BZ45	BZ45	=INDIRECT("ACA!B11")
CA45	CA45	=INDIRECT("ACA!C11")
CE45	CE45	=INDIRECT("ACA!E11")
CF45	CF45	=INDIRECT("ACA!F11")
CG45	CG45	=INDIRECT("ACA!G11")
BZ46	BZ46	=INDIRECT("ACA!B12")
CA46	CA46	=INDIRECT("ACA!C12")
CE46	CE46	=INDIRECT("ACA!E12")
CF46	CF46	=INDIRECT("ACA!F12")
CG46	CG46	=INDIRECT("ACA!G12")
BZ47	BZ47	=INDIRECT("ACA!B13")
CA47	CA47	=INDIRECT("ACA!C13")
CE47	CE47	=INDIRECT("ACA!E13")
CF47	CF47	=INDIRECT("ACA!F13")
CG47	CG47	=INDIRECT("ACA!G13")
BZ48	BZ48	=INDIRECT("ACA!B14")
CA48	CA48	=INDIRECT("ACA!C14")
CE48	CE48	=INDIRECT("ACA!E14")
CF48	CF48	=INDIRECT("ACA!F14")
CG48	CG48	=INDIRECT("ACA!G14")
BZ49	BZ49	=INDIRECT("ACA!B15")
CA49	CA49	=INDIRECT("ACA!C15")
CE49	CE49	=INDIRECT("ACA!E15")
CF49	CF49	=INDIRECT("ACA!F15")
CG49	CG49	=INDIRECT("ACA!G15")
BZ50	BZ50	=INDIRECT("ACA!B16")
CA50	CA50	=INDIRECT("ACA!C16")
CE50	CE50	=INDIRECT("ACA!E16")
CF50	CF50	=INDIRECT("ACA!F16")
CG50	CG50	=INDIRECT("ACA!G16")
CJ1:CL501	CJ1	=SORT(FILTER(BY1:CA608,BZ1:BZ608<>0),2,1)
CN1:CP478	CN1	=SORT(FILTER(CB1:CD608,CC1:CC608<>0),2,1)
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
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
CL1:CL608	Cell Value	=0	text	NO
CK1:CK608	Expression	=CL1=0	text	NO
CE1:CG608	Cell Value	=0	text	NO
B12,CC1:CC607	Cell Value	=0	text	NO

 

[valmir]

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