# List of Duplicate values, from multiple columns

#### Kanuck

##### New Member
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
ABCD
1AppleCococnutDuplicates from Column AShow if duplicated in A & B
2Orange Blackberry Apple
3MangoPeachLemon
4LemonBanana Pineapple
5Grapes CococnutPear
6Cococnut
7Apple
8Lemon
9Starfruit
10Pineapple
11Pear
12Pineapple
13Peach
14Raspberry
15Blueberry
16Pear
17Blackberry
18Banana
19
20
21
22
Sheet1
Cell Formulas
RangeFormula
C2C2=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)),"")
D2D2=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:C9C3=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:D9D3=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.

Kanuck

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### Eric W

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

Book1
ABCD
1AppleCococnutDuplicates from Column AShow if duplicated in A & B
2Orange Blackberry AppleCococnut
3MangoPeachOrange Peach
4LemonBanana MangoBlackberry
5Grapes CococnutLemonBanana
6CococnutGrapes
7AppleCococnut
8LemonStarfruit
9Starfruit Pineapple
10PineapplePear
11PearPeach
12PineappleRaspberry
13PeachBlueberry
14RaspberryBlackberry
15BlueberryBanana
16Pear
17Blackberry
18Banana
Sheet31
Cell Formulas
RangeFormula
C2:C15C2=UNIQUE(A1:A18)
D2:D5D2=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

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

#### Kanuck

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

#### kweaver

##### Well-known Member

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
ABCD
1List1CococnutDuplicates from Column AShow if duplicated in A & B
2AppleBlackberryAppleCococnut
3OrangePeachLemonPeach
4MangoBananaPineappleBlackberry
5LemonCococnutPearBanana
6Grapes
7Cococnut
8Apple
9Lemon
10Starfruit
11Pineapple
12Pear
13Pineapple
14Peach
15Raspberry
16Blueberry
17Pear
18Blackberry
19Banana
Sheet2
Cell Formulas
RangeFormula
D2:D5D2=FILTER(A2:A19,"yes"=IF(ISNA(VLOOKUP(A2:A19,B:B,1,0)),"no","yes"))
C2:C19C2=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

##### New Member
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

##### MrExcel MVP

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
ABCD
1AppleCococnutDuplicates from Column AShow if duplicated in A & B
2Orange Blackberry AppleCococnut
3MangoPeachLemonPeach
4LemonBanana PineappleBlackberry
5Grapes CococnutPearBanana
6Cococnut
7Apple
8Lemon
9Starfruit
10Pineapple
11Pear
12Pineapple
13Peach
14Raspberry
15Blueberry
16Pear
17Blackberry
18Banana
Sheet31
Cell Formulas
RangeFormula
C2:C5C2=UNIQUE(FILTER(A1:A18,COUNTIF(A:A,A1:A18)>1))
D2:D5D2=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

##### New Member
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
ABCD
1AppleCococnutDuplicates from Column AShow if duplicated in A & B
2Orange Blackberry AppleCococnut
3MangoPeachLemonPeach
4LemonBanana PineappleBlackberry
5Grapes CococnutPearBanana
6Cococnut
7Apple
8Lemon
9Starfruit
10Pineapple
11Pear
12Pineapple
13Peach
14Raspberry
15Blueberry
16Pear
17Blackberry
18Banana
Sheet31
Cell Formulas
RangeFormula
C2:C5C2=UNIQUE(FILTER(A1:A18,COUNTIF(A:A,A1:A18)>1))
D2:D5D2=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

##### MrExcel MVP
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

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

Replies
4
Views
57
Replies
6
Views
137
Replies
1
Views
36
Replies
30
Views
912
Replies
4
Views
306

1,148,158
Messages
5,745,108
Members
423,924
Latest member
Gazzat

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back