0

I have an Excel workbook measuring gene expression in blood and lungs. Currently, the table is formatted so that each row represents a different gene, the first 31 columns represent levels of that gene in the blood, and the second 31 columns represent levels of that gene in the lungs. I need to make it so that the blood levels are one column, the lungs are another column, and the gene labels are on the left, with 30 spaces between each one. However, I cannot use the Transpose function due to the fact that I have 22,000 different genes (and thus 22,000 different data sets). Any help with reorganizing this would be greatly appreciated!

2 Answers2

0

I suggest Convert matrix to 3-column table ('reverse pivot', 'unpivot', 'flatten', 'normalize') and when in a range, sort by Column, then select the blood and lung categories separately and sort each by Row.

Community
  • 1
  • 1
pnuts
  • 58,317
  • 11
  • 87
  • 139
0

I suspect that using transpose-paste and going out to lunch may be your best bet, but if you want to do it through a formula, make a new worksheet within the same document, and paste in the formula:

=INDIRECT(CONCATENATE("Sheet1!",ADDRESS(COLUMN(),ROW())))

I am assuming that your sheet is called "Sheet1", because that is the default. If your main sheet is called something else, replace "Sheet1!" with "[somename]!".

Drag out the formula as far as you need, select all, copy, and right click to paste as values.

Ben I.
  • 1,065
  • 1
  • 13
  • 29
  • I tried to use the transpose function, but nothing came up. I assumed that it just hadn't worked, but do you think that I just had to leave it for a while? In either case, I'll try your formula and let you know how that goes. In either case, thank you for your help! –  Jan 13 '15 at 14:21
  • What is the name of the main worksheet within your Excel file? – Ben I. Jan 14 '15 at 13:59
  • It was Sheet1, but luckily I managed to figure out how to do it in R. I appreciate your help though! –  Jan 14 '15 at 20:59