Extract only unique 1X2 and result must show in order 1X2

[Kishan]

Using Excel 2000</SPAN></SPAN>

Hi,</SPAN></SPAN>

I need a formula in the row 13,14,15 which can find unique character from row 6,7,8 and show result in order as 1X2 in the rows 13,14 and 15 </SPAN></SPAN>

Resulting data...</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2
3
4
5
6			1	1	2	1	1	X	1	2	X	2	1	1	X	X
7				1	1	2	1	X	1	1	2	2	2	1	1	X
8			1	2	1	1	1	1	2	X	2	2	1	X	X
9
10
11
12
13			1	1	1	1	1	1	1	1	X	2	1	1	1	X
14				2	2	2		X	2	X	2		2	X	X
15										2
16
17
18
19
Sheet10

Thank you in advance</SPAN></SPAN>

Regards,</SPAN>
Kishan</SPAN></SPAN>

 

[Marcelo Branco]

Try

Array formula in C13 copied across and down
=IFERROR(INDEX({1;"X";2},SMALL(IF(ISNUMBER(MATCH({1;"X";2},C$6:C$8,0)),IF(COUNTIF(C$12:C12,{1;"X";2})=0,{1;2;3})),1)),"")
Ctrl+Shift+Enter

M.

 

[XOR LX]

Hi,

In C13:

=IF(ROWS(C$6:C6)>COUNT(MATCH({1,"X",2},C$6:C$8,0)),"",INDEX({1,"X",2},SMALL(IF(ISNUMBER(MATCH({1,"X",2},C$6:C$8,0)),{1,2,3}),ROWS(C$6:C6))))

Copy down and across as required.

Regards

 

[XOR LX]

Try

Array formula in C13 copied across and down
=IFERROR(INDEX({1;"X";2},SMALL(IF(ISNUMBER(MATCH({1;"X";2},C$6:C$8,0)),IF(COUNTIF(C$12:C12,{1;"X";2})=0,{1;2;3})),1)),"")
Ctrl+Shift+Enter

M.

Ciao, Marcelo.

OP's using Excel 2000!

Also, that doesn't need CSE.

Cheers

 

[Kishan]

Try

Array formula in C13 copied across and down
=IFERROR(INDEX({1;"X";2},SMALL(IF(ISNUMBER(MATCH({1;"X";2},C$6:C$8,0)),IF(COUNTIF(C$12:C12,{1;"X";2})=0,{1;2;3})),1)),"")
Ctrl+Shift+Enter

M.

Hi Marcelo Branco, i search in the goggle that the IFERROR function in Microsoft Excel 2000 does not work. </SPAN></SPAN>

Thank you for your help</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[Kishan]

Hi,

In C13:

=IF(ROWS(C$6:C6)>COUNT(MATCH({1,"X",2},C$6:C$8,0)),"",INDEX({1,"X",2},SMALL(IF(ISNUMBER(MATCH({1,"X",2},C$6:C$8,0)),{1,2,3}),ROWS(C$6:C6))))

Copy down and across as required.

Regards

XOR LX, your formula results as expected!</SPAN></SPAN>

Thank you for your help</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[Marcelo Branco]

Ciao, Marcelo.

OP's using Excel 2000!

Also, that doesn't need CSE.

Cheers

Ciao XOR

I did not notice that OP uses XL 2000.

Grazie

M.

 

[Marcelo Branco]

Hi Marcelo Branco, i search in the goggle that the IFERROR function in Microsoft Excel 2000 does not work.

Thank you for your help

Kind Regards,
Kishan

You're right, IFERROR is not available in XL 2000.

XOR has already provided a formula that works in your Excel version.

M.

 

[XOR LX]

XOR LX, your formula results as expected!

Thank you for your help

Kind Regards,
Kishan

You're welcome!

Cheers