Find duplicate in another column and delete that row

[Gopinathak]

I have a different situation. Column A contains the Item No. and Column Contains its link Item No. But the Link Item No. is again coming as Item No. in Column A and its Link Item No. is that which has come in the first instance. I want to find out the First Item No.'s Link Item No. in Column B and delete the row. Normally finding duplicates shows all the numbers in Column B as Duplicate. Sample is provided below:

Item No Link Item No.
123 456
234 567
456 123
345 678
567 234
678 345

In the above sample I want to delete the rows 3,5 & 6.

Any help is appreciated.

[Fotis1991]

2 suggestions. In both of these we use a helper and hidden column. In this column C we use this formula.

=IF(COUNTIF($A$2:B2;A3)+COUNTIF($A$2:B2;B3)>1;1;"")

Then you have 3 options

1) Create a new table using this ARRAY formula.

=IF(ISERROR(INDEX(A$2:A$20;SMALL(IF($C$2:$C$20=1;ROW(A$2:A$20)-1);ROW(A1))));"";INDEX(A$2:A$20;SMALL(IF($C$2:$C$20=1;ROW(A$2:A$20)-1);ROW(A1))))

2) Filter your table according column C OR

3) Delete the rows without 1 in column c

[Gopinathak]

This is what I exactly wanted. It worked fantastically. Thank you so much.

[Fotis1991]

You are welcome!

At last which of the solutions do you prefer?

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.

[Ghozi Alkatiri]

try this array formula
{=IFERROR(INDEX(A$2:A$7,SMALL(IF(MATCH($A$2:$A$7,$A$2:$A$7,0)<MATCH($A$2:$A$7,$B$2:$B$7,0),MATCH($A$2:$A$7,$A$2:$A$7,0)),ROW(1:1))),"")}

drag right and down

[Gopinathak]

Will definitely do in future. Thank you.