Hello, Mr Excel.com team. Thanks to Mr Excel.com I found an excellent solution for identifying uncommon items on lists -> =UNIQUE(TOCOL(A1:B10,,1),,1). Is there a way i can change this function to identify common items on lists in the same format. Extra bonus would be if i can us it for more than 2 lists. Many thanks.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
identify common items on lists
- Thread starter Zakky
- Start date
Rick Rothstein
MrExcel MVP
- Joined
- Apr 18, 2011
- Messages
- 38,472
- Office Version
- 365
- Platform
- Windows
I think this formula will do what you asked for...
=LET(ab,A1:B10,t,TOCOL(ab,,1),UNIQUE(FILTER(t,COUNTIF(ab,t)>1)))
=LET(ab,A1:B10,t,TOCOL(ab,,1),UNIQUE(FILTER(t,COUNTIF(ab,t)>1)))
Upvote
0
Rick, thank you for replying. your good self helped me with the uncommon values formula. This common values formula works amazingly well. Will the UNIQUE formula for uncommon items work on more than 2 lists?
Upvote
0
Rick Rothstein
MrExcel MVP
- Joined
- Apr 18, 2011
- Messages
- 38,472
- Office Version
- 365
- Platform
- Windows
Yes, just get them all into a single array column (using TOCOL if they are contiguous, VSTACK if they are not).
Upvote
0
Similar threads
- Solved
- Question
