Identifying Duplicate data between 2 columns

  • Thread starter Thread starter Naja
  • Start date Start date
N

Naja

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. Data>Filter>Advanced Filter>Copy 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
 
Enter this formula in C2 and copy down as needed:

=IF(COUNTIF(B$2:B2,B2)=1,"Unique","Duplicate")
 
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","unique")

In 2003 try:

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