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
    copy and paste the columns then use Remove Duplicates it is on the Data tab. – Scott Craner Jul 12 '17 at 19:42
  • Use the Advanced Filter with Unique Records Only, Copy to another location. Then sort the results – Ron Rosenfeld Jul 13 '17 at 0:51
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

|improve this answer|||||
  • Had to make a tiny adjustment of adding -1 to Row() to account for headers, but worked wonderfully. Thanks! – Tock Jul 13 '17 at 13:59
  • @Tock you are quite welcome! – Gary's Student Jul 13 '17 at 14:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.