From a range to a unique vertical list

[gianvi]

Hello to all,
i have to work in same situation as the file in thread in post #5.
i translated the formula to join my italian language and also replaced " , " with " ; " but still not working.
i miss something?
i tried both way with normal formula and the ctrl/shift/enter way.

Tkyou for your help

office professional 2016 on Windows

test2.xlsx
	B	C	D	E	F	G	H	I	J	K	L
1											list
2	s01	s01	s01	s01	s01	s01	s01				0
3						s01					0
4		s02	s02	s02				s04			0
5		s02	s02	s02				s04			0
6		s02	s02	s03				s04			0
7		s02	s02	s02				s04			0
8		s02	s02	s02				s04			0
9											0
10											0
11				s04							0
12											0
13											0
14						s03					0
15						s03					0
16						s04					0
17						s05					0
18						s05					0
my range

Cell Formulas
Range	Formula
L2:L18	L2	=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROWS(B$2:I$18)*10^6+COLUMNS(B$2:I$18))/((B$2:I$18<>"")*(ISNA(MATCH(B$2:I$18,L$1:L1,0)))),1),"R000000C000000"),0),"")

 

[gianvi]

here my formula in italian
=SE.ERRORE(INDIRETTO(TESTO(AGGREGA(15;6;(RIGHE(B$2:I$18)*10^6+COLONNE(B$2:I$18))/((B$2:I$18<>"")*(VAL.NON.DISP(CONFRONTA(B$2:I$18;L$1:L1;0))));1);"R000000C000000");0);"")

 

[gianvi]

Found the trick right now.
Rows and Columns function in Italian translation are not RIGHE and COLONNE but RIF.RIGA and RIF.COLONNA
It works like a charm, tkyou.
here the correct formula in italian :
=SE.ERRORE(INDIRETTO(TESTO(AGGREGA(15;6;RIF.RIGA(B$2:I$18)*10^6+RIF.COLONNA(B$2:I$18))/((B$2:I$18<>"")*VAL.NON.DISP(CONFRONTA(B$2:I$18;L$1:L1;0))));1);"R000000C000000");0);"")

 

[Peter_SSs]

here my formula in italian
=SE.ERRORE(INDIRETTO(TESTO(AGGREGA(15;6;(RIGHE(B$2:I$18)*10^6+COLONNE(B$2:I$18))/((B$2:I$18<>"")*(VAL.NON.DISP(CONFRONTA(B$2:I$18;L$1:L1;0))));1);"R000000C000000");0);"")

You have used (English) ROWS instead of ROW and COLUMNS instead of COLUMN
So change RIGHE to RIF.RIGA and COLONNE to RIF.COLONNA

Edit: Ah, I see you got it while I was typing my response.

Rows and Columns function in Italian translation are not RIGHE and COLONNE

In fact they are, but you needed ROW and COLUMN instead.

 

[gianvi]

You have used (English) ROWS instead of ROW and COLUMNS instead of COLUMN
So change RIGHE to RIF.RIGA and COLONNE to RIF.COLONNA

Edit: Ah, I see you got it while I was typing my response.


In fact they are, but you needed ROW and COLUMN instead.

tkyou so much the same for your reply.