I have the following table and I am having trouble deriving a formula for my
answer:

Store # Item #
#0111 ADSD200.21SP
#0111 ADSD200.22SP
#0111 ADSD200SP.21.12
#0111 ADSD200SP.21.12
#0111 V29K10007.7SP
#0111 V29K10007.7SP
#0119 OG313
#0119 OG313
#0202 CF101-S
#0202 CF101-S

I would like my answer to look like this:
Store # Item #
#0111 ADSD200.21SP Unique
#0111 ADSD200.22SP Unique
#0111 ADSD200SP.21.12 Unique
#0111 ADSD200SP.21.12 Duplicate
#0111 V29K10007.7SP Unique
#0111 V29K10007.7SP Duplicate
#0119 OG313 Unique
#0119 OG313 Duplicate
#0202 CF101-S Unique
#0202 CF101-S Duplicate

Is this possible? Please help.

From your data it looks like ColB is sorted...With your data in ColA and ColB
with headers in row1 ;
in C2 enter the below formula and copy that down
=IF(COUNTIF($B$2:B2,B2)=1,"Unique","Duplicate")

If you want to generate a unique list use the Autofilter option

1. Select the range in Col A and ColB including the header.
2. DataFilterAdvanced FilterCopy to another location
3. In copy to I have selected D1 and check 'Unique records only'
4. Click OK will give you the unique list..



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


"Naja" wrote:

I have the following table and I am having trouble deriving a formula for my
answer:

Store # Item #
#0111 ADSD200.21SP
#0111 ADSD200.22SP
#0111 ADSD200SP.21.12
#0111 ADSD200SP.21.12
#0111 V29K10007.7SP
#0111 V29K10007.7SP
#0119 OG313
#0119 OG313
#0202 CF101-S
#0202 CF101-S

I would like my answer to look like this:
Store # Item #
#0111 ADSD200.21SP Unique
#0111 ADSD200.22SP Unique
#0111 ADSD200SP.21.12 Unique
#0111 ADSD200SP.21.12 Duplicate
#0111 V29K10007.7SP Unique
#0111 V29K10007.7SP Duplicate
#0119 OG313 Unique
#0119 OG313 Duplicate
#0202 CF101-S Unique
#0202 CF101-S Duplicate

Is this possible? Please help.

Enter this formula in C2 and copy down as needed:

=IF(COUNTIF(B$2:B2,B2)=1,"Unique","Duplicate")

--
Biff
Microsoft Excel MVP


"Naja" wrote in message
...
I have the following table and I am having trouble deriving a formula for
my
answer:

Store # Item #
#0111 ADSD200.21SP
#0111 ADSD200.22SP
#0111 ADSD200SP.21.12
#0111 ADSD200SP.21.12
#0111 V29K10007.7SP
#0111 V29K10007.7SP
#0119 OG313
#0119 OG313
#0202 CF101-S
#0202 CF101-S

I would like my answer to look like this:
Store # Item #
#0111 ADSD200.21SP Unique
#0111 ADSD200.22SP Unique
#0111 ADSD200SP.21.12 Unique
#0111 ADSD200SP.21.12 Duplicate
#0111 V29K10007.7SP Unique
#0111 V29K10007.7SP Duplicate
#0119 OG313 Unique
#0119 OG313 Duplicate
#0202 CF101-S Unique
#0202 CF101-S Duplicate

Is this possible? Please help.

Hi,

First, if your data is sorted as shown on column B then

=IF(COUNTIF(B$2:B2,B2)1,"duplicates","unique")
however, shorter would be
=IF(B2=B1,"duplicate","unique")

=====
But from your subject line it looks like you want to define uniques as based
on two columns A and B, if so try:

In 2007 try:

=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)1,"duplicate","u nique")

In 2003 try:

=IF(SUMPRODUCT((A$2:A2=A2)*(B$2:B2=B2))1,"duplica te","unique")
also you can use my earlier suggestion if both columns are sorted
=IF(SUMPRODUCT((A2=A1)*(B2=B1))0,"duplicate","uni que")


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Naja" wrote:

I have the following table and I am having trouble deriving a formula for my
answer:

Store # Item #
#0111 ADSD200.21SP
#0111 ADSD200.22SP
#0111 ADSD200SP.21.12
#0111 ADSD200SP.21.12
#0111 V29K10007.7SP
#0111 V29K10007.7SP
#0119 OG313
#0119 OG313
#0202 CF101-S
#0202 CF101-S

I would like my answer to look like this:
Store # Item #
#0111 ADSD200.21SP Unique
#0111 ADSD200.22SP Unique
#0111 ADSD200SP.21.12 Unique
#0111 ADSD200SP.21.12 Duplicate
#0111 V29K10007.7SP Unique
#0111 V29K10007.7SP Duplicate
#0119 OG313 Unique
#0119 OG313 Duplicate
#0202 CF101-S Unique
#0202 CF101-S Duplicate

Is this possible? Please help.