cmccabe1
05-21-2014, 02:39 PM
Currently I use a few formulas and manually calculate data. I would like to automate that process and need some expert help in doing so.
The formulas are: Formula 1
=IF(COUNTIFS( A$2:A$6713,F2,B$2:B$6713,"<="&G2,C$2:C$6713, ">="&G2),VLOOKUP(G2,$B$2:$D$6713,3,1),"No")
A B C D E
EpilepsyHomopolymer
chr
start
end
description
2
11
791787
791791
4C
3
11
791787
791792
5C
4
11
791817
791821
4G
…6713
I have put the workbook here as it is too large to attach.
https://onedrive.live.com/redir?resid=5D59E448AFE6FFD!144&authkey=!AP0nkuaNe-I2alM&ithint=file%2c.xlsm
Examples of each would be:
Formula 1 (EpilepsyHomopolymer): If Chr and Start in annovar match chr in panel and is between start and end in panel, Then whatever is in the description columns is copied to Homopolymer in annovar. I hope this makes sense and helps. Thank you very much.
So in Annovar chr is 1 and start is 40556922. This coordinate does not not fall in between the start and end of chr 1 in panel, so the Homopolymer cell in annovar would be "No".
The formulas are: Formula 1
=IF(COUNTIFS( A$2:A$6713,F2,B$2:B$6713,"<="&G2,C$2:C$6713, ">="&G2),VLOOKUP(G2,$B$2:$D$6713,3,1),"No")
A B C D E
EpilepsyHomopolymer
chr
start
end
description
2
11
791787
791791
4C
3
11
791787
791792
5C
4
11
791817
791821
4G
…6713
I have put the workbook here as it is too large to attach.
https://onedrive.live.com/redir?resid=5D59E448AFE6FFD!144&authkey=!AP0nkuaNe-I2alM&ithint=file%2c.xlsm
Examples of each would be:
Formula 1 (EpilepsyHomopolymer): If Chr and Start in annovar match chr in panel and is between start and end in panel, Then whatever is in the description columns is copied to Homopolymer in annovar. I hope this makes sense and helps. Thank you very much.
So in Annovar chr is 1 and start is 40556922. This coordinate does not not fall in between the start and end of chr 1 in panel, so the Homopolymer cell in annovar would be "No".