Sorting out of many similar

[soumen21]

Dear Experts,

I have a some data as shown below

Part	Manufacturer	Status	Code
123-456	AA	Obsolete	6
123-456	BB	Active	3
123-456	CC	Active	2
123-456	DD	Active	3
123-456	AA	Active	3
123-456	BB	Active	3

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

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