Sorting out of many similar

[soumen21]

Dear Experts,

I have a some data as shown below

[TABLE="width: 345"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Part[/TD]
[TD]Manufacturer[/TD]
[TD]Status[/TD]
[TD]Code[/TD]
[/TR]
[TR]
[TD]123-456[/TD]
[TD]AA[/TD]
[TD]Obsolete[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]123-456[/TD]
[TD]BB[/TD]
[TD]Active[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]123-456[/TD]
[TD]CC[/TD]
[TD]Active[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]123-456[/TD]
[TD]DD[/TD]
[TD]Active[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]123-456[/TD]
[TD]AA[/TD]
[TD]Active[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]123-456[/TD]
[TD]BB[/TD]
[TD]Active[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

I want to look for part 123-456 and if any of the part among the many listed is active, I want to display the part 123-456 as 'active' in a cell with code smallest of all in another cell else Obsolete with code 6.

 

[Fluff]

How about

Book1
	A	B	C	D	E	F
1	Part	Manufacturer	Status	Code		123-456
2	123-456	AA	Obsolete	6		Active 2
3	123-456	BB	Active	3
4	123-456	CC	Active	2
5	123-458	DD	Active	3
6	123-459	AA	Active	3
7	123-460	BB	Active	3
Watched

Cell Formulas
Range	Formula
F2	=IFERROR("Active "&AGGREGATE(15,6,$D$2:$D$7/(($A$2:$A$7=$F$1)*($C$2:$C$7="Active")),1),"Obsolete 6")

 

[soumen21]

Thanks a lot. Appreciate your help.

 

[Fluff]

My pleasure & thanks for the feedback