Re: intersection

I think I'd get a consolidated list.

Create a new work*** 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.)

Micro wrote:
>
> 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

--

Dave Peterson
.