+ Reply to Thread
Results 1 to 14 of 14

multiple lists intersection

  1. #1
    Registered User
    Join Date
    09-04-2013
    Location
    nyc
    MS-Off Ver
    Excel 2010
    Posts
    38

    multiple lists intersection

    INTERSECTION_FORUM_HELP.xlsx

    hi guys

    lets say i have 5 lists

    how do i find an intersection of those lists i.e. what values are present in all 5

    this is just a sample sheet, m actual file is very large to i cant do it manually

    thanks in advance

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: multiple lists intersection

    You can create a duplicate table right next to it that filters the matching values.

    In G1:

    =IF(COUNTIF($E$1:$E$30,A1)+COUNTIF($D$1:$D$30,A1)+COUNTIF($C$1:$C$30,A1)+COUNTIF($B$1:$B$30,A1)+COUNTIF($A$1:$A$30,A1)=5,A1,"")

    And then copy over and down.

    INTERSECTION_FORUM_HELPer.xlsx
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    09-04-2013
    Location
    nyc
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: multiple lists intersection

    thanks for the response, but i ultimately need just one column that has all the intersections-

  4. #4
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,959

    Re: multiple lists intersection

    @daffodil11
    One column is still enough or I'm missing something?
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  5. #5
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: multiple lists intersection

    you could use this :
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Drag down as far as needed
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  6. #6
    Registered User
    Join Date
    09-04-2013
    Location
    nyc
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: multiple lists intersection

    change the first two rows to

    1 2 2 2 1
    2 4 6 6 2


    it wont pick up 2 as an intersection

  7. #7
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,842

    Re: multiple lists intersection

    See my attached sheet to see if it is something that will work.

    - Moo
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    09-04-2013
    Location
    nyc
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: multiple lists intersection

    moo you are the man

    thats great

  9. #9
    Registered User
    Join Date
    09-04-2013
    Location
    nyc
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: multiple lists intersection

    one question, how did you get the unique values in column g

  10. #10
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,842

    Re: multiple lists intersection

    To get the unique values, I just pasted the data from columns B, C, D and E below the data already in column A, then used Excel's 'Advanced Filter: Unique Values' feature and copied them to column G.

    Glad to help!

    - Moo
    Last edited by Moo the Dog; 11-13-2013 at 05:48 PM.

  11. #11
    Registered User
    Join Date
    09-04-2013
    Location
    nyc
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: multiple lists intersection

    just copy paste and remove dupes?

  12. #12
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,842

    Re: multiple lists intersection

    See my post above

  13. #13
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: multiple lists intersection

    You are right, mine doesnt work for the scenario you showed, as I hadn't realized you had two 2's in E column...sorry about that

  14. #14
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: multiple lists intersection

    I offer this instead :
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Drag down

    Sorry about the first one
    Last edited by dredwolf; 11-13-2013 at 06:35 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 07-16-2013, 06:41 PM
  2. Index / Match to find value at point of intersection across multiple sheets
    By Pipes Chettiar in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-19-2012, 08:32 AM
  3. Intersection - Multiple ranges SUDDENLY not supported
    By Geronim0 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-22-2011, 08:16 AM
  4. Intersection - Multiple ranges SUDDENLY not supported
    By Geronim0 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-22-2011, 07:45 AM
  5. Intersection of two lists
    By Mary M in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-20-2007, 10:37 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1