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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Max

One way:

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

Regards

Trevor
 
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
 
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
 
Hmmm...

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

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

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

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

<vbg>
 
Your lack of vacation is affecting your math skills!
Dave has 2.71 times as much free time as you. <g>
 
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


---
 
Back
Top