# two columns.. complicated problem?

Microsoft Excel: mac 2008 (mac)
March 18, 2010 at 13:54:47
Specs: Macintosh
 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.

See More: two columns.. complicated problem?

#1
March 18, 2010 at 14:26:55
 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 i ```In 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 i ```I 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 ```

Report •

#2
March 18, 2010 at 16:52:59
 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?

Report •

#3
March 18, 2010 at 17:06:03
 No, it would not show up.I did not glean that as a criteria in your question. Are you saying that there items in both lists 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?

Report •

Related Solutions

#4
March 18, 2010 at 17:57:55
 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!

Report •

#5
March 18, 2010 at 18:10:59
 I'll work on a macro that should handle both columns. I don't know if formulae will get you what you want.

Report •

#6
March 21, 2010 at 10:10:13
 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 ghi ```Result 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 \$ signsDrag the formula right - across to column L in this example, but as many rows as you have samplesThen drag the formulas in all of row 2 columns H to L down alongside the list of all the genes in column GRegards

Report •

#7
March 24, 2010 at 12:21:56
 awesome! works perfectly.

Report •

#8
March 24, 2010 at 14:17:56
 Hi,Thanks for the feedback.Glad it worked.Regards

Report •