Hello,
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?
Thank you in advance.
Kanuck
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