List of Duplicate values, from multiple columns

[Kanuck]

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?

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

 

[Eric W]

If you have Excel 365, you probably have the new dynamic array functions. If so, the formulas are quite easy:

Book1
	A	B	C	D
1	Apple	Cococnut	Duplicates from Column A	Show if duplicated in A & B
2	Orange	Blackberry	Apple	Cococnut
3	Mango	Peach	Orange	Peach
4	Lemon	Banana	Mango	Blackberry
5	Grapes	Cococnut	Lemon	Banana
6	Cococnut		Grapes
7	Apple		Cococnut
8	Lemon		Starfruit
9	Starfruit		Pineapple
10	Pineapple		Pear
11	Pear		Peach
12	Pineapple		Raspberry
13	Peach		Blueberry
14	Raspberry		Blackberry
15	Blueberry		Banana
16	Pear
17	Blackberry
18	Banana
Sheet31

Cell Formulas
Range	Formula
C2:C15	C2	=UNIQUE(A1:A18)
D2:D5	D2	=UNIQUE(FILTER(A1:A18,ISNUMBER(MATCH(A1:A18,B1:B5,0))))
Dynamic array formulas.

If not, let me know and I'll use the old-fashioned formulas.

 

[Kanuck]

Hi thank you, was exactly what I needed. Works great! cool formula, didn't know about that one.

 

[Kanuck]

Curiosity though, what would it look like the old way?

 

[kweaver]

Kanuck: I thought you didn't want a list of UNIQUE values in column C but want those that were duplicated.

I like Eric's shorter approach to column D. I tried another way:

Book1
	A	B	C	D
1	List1	Cococnut	Duplicates from Column A	Show if duplicated in A & B
2	Apple	Blackberry	Apple	Cococnut
3	Orange	Peach	Lemon	Peach
4	Mango	Banana	Pineapple	Blackberry
5	Lemon	Cococnut	Pear	Banana
6	Grapes
7	Cococnut
8	Apple
9	Lemon
10	Starfruit
11	Pineapple
12	Pear
13	Pineapple
14	Peach
15	Raspberry
16	Blueberry
17	Pear
18	Blackberry
19	Banana
Sheet2

Cell Formulas
Range	Formula
D2:D5	D2	=FILTER(A2:A19,"yes"=IF(ISNA(VLOOKUP(A2:A19,B:B,1,0)),"no","yes"))
C2:C19	C2	=IFERROR(INDEX(A$2:A$19,MATCH(1,((COUNTIF(C$1:C1,A$2:A$19)=0)*(COUNTIF(A$2:A$19,A$2:A$19)>=2)),0)),"")
Dynamic array formulas.

 

[Kanuck]

Thank you kweaver, you are correct, but D column was the solution I needed, I had solved column C in my initial post there. Yours works as well for C though
Also thank you for your solutions to D, that works as well.

 

[Eric W]

I'm glad we could help! These functions are still relatively new, and people are figuring out ways to use them. Here's another way using COUNTIF:

Book1
	A	B	C	D
1	Apple	Cococnut	Duplicates from Column A	Show if duplicated in A & B
2	Orange	Blackberry	Apple	Cococnut
3	Mango	Peach	Lemon	Peach
4	Lemon	Banana	Pineapple	Blackberry
5	Grapes	Cococnut	Pear	Banana
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
Sheet31

Cell Formulas
Range	Formula
C2:C5	C2	=UNIQUE(FILTER(A1:A18,COUNTIF(A:A,A1:A18)>1))
D2:D5	D2	=UNIQUE(FILTER(A1:A18,COUNTIF(B:B,A1:A18)))
Dynamic array formulas.

These are shorter, and it's interesting (to me anyway) that they are very nearly the same.

 

[Kanuck]

I'm glad we could help! These functions are still relatively new, and people are figuring out ways to use them. Here's another way using COUNTIF:

Book1
	A	B	C	D
1	Apple	Cococnut	Duplicates from Column A	Show if duplicated in A & B
2	Orange	Blackberry	Apple	Cococnut
3	Mango	Peach	Lemon	Peach
4	Lemon	Banana	Pineapple	Blackberry
5	Grapes	Cococnut	Pear	Banana
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
Sheet31

Cell Formulas
Range	Formula
C2:C5	C2	=UNIQUE(FILTER(A1:A18,COUNTIF(A:A,A1:A18)>1))
D2:D5	D2	=UNIQUE(FILTER(A1:A18,COUNTIF(B:B,A1:A18)))
Dynamic array formulas.

These are shorter, and it's interesting (to me anyway) that they are very nearly the same.

Agree, thanks for this too Eric. If still interesting, would this look the same if you were comparing more than two columns for duplicates? In the current example, I believe that is saying just look against column A for duplicates in column B, but what if there was a column C & D that also had data.. that we wanted to see if D & B had duplicates, or C & A etc.

 

[Eric W]

Sure this could easily be extended to multiple columns. You'd end up doing some Boolean logic to handle cases like, I want a list of all values in A that exist in B AND C, or a list of all values in A that exist in B OR C.

This would work for the first case:

=UNIQUE(FILTER(A1:A18,COUNTIF(B:B,A1:A18)*COUNTIF(C:C,A1:A18)))

and for the second case

=UNIQUE(FILTER(A1:A18,COUNTIF(B:B,A1:A18)+COUNTIF(C:C,A1:A18)))

 

[Kanuck]

Very nice , this new functions got some swagger. Way easier now !