# Formula to Extract Duplicates in Two Columns

I've got a large amount of data that I need to analyse. Two of the columns contain values that I need to check for duplicates that will look something like this:

1    A
1    B
1    A
2    A
2    B
2    B
3    C
3    C
3    C
3    A


So the formula run across two columns would ideally spit out:

1    A
1    B
2    A
2    B
3    A
3    C


I've been using the following to extract duplicates from a single column, but am lost when it comes to looking at two columns.

{=INDEX(ImportedData!$C$2:$C$500,MATCH(0,COUNTIF($L$1:L1,ImportedData!$C$2:$C$500),0))}


Unfortunately I want to avoid manipulating the reference data, so it really makes things difficult. I'm thinking of concatenating the two columns and checking that singular column for dupes, but I feel like there's got to be a better way and suspect that doing that may also cause issues down the road.

• copy and paste the columns then use Remove Duplicates it is on the Data tab. Commented Jul 12, 2017 at 19:42
• Use the Advanced Filter with Unique Records Only, Copy to another location. Then sort the results Commented Jul 13, 2017 at 0:51

In C1 enter:

=A1 & B1


and copy downwards. In D1 enter 1, in D2 enter:

=IF(COUNTIF($C$1:C2,C2)>1,"",1+MAX($D$1:D1))


and copy downwards. Column D marks each unique data pair.

In E1 enter:

=IFERROR(INDEX(A:A,MATCH(ROW(),D:D,0)),"")


and copy downwards. In F1 enter:

=IFERROR(INDEX(B:B,MATCH(ROW(),D:D,0)),"")


and copy downwards: