Intersect operations
 

Dear Community,

I hope you can help...

I have 2 MS Excel 1 column tables. I would like to perform an
'Intersect Operation' on both tables, thus creating one table of
entries which appear in both of the original tables.

EG. I want to create tableC = tableA AND tableB

NOTE: I do not want to simply have the data of both tables joined
together. This is a Union operation. I want data which appears in
both A and B. NOT A or B.

I understand a spreadsheet is not a database, but surely this is
possible in MS Excel... if not, can anyone think of a speedy
alternative???


--
heyes
------------------------------------------------------------------------
heyes's Profile: http://www.excelforum.com/member.php...o&userid=31506
View this thread: http://www.excelforum.com/showthread...hreadid=511839

Intersect operations
 

How about a little work.

Start a new worksheet
copy the data from sheet1 to A1 of the new sheet
copy the data from Sheet2 under that list in column A of the new sheet.
(include a single header row in row 1)

Now you have a giant list in column A--but it may have duplicates.

Select column A of that new worksheet.
Data|Filter|advanced filter
copy to another location (Use B1 of that new sheet)
Check unique records only

(Debra Dalgleish has some notes at:
http://contextures.com/xladvfilter01.html)

Now column B contains a unique list based on both sheets.

Delete column A.

In B1, put: On Sheet1
In C1, Put: On Sheet2

In B2:Bxxx, put:
=isnumber(match(a2,sheet1!a:a,0))

in c2:Cxxx, put:
=isnumber(match(a2,sheet2!a:a,0))

Apply data|filter|autofilter to columns A:C.

Show only the Trues in column B and the Trues in column C.
and copy to a new location

or
show false in column b and delete those rows
then show false in column c and delete those rows

heyes wrote:

Dear Community,

I hope you can help...

I have 2 MS Excel 1 column tables. I would like to perform an
'Intersect Operation' on both tables, thus creating one table of
entries which appear in both of the original tables.

EG. I want to create tableC = tableA AND tableB

NOTE: I do not want to simply have the data of both tables joined
together. This is a Union operation. I want data which appears in
both A and B. NOT A or B.

I understand a spreadsheet is not a database, but surely this is
possible in MS Excel... if not, can anyone think of a speedy
alternative???

--
heyes
------------------------------------------------------------------------
heyes's Profile: http://www.excelforum.com/member.php...o&userid=31506
View this thread: http://www.excelforum.com/showthread...hreadid=511839

--

Dave Peterson

Intersect operations
 

heyes,

Since you are using set-theoretic terminology, I assume that the two
sets will not contain duplicates. If so, and you want to do it with
formulas, assume that set 1 is in A1:A11 and set 2 is in B1:B5. The
following formulas will produce the intersection in column D:D,
starting from D1 (no error checking for empty intersection).

in D1: (array formula, to be commited with Shift+Ctrl+Enter)
=INDEX(A1:A11,MIN(IF(ISNUMBER(MATCH(A1:A11,B1:B5,0 )),ROW(A1:A11))))

in D2: (also array formula)
=INDEX($A$1:$A$11, MATCH(1,(COUNTIF($D$1:D1,
$A$1:$A$11&"")=0)*ISNUMBER(MATCH($A$1:$A$11,$B$1:$ B$5,0)),0))

Copy D2 down until you see #N/A

HTH
Kostis Vezerides

Intersect operations
 

Very Clever!

Thank you very much! That's perfect ;)


--
heyes
------------------------------------------------------------------------
heyes's Profile: http://www.excelforum.com/member.php...o&userid=31506
View this thread: http://www.excelforum.com/showthread...hreadid=511839

Intersect operations
 

Glad to know it worked for you. It was in my to-do list to produce a
formula for this anyway :)

Regards

Kostis Vezerides