List of Duplicate values, from multiple columns

Kanuck

New Member
Joined
Jun 8, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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.


Thank you in advance.
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
Joined
Aug 18, 2015
Messages
11,683
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.
 
Solution

Kanuck

New Member
Joined
Jun 8, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Hi thank you, was exactly what I needed. Works great! cool formula, didn't know about that one.
 

Kanuck

New Member
Joined
Jun 8, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Curiosity though, what would it look like the old way?
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,137
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

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
Joined
Jun 8, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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
Joined
Aug 18, 2015
Messages
11,683

ADVERTISEMENT

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
Joined
Jun 8, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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
Joined
Aug 18, 2015
Messages
11,683
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
Joined
Jun 8, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Very nice , this new functions got some swagger. Way easier now !
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top