Vlookup? and range check

[malbarki]

Dears I need your help

My data is as follow

[TABLE="class: outer_border, width: 400"]
<tbody>[TR]
[TD="align: center"]Name[/TD]
[TD="align: center"]Status[/TD]
[/TR]
[TR]
[TD="align: center"]Pro1[/TD]
[TD="align: center"]Successful[/TD]
[/TR]
[TR]
[TD="align: center"]Pro1[/TD]
[TD="align: center"]Failed[/TD]
[/TR]
[TR]
[TD="align: center"]Pro2[/TD]
[TD="align: center"]Failed[/TD]
[/TR]
[TR]
[TD="align: center"]Pro2[/TD]
[TD="align: center"]Failed[/TD]
[/TR]
[TR]
[TD="align: center"]Pro3[/TD]
[TD="align: center"]Successful[/TD]
[/TR]
[TR]
[TD="align: center"]Pro3[/TD]
[TD="align: center"]Successful[/TD]
[/TR]
[TR]
[TD="align: center"]Pro4[/TD]
[TD="align: center"]Successful[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]If I have Successful and Failed return

Failed​

[/TD]
[/TR]
[TR]
[TD]If I have Failed and Failed return

Failed​

[/TD]
[/TR]
[TR]
[TD]If I have Successful and Successful return

Successful​

[/TD]
[/TR]
[TR]
[TD]If there is no duplication return what ever the status is[/TD]
[/TR]
</tbody>[/TABLE]

​

​

Expected results

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD="align: center"]Name[/TD]
[TD="align: center"]Status[/TD]
[/TR]
[TR]
[TD]Pro1[/TD]
[TD]Failed[/TD]
[/TR]
[TR]
[TD]Pro2[/TD]
[TD]Failed[/TD]
[/TR]
[TR]
[TD]Pro3[/TD]
[TD]Successful[/TD]
[/TR]
[TR]
[TD]Pro4[/TD]
[TD]Successful[/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

[TABLE="width: 821"]
<colgroup><col><col><col span="3"><col><col span="2"><col><col span="2"></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Status[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pro1[/TD]
[TD]Successful[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pro1[/TD]
[TD]Failed[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pro2[/TD]
[TD]Failed[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pro2[/TD]
[TD]Failed[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pro3[/TD]
[TD]Successful[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pro3[/TD]
[TD]Successful[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Count of Status[/TD]
[TD]Status[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pro4[/TD]
[TD]Successful[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]Failed[/TD]
[TD]Successful[/TD]
[TD]Grand Total[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Pro1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Pro2[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Pro3[/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Pro4[/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Grand Total[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]failed[/TD]
[TD]succesful[/TD]
[TD]status[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Pro1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]failed[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Pro2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]failed[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Pro3[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]successful[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Pro4[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]successful[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]a helper pivot and a helper table, which can be hidden,[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]do the business…[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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? ?