v*********0 发帖数: 941 | 1 Now I have two Excel spreadsheets as below:
Spreadsheet1
Gene change padj
1 212 0.0006
2 99 0.09
3 15 0.7
4 59 0.456
5 418 0.00001
6 566 0
Spreadsheet2
Gene statistic FDR
1 28 0.0005
2 17 0.007
3 3 0.81
4 8 0.28
5 48 0.002
6 111 0
Using Excel, how can I create one new spreadsheet that only includes the
Genes with both padj and FDR are less than 0.05?
And how can I do this in any other statsitical software, Like R, SPSS?
Thanks very much for any idea for any question!
In addition, how can I first merge these two spreadsheets according to Gene
in Excel? |
D******n 发帖数: 2836 | 2 1. hand pick.
2. sort them by padj and FDR, and then hand pick. |
v*********0 发帖数: 941 | 3 Thanks. Actually, there is about 10,000 Genes in each spreadsheet. I know
how to sort or filter by using one factor( FDR or Padj) in each Spreadsheet
. But after merging them to One New Spreadsheet, How to sort or filter by
choosing both FDR and Padj less than 0.05?
【在 D******n 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 1. hand pick. : 2. sort them by padj and FDR, and then hand pick.
|
p***r 发帖数: 920 | 4 creat a flag column that satisfied the condition |
j*****g 发帖数: 39 | 5 I agree with this one. You may want to merge two sheets into one and it is
easier to write your condition. And vlookup may help you in merging two
sheets. Filter can be used to identify and show the results that you want.
【在 p***r 的大作中提到】![](/moin_static193/solenoid/img/up.png) : creat a flag column that satisfied the condition
|
a********t 发帖数: 1810 | 6 First method: import these two spreadsheets into MS ACCESS, and create query
in Access to extract data you want, then export query into new excel.
2nd method: directly write SQL code in marcos in your excel, extract data
you want |
G*****u 发帖数: 1222 | 7 先把两个spreadsheet上的gene整合到同一个表上
使用"advanced filter"可以很简单的去除duplicated records
使用vlookup从两个表上分别得到padj和FDR
再加一列 用if statement找出满足条件的行 删除不需要的
十分钟应该就搞定了 |
a********t 发帖数: 1810 | 8 All methods are not good but mine, his data will change every day/week/month
, rows will increase or decrease. Would you re-filter, delete, copy/paste in
every change? It is OK, but definitely not efficient method.
He knows how to filter,delete,copy/paste, but that is not what he wants |