intersection

  • Thread starter Thread starter Micro
  • Start date Start date
M

Micro

I have a problem with Excel, I have to do an intersection between 4 columns.
In every column I have a list and my problem is to select the entries in
common il all 4 columns.
To solve this problem I thought to something similiar to the intersect
operator.
How can I do it? I searched in the function but "intersect" operator isn't
present.
Can anyone help me?

thank you in advance

Pietro
 
I think I'd get a consolidated list.

Create a new worksheet and all the lists into column A.
Include only one header row at the top.

Now use Data|Filter|Advanced filter to eliminat duplicates.
You can see detailed instructions at Debra Dalgleish's site:
http://www.contextures.com/xladvfilter01.html#FilterUR

Put that unique list into column B and delete column A.

Now you can use a formula to check which values are in all the columns.

Since there's a header in A1, put this in B2 and copy down.

=isnumber(match(a2,sheet1!a:a,0)) & "." &
isnumber(match(a2,sheet1!b:b,0)) & "." &
isnumber(match(a2,sheet1!c:c,0)) & "." &
isnumber(match(a2,sheet1!d:d,0))

(all one cell.)

Modify sheet1 to be the sheet that contains the 4 columns and adjust the
columns. (I used sheet1 and columns A:D.)

After you do this, you'll see true.true.true.true if that value occurs in all 4
columns.

So add a header to B1 and then apply data|Filter|autofilter to that column.

Filter to show just the true.true.true.true's.

(Or even use a custom filter that contains False and show those mismatches.
Then you can delete those visible rows.)
 
But when I apply the function

"=isnumber(match(a2,sheet1!a:a,0)) & "." &
isnumber(match(a2,sheet1!b:b,0)) & "." &
isnumber(match(a2,sheet1!c:c,0)) & "." &
isnumber(match(a2,sheet1!d:d,0))"

It gives to me always "true,true,true,true", even fi this function is
applied on a value missed in some column...
Why?
I think I fail in some steps.
can you help me?

thank you

Pietro
 
Sheet1 is the sheet with the original data, right?

And columns A:D are those original columns?

If no, then make sure you change those sheetnames/column references.

And you get all true's when you copy down???

If yes, make sure tools|options|Calculation tab is set for automatic.
 
Back
Top