Comparing 2 colums of data

[Sara_UK]

Hi
This is prob a daft question, and I know there must be an easy way to do it -

I've got a simple set of data...2 long lists (about 3000 items per list), just gene names - and all I want to do is to find which gene names are common to them both, and which are unique to each individual list. So far I'm just copying, pasting and finding each one..v tedious and taking forever (done about 1000 in about an hour) - I know there will be an easy way - anyone help me??

thanks
Sara

[Lyriclover]

Hey, you might wanna try something like this...

A B C
1 Gene1 Gene2 Unique?
2 red black 1
3 blue white 0
4 green red 0
5 yellow orange 1
6 purple yellow 0
7 pink taupe 0

In this example I've used the following calculation in the Unique column (just copy the formula down):
=COUNTIF($B$2:$B$7,A2)

If = 0, then unique to that list, if = 1, then not unique. You'd have to do this for both columns though...

[Sara_UK]

I just tried that, and it doens't seem to do waht I want?
Unless I didn't make myself clear...

e.g
Column A
YAL001C
YAL004W
YAL005C
YAL007C
YAL011W
YAL015C
YAL016W
YAL017W
YAL021C
YAL023C
YAL025C
YAL026C
YAL028W
YAL035C-A
YAL051W
YAL060W
YAL064C-A
YAL066W
YAR007C
YAR014C
YAR019C
YAR020C
YAR030C

Column B
YAL004W
YAL011W
YAL012W
YAL015C
YAL016W
YAL017W
YAL021C
YAL024C
YAL025C
YAL030W
YAL032C
YAL033W
YAL035C-A
YAL038W
YAL045C
YAL047C
YAL048C
YAL053W
YAL054C
YAL056W
YAL058C-A
YAL061W
YAL062W

for examples, YAL004W is found in both A and B (this is mainly what I need, I'm just after values at the moment, so if I know how many are in both columsn, i can just subtract that from the total to give me th number unique to each column).
Should your formula do that for me then/
Thanks for all your help!
Sara

[Lyriclover]

I believe that these formulas will do what you need. Check out the attachment. Just modify the cell references accordingly. If you're still having troubles, let me know.

[samprince]

Use Countif For Sure.

If your Data is in A&B, in C2 use:

=COUNTIF(B:B,A2) & Fill Down.

Will Give a 1 Against Each Value in Column A that is present in Column B
at the bottom of the list use:
=(COUNT(C2:Cx))-COUNTIF(C2:Cx,">0") to give the number of uniques in Column B.

in D2 use:

=COUNTIF(A:A,B3) & Fill Down.
Will Give a 1 Against Each Value in Column B that is present in Column A
at the bottom of the list use:
=(COUNT(D2:Dx))-COUNTIF(D2:Dx,">0") to give the number of uniques in Column A.


Note: Change x to whatever your array is.

[Sara_UK]

Thank you both every so much! That's done exactly what I wanted - saved me almost a days work of boring finding! (just hope I remember for next time!)

thanks again
Sara