Find unique and missing

[Kishan]

Using Excel 2000
Hi,

I want to find among each row in C:F column what is unique or missing, results in the coloums H:I
Example data

	A	B	C	D	E	F	G	H	I	J
1
2
3
4
5			P1	P2	P3	P4		Unique	Missing
6			1	1	2	1		12	X
7			1	X	2	2		1X2
8			1	X	1	1		1X	2
9			1	X	1	2		1X2
10			X	1	X	1		1X	2
11			X	X	X	1		1X	2
12			2	X	1	1		1X2
13			X	1	X	X		1X	2
14			1	X	X	1		1X	2
15			X	2	X	1		1X2
16			1	1	1	1		1	X2
17			1	2	1	2		12	X
18			1	X	X	1		1X	2
19			1	X	X	1		1X	2
20
21
22

<colgroup><col><col span="2"><col span="4"><col><col span="2"><col></colgroup><tbody>
</tbody>

Thank you in advance

Regards,
Kishan

 

[Fluff]

Probably neater ways, but how about

Excel 2013 32 bit
	C	D	E	F	G	H	I
5	P1	P2	P3	P4
6	1	1	2	1		12	X
7	1	X	2	2		1X2
8	1	X	1	1		1X	2
9	1	X	1	2		1X2
10	X	1	X	1		1X	2
11	X	X	X	1		1X	2
12	2	X	1	1		1X2
13	X	1	X	X		1X	2
14	1	X	X	1		1X	2
15	X	2	X	1		1X2
16	1	1	1	1		1	X2
17	1	2	1	2		12	X
18	1	X	X	1		1X	2
19	1	X	X	1		1X	2
Sheet2

Cell Formulas
Range	Formula
H6	=SUBSTITUTE("1X2",I6,"")
I6	=IF(ISNA(MATCH(1,C6:F6,0)),1,"")&IF(ISNA(MATCH("X",C6:F6,0)),"X","")&IF(ISNA(MATCH(2,C6:F6,0)),2,"")

 

[Kishan]

Probably neater ways, but how about

Worksheet Formulas Cell Formula H6 =SUBSTITUTE([COLOR=rgb(255]"1X2",I6,""[/COLOR]) I6 =IF([COLOR=rgb(255]ISNA(MATCH([COLOR=0)]1,C6:F6,0[/COLOR])),1,""[/COLOR])&IF([COLOR=rgb(255]ISNA(MATCH([COLOR=0)]"X",C6:F6,0[/COLOR])),"X",""[/COLOR])&IF([COLOR=rgb(255]ISNA(MATCH([COLOR=0)]2,C6:F6,0[/COLOR])),2,""[/COLOR]) <tbody> </tbody>

<tbody>
</tbody>

Hi Fluff, it is a working as request perfect!!

Thank you for help

Kind Regards,
Kishan

 

[Fluff]

Glad to help & thanks for the feedback

 

[Kishan]

Hi Fluff, sorry to disturb you again, please could you check row 6 & row 20 where are, 4 X’S in the missing side gives the correct result "12", but Unique it should return "X"

	A	B	C	D	E	F	G	H	I	J
1
2
3
4
5			P1	P2	P3	P4		Unique	Missing
6			X	X	X	X		1X2	12
13			X	1	X	X		1X	2
15			X	2	X	1		1X2
16			1	1	1	1		1	X2
17			1	2	1	2		12	X
18			1	X	X	1		1X	2
19			1	X	X	1		1X	2
20			X	X	X	X		1X2	12
21
22

<tbody>
</tbody>

Thank you

Regards,
Kishan

 

[Fluff]

Replace the Col H formula with
=IF(ISNA(MATCH(1,C6:F6,0)),"",1)&IF(ISNA(MATCH("X",C6:F6,0)),"","X")&IF(ISNA(MATCH(2,C6:F6,0)),"",2)

 

[Kishan]

Replace the Col H formula with
=IF(ISNA(MATCH(1,C6:F6,0)),"",1)&IF(ISNA(MATCH("X",C6:F6,0)),"","X")&IF(ISNA(MATCH(2,C6:F6,0)),"",2)

Thank you Fluff, yes this formula did the trick, all is resulting very perfect!!

Kind Regards,
Kishan

 

[Fluff]

Glad to help & thanks for the feedback