Matching Two Columns with Many rows
 

Hello all,

I have never used this posting system before and I considered myself an
advanced Excel user before reading some of the posts and their answers on
this site. I have never used VBA before or any Macros, so please keep that
in mind if answering.

Question:

I have two columns of data (A and B). Each has close to 1000 rows. I need
to know which values appear in both columns. That's most important. I would
also like to know which values are exclusively in Column A or B, but that
would be secondary.

Thank you in advance.

Matching Two Columns with Many rows
 

Hi Srbanksphd

See Chip Pearson's duplicates pages for a variety of techniques:

http://www.cpearson.com/excel/duplic...tingDuplicates


---
Regards,
Norman


"srbanksphd" wrote in message
...
Hello all,

I have never used this posting system before and I considered myself an
advanced Excel user before reading some of the posts and their answers on
this site. I have never used VBA before or any Macros, so please keep
that
in mind if answering.

Question:

I have two columns of data (A and B). Each has close to 1000 rows. I
need
to know which values appear in both columns. That's most important. I
would
also like to know which values are exclusively in Column A or B, but that
would be secondary.

Thank you in advance.

Matching Two Columns with Many rows
 

I'd insert a new column B and a new column D.

So the original data is in column A and column C.

Then in B1, put this formula:
=if(isnumber(match(a1,c:c,0)),"In column C","Not in column C")
then drag down.

And a similar formula in column D:
=if(isnumber(match(c1,a:a,0)),"In column A","Not in column A")

========

In fact, you may want to try this.

Start a new worksheet
copy your data in column A to column A of that new worksheet
copy your data in column B to the bottom of column A of that new worksheet
(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

Delete column A (we're done with it)

Add headers in B1 and C1:
B1: It's in column A
C1: It's in column B

Now put this in B2.
=isnumber(match(a2,sheet1!a:a,0))
put this in C2:
=isnumber(match(a2,sheet1!b:b,0))

Select B2:C2
and drag down.

You can filter on each of those columns to see what you want.
You'l


srbanksphd wrote:

Hello all,

I have never used this posting system before and I considered myself an
advanced Excel user before reading some of the posts and their answers on
this site. I have never used VBA before or any Macros, so please keep that
in mind if answering.

Question:

I have two columns of data (A and B). Each has close to 1000 rows. I need
to know which values appear in both columns. That's most important. I would
also like to know which values are exclusively in Column A or B, but that
would be secondary.

Thank you in advance.

--

Dave Peterson

Matching Two Columns with Many rows
 

Hi,
Highlight the range from A1 to A1000 and do conditional formatting. Select
the option Formula is and type the following

=COUNTIF(B:B,A1)0

Format the cells with say yellow background. Click on OK.

Similarly do the conditional formatting form B1 to B1000. The formula there
will be

=COUNTIF(A:A,B1)0

The cells which do not turn yellow are unique. Those that have a yellow
background are in the other column.

Alok



"srbanksphd" wrote:

Hello all,

I have never used this posting system before and I considered myself an
advanced Excel user before reading some of the posts and their answers on
this site. I have never used VBA before or any Macros, so please keep that
in mind if answering.

Question:

I have two columns of data (A and B). Each has close to 1000 rows. I need
to know which values appear in both columns. That's most important. I would
also like to know which values are exclusively in Column A or B, but that
would be secondary.

Thank you in advance.