0

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.

2
  • 2
    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

1 Answer 1

0

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:

enter image description here

2
  • Had to make a tiny adjustment of adding -1 to Row() to account for headers, but worked wonderfully. Thanks!
    – Tock
    Commented Jul 13, 2017 at 13:59
  • @Tock you are quite welcome! Commented Jul 13, 2017 at 14:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .