Help comparing data in 2 columns and finding same data in both

[Max]

Hi,

I have a 2 column spreadsheet with numerical data in each column.
I need to compare columns and note which numbers are in both columns
(preferably in a third column)

example:
I have this...

col A col B
1001855 1001855
1001855 1001866
1001866 1001877
1001866 1001888
1001877 1001899

I need this....

col A col B col C
1001855 1001855 X
1001855 1001866 X
1001866 1001877 X
1001866 1001888
1001877 1001899


A has over 14,000 lines and B has over 10,000
either may have duplicates.

Suggestions?

Thanks
Max

 

[Trevor Shuttleworth]

Max

One way:

=IF(COUNTIF(A:A,$B1)>0,"X","") and drag down the column

Regards

Trevor

 

[T. Valko]

Enter this formula in C1 and copy down to the end of data in column B:

=IF(ISNUMBER(MATCH(B1,A:A,0)),"x","")

Hint: for fast copying of the formula double click the fill handle (that
little "square" on the bottom right of the selected cell). Double clicking
the fill handle will copy the formula down the column until it reaches an
empty cell in column B.

Biff

 

[Max]

Very interesting.
When I used Biff's formula not all of the common data was found.
When I used Trevor's formula all of the common data appears to have been
found.

Thank you both for your assistance.
Regards
Max

 

[T. Valko]

Hmmm...

At the most basic level, both formulas are essentially the same. The MATCH
formula is faster to calculate.

Biff

 

[Dave Peterson]

Maybe there were "numbers" that were really text???

=match() wouldn't find a match, but =countif() would.

 

[T. Valko]

Yes, I was just getting ready to explain that.

Biff

 

[Dave Peterson]

Oh, sure.

I get one worksheet function question right and you want to take it away from
me!

<vbg>

 

[T. Valko]

I logged in to CDO and voted for your reply:

http://img503.imageshack.us/img503/7329/goodanswerdavevs2.jpg

Don't know why it didn't give you the green checkmark!

Biff

 

[Dave Peterson]

1 out of 1 found it useful! That's close to 100%.

(You have wayyyyyyyy too much time on your hands!)

I logged in to CDO and voted for your reply:

http://img503.imageshack.us/img503/7329/goodanswerdavevs2.jpg

Don't know why it didn't give you the green checkmark!

Biff

 

[T. Valko]

(You have wayyyyyyyy too much time on your hands!)

Yes. Yes I do, but, it looks like you have twice as much as me!

http://img525.imageshack.us/img525/6752/freetimetn3.jpg

I need a vacation!

Biff

 

[Dave Peterson]

You win!

(You have wayyyyyyyy too much time on your hands!)

Yes. Yes I do, but, it looks like you have twice as much as me!

http://img525.imageshack.us/img525/6752/freetimetn3.jpg

I need a vacation!

Biff

 

[Pete_UK]

Hi Biff,

did you compile that table yourself?

Pete

 

[T. Valko]

No. Debra Dalgleish keeps stats:

http://contextures.com/xlngstats.html

Biff

 

[Dave Peterson]

It looks like it comes from Debra Dalgleish's site:
http://contextures.com/xlngstats.html

 

[Debra Dalgleish]

Your lack of vacation is affecting your math skills!
Dave has 2.71 times as much free time as you. <g>

 

[Dave Peterson]

And your vacation to the Bahamas, er, back porch is going well, I hope????

 

[Debra Dalgleish]

Well, my math skills are improving!
Just don't ask about the book.

And your vacation to the Bahamas, er, back porch is going well, I hope????

 

[T. Valko]

Your lack of vacation is affecting your math skills!

That's not all it's affecting!

Biff

 

[Max]

Don't know why it didn't give you the green checkmark!
Think only* original posters can "give" the greens
*perhaps also MVPs if they choose to mark the responses in CDO