LinkBack URL
About LinkBacks
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
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
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!
thanks for the response, but i ultimately need just one column that has all the intersections-
@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
you could use this :
Formula:
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
change the first two rows to
1 2 2 2 1
2 4 6 6 2
it wont pick up 2 as an intersection
See my attached sheet to see if it is something that will work.
- Moo
moo you are the man
thats great
one question, how did you get the unique values in column g
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.
just copy paste and remove dupes?
See my post above
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
I offer this instead :
Formula: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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks