Index match funcion ignoring blank cells

[valmir]

Hi everyone!
I am using these formulas to rank names and the problem I am having is that they are returning blank cells:
=COUNTIF($BZ1:$BZ608;"<="&$BZ1:$BZ608)

=IFERROR(INDEX($BY$1:$CA$608;MATCH(BX1;$CJ$1:$CJ$608;0);1);"")
=IFERROR(INDEX($BZ$1:$BZ$608;MATCH(BX1;$CJ$1:$CJ$608;0);2);"")
=IFERROR(INDEX($BY$1:$BY$608;MATCH(BX1;$CJ$1:$CJ$608;0);3);"")

I have gone through all similar threads and I could not get anything to work
Please help!

Stats 2016.xlsx
	CK
38
STATS2

 

[jasonb75]

Edited post:-

This should do it, The references to CG1 should be changed to the top left cell with the working SORT formula for the original question.

=INDEX(CG1#;SEQUENCE(ROWS(CG1#));{1;3;2})

 

[valmir]

=INDEX(CG1#;SEQUENCE(ROWS(CG1#));{1;3;2})

Columns CL to CN should look like this:

Stats 2016.xlsx
	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO
1	1	4AB	Borra			3	3			PET	Abdul	António Nzayinawo	61		PLS	Abel Lukango	Abel	130
2	2	4AB	Cadiata			21				MAI	Abegá	Manuel Pedro Pacavira	77		DES	Abelardo Gomes Socola	Belito Socola	130
3	3	4AB	Campos	António Campos Calei	1	24		73		PLS	Abel	Abel Lukango	80		DES	Abengea Mambani Ali	Ali	39
4	4	4AB	Chara ?			2	1			INT	Abianda 1	Yann Junior Abianda 1	98		DES	Adão Francisco Congo Zalata	Dadão Bile	130
5	5	4AB	Dany Ribeiro	Daniel Mendes Ribeiro	6	26	1	8		KAB	Abianda 2	Yann Junior Abianda 2	123		PRI	Adão Joaquim Bango Cabaça	Tony Cabaça	109
6	6	4AB	Dário Amorim	Dário Félix Amorim			3			LIB	Adilson Kivão	Adilson Ernesto Kivão	126		CAA	Adelino Wima Calunhi António	Maninho	114
7	7	4AB	Dudú Jack	Domingos Monteiro Jack	1	8	4	74		CAA	Adilson Manuel	Adilson Joaquim Manuel	157		SAG	Adérito Yandelela Chissoca 1	Lelas 1	128
8	8	4AB	Edgard	Edgard Arnaud Afane		18				ACA	Adó Pena	António Joaquim Barros Pena	160		MAI	Adérito Yandelela Chissoca 2	Lelas 2	139
9	9	4AB	Edi			1				BEN	Ady Paulo	Agostinho Domingos Paulo	161		INT	Adilson Cipriano da Cruz	Neblú	130
10	10	4AB	Geúda	Gelson L. da S. Teles		8	7			PLS	Agoya	Augusto Makanja	216		LIB	Adilson Ernesto Kivão	Adilson Kivão	193
11	11	4AB	Gui	Manuel Porfírio Pompílio de Matos		6				ACA	Aires	Ludgero Aires Cachicote da Rocha	224		CAA	Adilson Joaquim Manuel	Adilson Manuel	335
12	12	4AB	Hippi	Domingos Paulo João José	1	15	1	75		MAI	Alex	Alexandre G. Mário Miguel	231		SAG	Adolfo da Silva Catemba 1	Denilson 1	130
13	13	4AB	Ivanilson				5			POR	Alexandre	Alexandre Domingos Cristóvão M'Futila	238		POR	Adolfo da Silva Catemba 2	Denilson 2	130
14	14	4AB	Jairzinho	Jair Sebastião de Castro		8	5			DES	Ali	Abengea Mambani Ali	243		PRO	Adriano Belmiro Duarte Nicolau	Yano	230
15	15	4AB	Jesse				3			PRO	Almeida	José Xavier Fernando	247		DES	Adriano da Costa Mateus Alberto	Kumaca	130
16	16	4AB	Kandú	Déo Kanda	3	14	9	27		KAB	Amarildo 1	Amarildo Eugénio Augusto Miranda 1	268		ASA	Afonso Sebastião Cabungula	Fofó	118
17	17	4AB	Kizombé	Alberto dos Santos Domingos		16	1			ASA	Amarildo 2	Amarildo Eugénio Augusto Miranda 2	279		INT	Agostinho Cristóvão Paciência	Mabululu	23
18	18	4AB	Lara ?				1			BEN	Amaro	Amândio Manuel Filipe da Costa	286		BEN	Agostinho Domingos Paulo	Ady Paulo	130
19	19	4AB	Lelo	José Lelo Sevo Barros	1	14		76		BEN	Amido Baldé	Amido Baldé	294		KAB	Aires Emilson Gonzaga Zeca	Emilson	267
20	20	4AB	Manucho Muquengue	Mariano Simão Muquengue	2	15	11	46		PRO	Anastácio 1	Anastácio Manuel da Costa 1	329		MAI	Albano Camuele Sumano	Pai da Bola	341
21	21	4AB	Matamba	Matamba Paulino Sousa	1	27		77		ASA	Anastácio 2	Anastácio Manuel da Costa 2	338		PRI	Albert Milambo-Mutamba	Milambo	350
22	22	4AB	Paito	Manuel Gaspar Fernandes		1	5			INT	António	Tomás Chilombo Cambuque Sapalo	398		PRI	Alberto Álvaro Paca	Fissy	325
23	23	4AB	Palucho	Paulo Pereira da Silva	1	6	13	78		SAG	Ary Calvete	Helmut Ariadne Figueiredo Calvete	402		4AB	Alberto dos Santos Domingos	Kizombé	402
24	24	4AB	Pilola	José Olívio Andrade Pereira	3	11		28		PET	Ary Oliveira	Ariclene Assunção Oliveira	418		SAG	Alentua Tangala Rolli	Rolli	271
25	25	4AB	Prazeres	Prazeres Chijica M. Dala		3	1			PRI	Ary Papel	Manuel David Afonso	423		POR	Alexandre Domingos Cristóvão M'Futila	Alexandre	408
26	26	4AB	Rochana	Panda Marcelo José		23				PLS	Atunako	Eboue Manzambi Atunako	432		MAI	Alexandre G. Mário Miguel	Alex	397
27	27	4AB	Rui ?			8	2			PRO	Avex 1	Avelino Eduardo António Craque 1	437		DES	Alfredo Cassinda Calunganga	Chiwe	130
28	28	4AB	Tony Augusto	António Pedro Augusto		7				DES	Avex 2	Avelino Eduardo António Craque 2	461		LIB	Alfredo Kulembe Ribeiro	Fredy	46
29	29	4AB	Vander	Vander Ramos Pinto		19				SAG	Ayala	Mário Álvaro Agostinho	479		ASA	Almiro Edson Daniel Lobo	Miro	454
30	30	4AB	Xavier	Xavier Eduardo Vicente Vunge	1	23		79		PET	Azulão	Tiago Lima Leal	489		BEN	Amândio Manuel Filipe da Costa	Amaro	468
31	31	4AB								ASA	Babacar	Babacar Fall	107		KAB	Amarildo Eugénio Augusto Miranda 1	Amarildo 1	130
32	32	4AB								BEN	Baby	Valdemar Denso António	107		ASA	Amarildo Eugénio Augusto Miranda 2	Amarildo 2	130
33	33	4AB								PLS	Badrick	Badrick Paulo Madadi	107		BEN	Amido Baldé	Amido Baldé	130
34	34	4AB								PET	Balacai	Evaristo Maurício Pascoal	107		PRO	Anastácio Manuel da Costa 1	Anastácio 1	130
35	35	4AB								PLS	Barrezó	Joveth Adão	107		ASA	Anastácio Manuel da Costa 2	Anastácio 2	130
36	36	4AB								INT	Bartolo	Bartolomeu Nguia Baptista	107		ASA	Anderson Benjamim Monteiro	Guelor	130
37	37	4AB								INT	Bebé 1	Odimir Abreu Gabriel Breganha 1	107		BEN	André Augusto Miranda Kongo	Toy	130
38	38	4AB								ASA	Bebé 2	Odimir Abreu Gabriel Breganha 2	107		INT	Anthony Kevin Mfede Junior	Mfede	130
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:CE38	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")
CG1:CI478	CG1	=SORT(FILTER(BY1:CA608,CA1:CA608<>0),2,1)
CJ1:CJ608	CJ1	=COUNTIF($BZ1:$BZ608,"<="&$BZ1:$BZ608)
CL1:CL38	CL1	=IFERROR(INDEX($BY$1:$CA$608,MATCH(BX1,$CO$1:$CO$608,0),1),"")
CM1:CM38	CM1	=IFERROR(INDEX($BY$1:$CA$608,MATCH(BX1,$CO$1:$CO$608,0),3),"")
CN1:CN38	CN1	=IFERROR(INDEX($BY$1:$CA$608,MATCH(BX1,$CO$1:$CO$608,0),2),"")
CO1:CO608	CO1	=COUNTIF($CA1:$CA608,"<="&$CA1:$CA608)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
BY1	Expression	=BZ1=0	text	NO
BX1	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

But when I apply your formula to CL1, I am getting this:

Stats 2016.xlsx
	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO
1	1	4AB	Borra			3	3			PET	Abdul	António Nzayinawo	61		PET			130
2	2	4AB	Cadiata			21				MAI	Abegá	Manuel Pedro Pacavira	77		Manuel Pedro Pacavira			130
3	3	4AB	Campos	António Campos Calei	1	24		73		PLS	Abel	Abel Lukango	80		Abel			39
4	4	4AB	Chara ?			2	1			INT	Abianda 1	Yann Junior Abianda 1	98		###			130
5	5	4AB	Dany Ribeiro	Daniel Mendes Ribeiro	6	26	1	8		KAB	Abianda 2	Yann Junior Abianda 2	123		###			109
6	6	4AB	Dário Amorim	Dário Félix Amorim			3			LIB	Adilson Kivão	Adilson Ernesto Kivão	126		###			114
7	7	4AB	Dudú Jack	Domingos Monteiro Jack	1	8	4	74		CAA	Adilson Manuel	Adilson Joaquim Manuel	157		###			128
8	8	4AB	Edgard	Edgard Arnaud Afane		18				ACA	Adó Pena	António Joaquim Barros Pena	160		###			139
9	9	4AB	Edi			1				BEN	Ady Paulo	Agostinho Domingos Paulo	161		###			130
10	10	4AB	Geúda	Gelson L. da S. Teles		8	7			PLS	Agoya	Augusto Makanja	216		###			193
11	11	4AB	Gui	Manuel Porfírio Pompílio de Matos		6				ACA	Aires	Ludgero Aires Cachicote da Rocha	224		###			335
12	12	4AB	Hippi	Domingos Paulo João José	1	15	1	75		MAI	Alex	Alexandre G. Mário Miguel	231		###			130
13	13	4AB	Ivanilson				5			POR	Alexandre	Alexandre Domingos Cristóvão M'Futila	238		###			130
14	14	4AB	Jairzinho	Jair Sebastião de Castro		8	5			DES	Ali	Abengea Mambani Ali	243		###			230
15	15	4AB	Jesse				3			PRO	Almeida	José Xavier Fernando	247		###			130
16	16	4AB	Kandú	Déo Kanda	3	14	9	27		KAB	Amarildo 1	Amarildo Eugénio Augusto Miranda 1	268		###			118
17	17	4AB	Kizombé	Alberto dos Santos Domingos		16	1			ASA	Amarildo 2	Amarildo Eugénio Augusto Miranda 2	279		###			23
18	18	4AB	Lara ?				1			BEN	Amaro	Amândio Manuel Filipe da Costa	286		###			130
19	19	4AB	Lelo	José Lelo Sevo Barros	1	14		76		BEN	Amido Baldé	Amido Baldé	294		###			267
20	20	4AB	Manucho Muquengue	Mariano Simão Muquengue	2	15	11	46		PRO	Anastácio 1	Anastácio Manuel da Costa 1	329		###			341
21	21	4AB	Matamba	Matamba Paulino Sousa	1	27		77		ASA	Anastácio 2	Anastácio Manuel da Costa 2	338		###			350
22	22	4AB	Paito	Manuel Gaspar Fernandes		1	5			INT	António	Tomás Chilombo Cambuque Sapalo	398		###			325
23	23	4AB	Palucho	Paulo Pereira da Silva	1	6	13	78		SAG	Ary Calvete	Helmut Ariadne Figueiredo Calvete	402		###			402
24	24	4AB	Pilola	José Olívio Andrade Pereira	3	11		28		PET	Ary Oliveira	Ariclene Assunção Oliveira	418		###			271
25	25	4AB	Prazeres	Prazeres Chijica M. Dala		3	1			PRI	Ary Papel	Manuel David Afonso	423		###			408
26	26	4AB	Rochana	Panda Marcelo José		23				PLS	Atunako	Eboue Manzambi Atunako	432		###			397
27	27	4AB	Rui ?			8	2			PRO	Avex 1	Avelino Eduardo António Craque 1	437		###			130
28	28	4AB	Tony Augusto	António Pedro Augusto		7				DES	Avex 2	Avelino Eduardo António Craque 2	461		###			46
29	29	4AB	Vander	Vander Ramos Pinto		19				SAG	Ayala	Mário Álvaro Agostinho	479		###			454
30	30	4AB	Xavier	Xavier Eduardo Vicente Vunge	1	23		79		PET	Azulão	Tiago Lima Leal	489		###			468
31	31	4AB								ASA	Babacar	Babacar Fall	107		###			130
32	32	4AB								BEN	Baby	Valdemar Denso António	107		###			130
33	33	4AB								PLS	Badrick	Badrick Paulo Madadi	107		###			130
34	34	4AB								PET	Balacai	Evaristo Maurício Pascoal	107		###			130
35	35	4AB								PLS	Barrezó	Joveth Adão	107		###			130
36	36	4AB								INT	Bartolo	Bartolomeu Nguia Baptista	107		###			130
37	37	4AB								INT	Bebé 1	Odimir Abreu Gabriel Breganha 1	107		###			130
38	38	4AB								ASA	Bebé 2	Odimir Abreu Gabriel Breganha 2	107		###			130
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:CE38	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")
CG1:CI478	CG1	=SORT(FILTER(BY1:CA608,CA1:CA608<>0),2,1)
CJ1:CJ608	CJ1	=COUNTIF($BZ1:$BZ608,"<="&$BZ1:$BZ608)
CL1:CL478	CL1	=INDEX(CG1#,SEQUENCE(ROWS(CG1#)),{1;3;2})
CO1:CO608	CO1	=COUNTIF($CA1:$CA608,"<="&$CA1:$CA608)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
BY1	Expression	=BZ1=0	text	NO
BX1	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

 

[jasonb75]

Try this instead, the formula that I suggested worked for me but that is with commas instead of semicolons.

=INDEX(CG1#;SEQUENCE(ROWS(CG1#));{1\3\2})

 

[valmir]

=INDEX(CG1#;SEQUENCE(ROWS(CG1#));{1\3\2})

Sorry Sir. Maybe I didn't give you a good explanation. What I have with this formula is simply a swap between CH and CI. When I said that "I want to achieve the same result", I meant CA in alphabetical order. (The first one that you did (=SORT(FILTER(BY1:CA608;CA1:CA608<>0);2;1)) was BZ in alphabetical order. I hope I have been clear now and sorry for confusing you.

 

[jasonb75]

Referring back to post 13,

It's actually sorted on CA, not BY. To sort on BZ, change 3 to 2.

BY=1 (1st column)
BZ=2 (2nd column)
CA=3 (3rd column)

You just need to change the column number.

=SORT(FILTER(BY1:CA608;CA1:CA608<>0);3;1)

 

[valmir]

=SORT(FILTER(BY1:CA608;CA1:CA608<>0);3;1)

I am sorry for the late reply. I had to attend an urgent engagement and only came back now. The above formula gets the CN column sorted alphabetically just as I want however, it still places the full names column (sourced from CA) after the CM column (sourced from BZ). This time, I want to have the full name column (alphabetically sorted) to appear before the matching nicknames. Please take a look how it appears after I applied this last formula:

Stats 2016.xlsx
	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO
1	1	4AB	Borra			3	3			PET	Abdul	António Nzayinawo	61		PLS	Abel	Abel Lukango	130
2	2	4AB	Cadiata			21				MAI	Abegá	Manuel Pedro Pacavira	77		DES	Belito Socola	Abelardo Gomes Socola	130
3	3	4AB	Campos	António Campos Calei	1	24		73		PLS	Abel	Abel Lukango	80		DES	Ali	Abengea Mambani Ali	39
4	4	4AB	Chara ?			2	1			INT	Abianda 1	Yann Junior Abianda 1	98		DES	Dadão Bile	Adão Francisco Congo Zalata	130
5	5	4AB	Dany Ribeiro	Daniel Mendes Ribeiro	6	26	1	8		KAB	Abianda 2	Yann Junior Abianda 2	123		PRI	Tony Cabaça	Adão Joaquim Bango Cabaça	109
6	6	4AB	Dário Amorim	Dário Félix Amorim			3			LIB	Adilson Kivão	Adilson Ernesto Kivão	126		CAA	Maninho	Adelino Wima Calunhi António	114
7	7	4AB	Dudú Jack	Domingos Monteiro Jack	1	8	4	74		CAA	Adilson Manuel	Adilson Joaquim Manuel	157		SAG	Lelas 1	Adérito Yandelela Chissoca 1	128
8	8	4AB	Edgard	Edgard Arnaud Afane		18				ACA	Adó Pena	António Joaquim Barros Pena	160		MAI	Lelas 2	Adérito Yandelela Chissoca 2	139
9	9	4AB	Edi			1				BEN	Ady Paulo	Agostinho Domingos Paulo	161		INT	Neblú	Adilson Cipriano da Cruz	130
10	10	4AB	Geúda	Gelson L. da S. Teles		8	7			PLS	Agoya	Augusto Makanja	216		LIB	Adilson Kivão	Adilson Ernesto Kivão	193
11	11	4AB	Gui	Manuel Porfírio Pompílio de Matos		6				ACA	Aires	Ludgero Aires Cachicote da Rocha	224		CAA	Adilson Manuel	Adilson Joaquim Manuel	335
12	12	4AB	Hippi	Domingos Paulo João José	1	15	1	75		MAI	Alex	Alexandre G. Mário Miguel	231		SAG	Denilson 1	Adolfo da Silva Catemba 1	130
13	13	4AB	Ivanilson				5			POR	Alexandre	Alexandre Domingos Cristóvão M'Futila	238		POR	Denilson 2	Adolfo da Silva Catemba 2	130
14	14	4AB	Jairzinho	Jair Sebastião de Castro		8	5			DES	Ali	Abengea Mambani Ali	243		PRO	Yano	Adriano Belmiro Duarte Nicolau	230
15	15	4AB	Jesse				3			PRO	Almeida	José Xavier Fernando	247		DES	Kumaca	Adriano da Costa Mateus Alberto	130
16	16	4AB	Kandú	Déo Kanda	3	14	9	27		KAB	Amarildo 1	Amarildo Eugénio Augusto Miranda 1	268		ASA	Fofó	Afonso Sebastião Cabungula	118
17	17	4AB	Kizombé	Alberto dos Santos Domingos		16	1			ASA	Amarildo 2	Amarildo Eugénio Augusto Miranda 2	279		INT	Mabululu	Agostinho Cristóvão Paciência	23
18	18	4AB	Lara ?				1			BEN	Amaro	Amândio Manuel Filipe da Costa	286		BEN	Ady Paulo	Agostinho Domingos Paulo	130
19	19	4AB	Lelo	José Lelo Sevo Barros	1	14		76		BEN	Amido Baldé	Amido Baldé	294		KAB	Emilson	Aires Emilson Gonzaga Zeca	267
20	20	4AB	Manucho Muquengue	Mariano Simão Muquengue	2	15	11	46		PRO	Anastácio 1	Anastácio Manuel da Costa 1	329		MAI	Pai da Bola	Albano Camuele Sumano	341
21	21	4AB	Matamba	Matamba Paulino Sousa	1	27		77		ASA	Anastácio 2	Anastácio Manuel da Costa 2	338		PRI	Milambo	Albert Milambo-Mutamba	350
22	22	4AB	Paito	Manuel Gaspar Fernandes		1	5			INT	António	Tomás Chilombo Cambuque Sapalo	398		PRI	Fissy	Alberto Álvaro Paca	325
23	23	4AB	Palucho	Paulo Pereira da Silva	1	6	13	78		SAG	Ary Calvete	Helmut Ariadne Figueiredo Calvete	402		4AB	Kizombé	Alberto dos Santos Domingos	402
24	24	4AB	Pilola	José Olívio Andrade Pereira	3	11		28		PET	Ary Oliveira	Ariclene Assunção Oliveira	418		SAG	Rolli	Alentua Tangala Rolli	271
25	25	4AB	Prazeres	Prazeres Chijica M. Dala		3	1			PRI	Ary Papel	Manuel David Afonso	423		POR	Alexandre	Alexandre Domingos Cristóvão M'Futila	408
26	26	4AB	Rochana	Panda Marcelo José		23				PLS	Atunako	Eboue Manzambi Atunako	432		MAI	Alex	Alexandre G. Mário Miguel	397
27	27	4AB	Rui ?			8	2			PRO	Avex 1	Avelino Eduardo António Craque 1	437		DES	Chiwe	Alfredo Cassinda Calunganga	130
28	28	4AB	Tony Augusto	António Pedro Augusto		7				DES	Avex 2	Avelino Eduardo António Craque 2	461		LIB	Fredy	Alfredo Kulembe Ribeiro	46
29	29	4AB	Vander	Vander Ramos Pinto		19				SAG	Ayala	Mário Álvaro Agostinho	479		ASA	Miro	Almiro Edson Daniel Lobo	454
30	30	4AB	Xavier	Xavier Eduardo Vicente Vunge	1	23		79		PET	Azulão	Tiago Lima Leal	489		BEN	Amaro	Amândio Manuel Filipe da Costa	468
31	31	4AB								ASA	Babacar	Babacar Fall	107		KAB	Amarildo 1	Amarildo Eugénio Augusto Miranda 1	130
32	32	4AB								BEN	Baby	Valdemar Denso António	107		ASA	Amarildo 2	Amarildo Eugénio Augusto Miranda 2	130
33	33	4AB								PLS	Badrick	Badrick Paulo Madadi	107		BEN	Amido Baldé	Amido Baldé	130
34	34	4AB								PET	Balacai	Evaristo Maurício Pascoal	107		PRO	Anastácio 1	Anastácio Manuel da Costa 1	130
35	35	4AB								PLS	Barrezó	Joveth Adão	107		ASA	Anastácio 2	Anastácio Manuel da Costa 2	130
36	36	4AB								INT	Bartolo	Bartolomeu Nguia Baptista	107		ASA	Guelor	Anderson Benjamim Monteiro	130
37	37	4AB								INT	Bebé 1	Odimir Abreu Gabriel Breganha 1	107		BEN	Toy	André Augusto Miranda Kongo	130
38	38	4AB								ASA	Bebé 2	Odimir Abreu Gabriel Breganha 2	107		INT	Mfede	Anthony Kevin Mfede Junior	130
39	39	ACA	Adó Pena	António Joaquim Barros Pena	1	13	2	80		PET	Bebo	Manuel João Miguel da Costa	8		4AB	Campos	António Campos Calei	42
40	40	ACA	Aires	Ludgero Aires Cachicote da Rocha		1				DES	Bebucho	Márcio Sakuala Ramalho Henriques	12		PRO	Silva Anato	António da Silva Anato	303
41	41	ACA	Borges 1	Cláudio Ricardo Cunha Borges 1	1	8	1	81		DES	Belito Socola	Abelardo Gomes Socola	59		SAG	Gomito Cassule	António Gonçalo Cassule	99
42	42	ACA	Cabibi Abreu 1	Mário Rui de Abreu 1			1			BEN	Bena	Diveluca Simão Nascimento	73		ACA	Adó Pena	António Joaquim Barros Pena	345
43	43	ACA	Camota	Júlio Camota Marcelino Tito		2	1			SAG	Beni	Beni Kimbi Fua	79		PRO	Lunguinha	António Luís dos Santos Serrado	281
44	44	ACA	Chabalala	Gaspar Necas Fortunato	3	4	6	29		PRO	Benvindo Garcia	Benvindo Regresso Pontes Garcia	97		SAG	Oliveira	António Manuel da Silva Oliveira	192
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:CE44	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")
BZ41	BZ41	=INDIRECT("ACA!B7")
CA41	CA41	=INDIRECT("ACA!C7")
CB41	CB41	=INDIRECT("ACA!E7")
CC41	CC41	=INDIRECT("ACA!F7")
CD41	CD41	=INDIRECT("ACA!G7")
BZ42	BZ42	=INDIRECT("ACA!B8")
CA42	CA42	=INDIRECT("ACA!C8")
CB42	CB42	=INDIRECT("ACA!E8")
CC42	CC42	=INDIRECT("ACA!F8")
CD42	CD42	=INDIRECT("ACA!G8")
BZ43	BZ43	=INDIRECT("ACA!B9")
CA43	CA43	=INDIRECT("ACA!C9")
CB43	CB43	=INDIRECT("ACA!E9")
CC43	CC43	=INDIRECT("ACA!F9")
CD43	CD43	=INDIRECT("ACA!G9")
BZ44	BZ44	=INDIRECT("ACA!B10")
CA44	CA44	=INDIRECT("ACA!C10")
CB44	CB44	=INDIRECT("ACA!E10")
CC44	CC44	=INDIRECT("ACA!F10")
CD44	CD44	=INDIRECT("ACA!G10")
CG1:CI478	CG1	=SORT(FILTER(BY1:CA608,CA1:CA608<>0),2,1)
CJ1:CJ608	CJ1	=COUNTIF($BZ1:$BZ608,"<="&$BZ1:$BZ608)
CL1:CN478	CL1	=SORT(FILTER(BY1:CA608,CA1:CA608<>0),3,1)
CO1:CO608	CO1	=COUNTIF($CA1:$CA608,"<="&$CA1:$CA608)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
BY1	Expression	=BZ1=0	text	NO
BX1	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

 

[valmir]

I have now noticed another issue with the original formula. Some names in BZ do not have a corresponding match in CA. This formula:
=SORT(FILTER(BY1:CA608;CA1:CA608<>0);2;1)
ignores all names in BZ without a corresponding match in CA. This formula, however, with the required changes for CL to CO, should work fine because all names in CA have a corresponding match in BZ.

 

[valmir]

It looks like some of your blanks might actually be zeros that are being masked by conditional formatting, see if this corrects it.

=SORT(FILTER(BY1:CA10,CA1:CA10<>0),2,1)

I was playing aroung with your formula because I don't know much about how excel formulas work and by trial and error I used this variant:
=SORT(FILTER(BY1:CA608;BZ1:BZ608<>0);2;1)
And now I can say that this one works perfectly, including all BZ values without a corresponding CA values (which I didn't have with the above formula). Now I am struggling to figure out the rest.

 

[jasonb75]

=SORT(FILTER(BY1:CA608;BZ1:BZ608<>0);2;1)
And now I can say that this one works perfectly, including all BZ values without a corresponding CA values (which I didn't have with the above formula). Now I am struggling to figure out the rest.

By that, I assume that you mean swapping the 2 columns around. I think that this is what you need based on the formula from your post that you say works perfectly.

=INDEX(SORT(FILTER(BY1:CA608;BZ1:BZ608<>0);2;1),SEQUENCE(ROWS(BZ1:BZ608)),{1\3\2})

I haven't tested this because I didn't save the file that I used to test the earlier formulas. If it's not correct then I'll set it up again later but I'm a bit short on time now.

 

[valmir]

=INDEX(SORT(FILTER(BY1:CA608;BZ1:BZ608<>0);2;1),SEQUENCE(ROWS(BZ1:BZ608)),{1\3\2})

Thanks again for your reply Sir. This formula is almost there. The only problem is that it alphabetically sorts the last column instead of the middle column
(I will wait until you have time of course ? ) Also note that 3rd column values without a corresponding match on the 2nd column should not appear at all.
Please take a look at the result:

Stats 2016.xlsx
	CY	CZ	DA
1	PET	António Nzayinawo	Abdul
2	MAI	Manuel Pedro Pacavira	Abegá
3	PLS	Abel Lukango	Abel
4	INT	Yann Junior Abianda 1	Abianda 1
5	KAB	Yann Junior Abianda 2	Abianda 2
6	LIB	Adilson Ernesto Kivão	Adilson Kivão
7	CAA	Adilson Joaquim Manuel	Adilson Manuel
8	ACA	António Joaquim Barros Pena	Adó Pena
9	ASA	0	Ady ?
10	BEN	Agostinho Domingos Paulo	Ady Paulo
11	PLS	Augusto Makanja	Agoya
12	ACA	Ludgero Aires Cachicote da Rocha	Aires
13	MAI	Alexandre G. Mário Miguel	Alex
14	POR	Alexandre Domingos Cristóvão M'Futila	Alexandre
15	DES	Abengea Mambani Ali	Ali
16	PRO	José Xavier Fernando	Almeida
17	KAB	Amarildo Eugénio Augusto Miranda 1	Amarildo 1
18	ASA	Amarildo Eugénio Augusto Miranda 2	Amarildo 2
19	BEN	Amândio Manuel Filipe da Costa	Amaro
20	BEN	Amido Baldé	Amido Baldé
21	PRO	Anastácio Manuel da Costa 1	Anastácio 1
22	ASA	Anastácio Manuel da Costa 2	Anastácio 2
23	BEN	0	Anderson
24	INT	Tomás Chilombo Cambuque Sapalo	António
25	SAG	Helmut Ariadne Figueiredo Calvete	Ary Calvete
26	PET	Ariclene Assunção Oliveira	Ary Oliveira
27	PRI	Manuel David Afonso	Ary Papel
28	PLS	Eboue Manzambi Atunako	Atunako
STATS2