Formula to return desired Result

[MS-Exl-Learner]

Hi,

I need a formula that should check the column A & B, Column A consist
Duplicate Names, but the value in B column for the duplicate Names will
differ. Now i just want to get if duplicate names are numbers then i need the
result in column C stating that "NUMBERS". if duplicate names are numbers &
ST then it should give the result as "NUMBERS & ST", If the duplicate values
contains only ST then it should return the result as "ST". Finally if the (A
Column) Name does not contain any duplicate value, then it should give the
result of "ST" or "NUMBER" depends on the B column Value (ex. abbbb)
The expected answers are given in C Column for ur reference

A B C
NAMES DETAILS STATUS
cat ST NUMBERS & ST
cat 12354 NUMBERS & ST
Lion 321325 NUMBERS
rat ST ST
rat ST ST
tiger 6548 NUMBERS
tiger 6548 NUMBERS
elephant ST NUMBERS & ST
elephant 5466 NUMBERS & ST

Thanks.

 

[Jacob Skaria]

One way...In D2; try the below and copy down

=IF(SUMPRODUCT(($A$2:$A$10=A2)*(ISNUMBER($B$2:$B$10)))=0,"ST",IF(SUMPRODUCT(($A$2:$A$10=A2)*(ISNUMBER($B$2:$B$10)))=SUMPRODUCT(--($A$2:$A$10=A2),--($B$2:$B$10<>"")),"NUMBERS","NUMBERS & ST"))

If this post helps click Yes

 

[Bernard Liengme]

Try this
=CHOOSE(SUMPRODUCT(($A$2:$A$10=A2)*($B$2:$B$10="ST"))+1,"Numbers","Numbers &
ST","ST")
best wishes

 

[MS-Exl-Learner]

Thank you Very Much Jacob.... I got the exact formula from you which i am
searching
for the past 5 days, so once again thank you...

Pls keep on helping us....

 

[MS-Exl-Learner]

Wow! this is also another one good method! Thank you for spending your time
for replying my query. Thank u experts :~)
--------------------
(MS-Exl-Learner)
--------------------

Try this
=CHOOSE(SUMPRODUCT(($A$2:$A$10=A2)*($B$2:$B$10="ST"))+1,"Numbers","Numbers &
ST","ST")
best wishes