I have two lists of genes. I want excel to arrange the

lists that any gene which appears in one list will be in the

same row as an gene when it appears in the second list.

If the gene is not in one list it would just insert a blank

cell. Is this even possible, short of doing it by hand.

Is this what you are looking for? I started with this:

A B 1 a d 2 b f 3 c e 4 d a 5 e h 6 f 7 g 8 h 9 iIn C1, I entered this formula and dragged it down to C9:

=IF(ISNA(MATCH(A1,$B$1:$B$5,0)),"",INDEX($B$1:$B$5,MATCH(A1,$B$1:$B$5,0),1))

and ended up with this:

A B C 1 a d a 2 b f 3 c e 4 d a d 5 e h e 6 f f 7 g 8 h h 9 iI then did:

1 - Copy....Paste Special...Values on Column C

2 - Delete Column B and ended up with this:A B 1 a a 2 b 3 c 4 d d 5 e e 6 f f 7 g 8 h h 9 i

dude! thats awesome. thanks.

but what if say you had a "j" in column b but not in column a..

would it not show up in column c?

No, it would not show up. I did not glean that as a criteria in your question. Are you saying that there items in

bothlists that might not appear in the other?If that is the case, I'm not sure how you would want the output to appear. Are you saying that each list could have blanks in various locations?

yeah. each list is a list of mutations specific to each sample

and i need to order them so that they line up. with a blank

appearing where each sample doesnt have a mutation. Not only

that but i have to compare many samples so I will have more

than two columns.. ugh. I know its complex. Anyway your

formula is already been a great help and I will use it a lot!

thanks man!

I'll work on a macro that should handle both columns. I don't know if formulae will get you what you want.

Hi, Am I right in thinking that Excel 2008 for Mac does not support Visual Basic macros, or have I got that completely wrong?

Anyway, try this:

If you create a single list of all the genes in all the samples being compared, you can create the matching lists for all the samples.

Here is an example, just using five samples (columns)

Source data:- A B C D E 2 def ijk cde cde xyz 3 rst abc bcd qrs def 4 mno def hij vwx efg 5 pqr bcd mno abc bcd 6 ghi qrs ghi ijk 7 qrs mno xyz fgh 8 xyz abc 9 ghiResult data:

- G H I J K L 2 abc abc abc abc 3 bcd bcd bcd bcd 4 cde cde cde 5 def def def def 6 efg efg 7 fgh fgh 8 ghi ghi ghi ghi 9 hij hij 10 ijk ijk ijk 11 mno mno mno mno 12 pqr pqr 13 qrs qrs qrs qrs 14 rst rst 15 vwx vwx 16 xyz xyz xyz xyz

Column G has 'all' the genes in it.

Put this formula in cell H2:=IF(ISNA(VLOOKUP($G2,A$2:A$9,1,FALSE)),"",VLOOKUP($G2,A$2:A$9,1,FALSE))

Note the $ signs

Drag the formula right - across to column L in this example, but as many rows as you have samples

Then drag the formulas in all of row 2 columns H to L down alongside the list of all the genes in column GRegards

awesome! works perfectly.

Hi, Thanks for the feedback.

Glad it worked.

Regards

Ask Your Question

Weekly Poll

Do you think Jony Ive could make a big impact on Airbnb?

Discuss in The Lounge

Poll History