In the A & B columns there there is values that are duplicated. I want to see a list of those duplicated items.

I am able to see the duplicated items if just search A column, but I want to see a list of duplicate items that show in both A & B.

How do I get that to work?

list of duplicate values.xlsx | ||||||
---|---|---|---|---|---|---|

A | B | C | D | |||

1 | Apple | Cococnut | Duplicates from Column A | Show if duplicated in A & B | ||

2 | Orange | Blackberry | Apple | |||

3 | Mango | Peach | Lemon | |||

4 | Lemon | Banana | Pineapple | |||

5 | Grapes | Cococnut | Pear | |||

6 | Cococnut | |||||

7 | Apple | |||||

8 | Lemon | |||||

9 | Starfruit | |||||

10 | Pineapple | |||||

11 | Pear | |||||

12 | Pineapple | |||||

13 | Peach | |||||

14 | Raspberry | |||||

15 | Blueberry | |||||

16 | Pear | |||||

17 | Blackberry | |||||

18 | Banana | |||||

19 | ||||||

20 | ||||||

21 | ||||||

22 | ||||||

Sheet1 |

Cell Formulas | ||
---|---|---|

Range | Formula | |

C2 | C2 | =IFERROR(INDEX(A$1:A$18,MATCH(1, ((COUNTIF(C1:C$1,A$1:A$18)=0)*(COUNTIF(A$1:A$18,A$1:A$18)>=2)),0)),"") |

D2 | D2 | =IFERROR(INDEX(A$1:B$19,MATCH(1, ((COUNTIF(D1:D$1,A$1:B$19)=0)*(COUNTIF(A$1:B$19,A$1:B$19)>=2)),0)),"") |

C3:C9 | C3 | =IFERROR(INDEX(A$1:A$18,MATCH(1, ((COUNTIF(C$1:C2,A$1:A$18)=0)*(COUNTIF(A$1:A$18,A$1:A$18)>=2)),0)),"") |

D3:D9 | D3 | =IFERROR(INDEX(A$1:B$19,MATCH(1, ((COUNTIF(D$1:D2,A$1:B$19)=0)*(COUNTIF(A$1:B$19,A$1:B$19)>=2)),0)),"") |

Press CTRL+SHIFT+ENTER to enter array formulas. |

Thank you in advance.

Kanuck