Vlookup? and range check

[malbarki]

Dears I need your help

My data is as follow

Name	Status
Pro1	Successful
Pro1	Failed
Pro2	Failed
Pro2	Failed
Pro3	Successful
Pro3	Successful
Pro4	Successful

<tbody>
</tbody>

And what I want is retrieve the names without duplication and this criteria for the status

If I have Successful and Failed return Failed​

If I have Failed and Failed return Failed​

If I have Successful and Successful return Successful​

If there is no duplication return what ever the status is

<tbody>
</tbody>

​

​

Expected results

Name	Status
Pro1	Failed
Pro2	Failed
Pro3	Successful
Pro4	Successful

<tbody>
</tbody>

 

[oldbrewer]

Name	Status
Pro1	Successful
Pro1	Failed
Pro2	Failed
Pro2	Failed
Pro3	Successful
Pro3	Successful				Count of Status	Status
Pro4	Successful				Name	Failed	Successful	Grand Total
					Pro1	1	1	2
					Pro2	2		2
					Pro3		2	2
					Pro4		1	1
					Grand Total	3	4	7
						failed	succesful	status
					Pro1	1	1	failed
					Pro2	1		failed
					Pro3		1	successful
					Pro4		1	successful
					a helper pivot and a helper table, which can be hidden,
					do the business…

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

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F
1	Name	Status			Name	Status
2	Pro1	Successful			Pro1	Failed
3	Pro1	Failed			Pro2	Failed
4	Pro2	Failed			Pro3	Successful
5	Pro2	Failed			Pro4	Successful
6	Pro3	Successful
7	Pro3	Successful
8	Pro4	Successful
Sheet1

In E2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$8,SMALL(IF(FREQUENCY(IF($A$2:$A$8<>"",MATCH($A$2:$A$8,$A$2:$A$8,0)),ROW($A$2:$A$8)-ROW($A$2)+1),ROW($A$2:$A$8)-ROW($A$2)+1),ROWS($E$2:E2))),"")

In F2 just enter and copy down:

=IF($E2="","",IF(COUNTIFS($A$2:$A$8,$E2,$B$2:$B$8,"<>"),IF(COUNTIFS($A$2:$A$8,$E2,$B$2:$B$8,"failed"),"Failed","Successful"),"no data"))

 

[Phuoc]

If you have Failed and Successful, what is the result? ?