intersection

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.)

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

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@netscapeXSPAM.com> wrote in message
news:42C2816C.30ED9925@netscapeXSPAM.com...
> 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.)
>
> 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

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.

Micro wrote:
>
> 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@netscapeXSPAM.com> wrote in message
> news:42C2816C.30ED9925@netscapeXSPAM.com...
> > 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.)
> >
> > 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

--

Dave Peterson