Hello All,

I usually have to compare list of genes to find out which genes are common
in the lists, so I usually use http://jura.wi.mit.edu/bioc/tools/compare.html

But wondering how do I do it if I have got more than 2 lists.

Also, is there any way that I can compare these lists in the worksheet and
highlight common entries?

Many thanks

Assuming you have one list in column A (the longer one), and the other
list in column B (the shorter one), both starting on row 2 with a
header row on row 1, enter this formula in cell C2:

=IF(ISNA(MATCH(B2,A$2:A$250,0)),"",B2)

This assumes the first list in column A occupies A2 to A250 - adjust to
suit. Copy this formula down for as many entries as there are in column
B (double-click the fill handle). This will list the common entries and
will return a blank if there is no match - you could fix the values
with paste special, then sort the items in this list so that the blanks
drop to the bottom.

Hope this helps.

Pete

Sorry, I missed your first question. Using the common list from column
C in conjunction with a third list, the same approach can be taken to
give you another list which is common to all three original lists.
Apply the same for 4 or more lists.

Hope this helps

Pete

So by this method I guess I will be doing pairwise comparison, Isnt there any
option to compare more than two lists, all at once?

"Pete" wrote:

Sorry, I missed your first question. Using the common list from column
C in conjunction with a third list, the same approach can be taken to
give you another list which is common to all three original lists.
Apply the same for 4 or more lists.

Hope this helps

Pete

This formula of your works but I have to have a seperate column to get the
common entries, how do I do it so that I just highlight the common entries in
parent column (as I need to know their order (ranking) number)

Thanks for your inout

Well, you will need at least one extra column to put formulae in.

You might like to try conditional formatting on your 2 columns of data
to highlight the ones in common (I'm not sure what you mean by
"ranking"). Following what I gave you before, highlight the cells in
column A from A2 to A250 (adjust this as necessary). Then select Format
| Conditional Formatting then in the panel presented select "Cell Value
is" and "equal to" then in the third panel enter the formula:

=INDEX($C$2:$C$150,MATCH(A2,$C$2:$C$150,0),1)

assuming your third list occupies C2 to C150 (adjust as necessary).
Then click the Format button, select the Patterns tab, choose bright
Yellow, then OK your way out. This should highlight the cells in column
A which contain values which are also in column B (duplicates allowed).
You can apply the same approach to the data in column B - just change
the A2 in the middle of the formula to B2.

Is this the effect you were after?

Pete

This was most helpful and simple to do. Most of the other aswers are for
very seasoned excel users and difficult to understand. This worked for me!
Thanks Pete

"Pete" wrote:

Assuming you have one list in column A (the longer one), and the other
list in column B (the shorter one), both starting on row 2 with a
header row on row 1, enter this formula in cell C2:

=IF(ISNA(MATCH(B2,A$2:A$250,0)),"",B2)

This assumes the first list in column A occupies A2 to A250 - adjust to
suit. Copy this formula down for as many entries as there are in column
B (double-click the fill handle). This will list the common entries and
will return a blank if there is no match - you could fix the values
with paste special, then sort the items in this list so that the blanks
drop to the bottom.

Hope this helps.

Pete