Re: intersection

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

"Dave Peterson" <ec35720@xxxxxxxxxxxxxxxxx> wrote in message
news:42C2816C.30ED9925@xxxxxxxxxxxxxxxxxxxx
> 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


.