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?

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:


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 •

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 •

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

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 •

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 •

March 21, 2010 at 10:10:13

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:

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 G


Report •

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

Report •

March 24, 2010 at 14:17:56

Thanks for the feedback.

Glad it worked.


Report •

Ask Question