# multiple lists intersection

1. ## 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

2. ## 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

3. ## Re: multiple lists intersection

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

4. ## Re: multiple lists intersection

@daffodil11
One column is still enough or I'm missing something?

5. ## Re: multiple lists intersection

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

Drag down as far as needed

6. ## 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. ## Re: multiple lists intersection

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

- Moo

8. ## Re: multiple lists intersection

moo you are the man

thats great

9. ## Re: multiple lists intersection

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

10. ## 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.

- Moo

11. ## Re: multiple lists intersection

just copy paste and remove dupes?

12. ## Re: multiple lists intersection

See my post above

13. ## 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. ## Re: multiple lists intersection

Formula:
`Please Login or Register  to view this content.`

Drag down

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

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