Formula to return desired Result
 

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.
--------------------
(MS-Exl-Learner)
--------------------

Formula to return desired Result
 

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

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

If this post helps click Yes
---------------
Jacob Skaria


"MS-Exl-Learner" wrote:

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.
--------------------
(MS-Exl-Learner)
--------------------

Formula to return desired Result
 

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"MS-Exl-Learner" wrote in message
...
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.
--------------------
(MS-Exl-Learner)
--------------------

Formula to return desired Result
 

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)
--------------------



"Jacob Skaria" wrote:

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

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

If this post helps click Yes
---------------
Jacob Skaria


"MS-Exl-Learner" wrote:

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.
--------------------
(MS-Exl-Learner)
--------------------

Formula to return desired Result
 

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



"Bernard Liengme" wrote:

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"MS-Exl-Learner" wrote in message
...
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.
--------------------
(MS-Exl-Learner)
--------------------