VB parse

[cmccabe]

I have two questions:

1. In the attached spreadsheet the Results tab is how the data looks: in order to run the below code can a line be included that removes columns E and on (Desired tab has an example in it)?

Please Login or Register  to view this content.

The second questions is can the code be run each time a file is open? As of now it is a personal macro (PERSONAL.XLSB module 32).Thanks.

[shg]

... removes columns E and on

Please Login or Register  to view this content.

Desired tab has an example in it

Results and Desired are the same in the workbook you posted.

can the code be run each time a file is open?

Each time ANY workbook is opened? That would clobber any workbook having contents other than like this.

You could assign it to a keyboard shortcut.

[cmccabe]

I didn't even think about the other workbooks, too focused on this one I guess.

Column C (Chromosomal Variant) has three possible ways to look:

1. NC_000015.9:g.89876827_89876828insTGCTGC (ins = insertion)
2. NC_000015.9:g.89876858T>C
3. NC_000015.9:g.89876801_89876800delGTC (del = deletion)

I have attached a file with the data and how the output should look. Is it possible to include an If/Then for ins, del, or not ins or del? Thanks.

[shg]

You've done this based on some understanding of what all that means. I gather it is some type of grammar, but to me, it's gibberish. I don't know what's input, what's output, or the rules of parsing.

The prior code gave some insight for one particular type of data.

[cmccabe]

I apologize for the confusion... I am a scientist trying to learn programming.

The input format of the data will always look the same (the same 4 columns will always be there).

Column C is parsed, in one of 3 ways, depending on the data in it:

1. NC_000015.9:g.89876827_89876828insTGCTGC (ins = insertion)
2. NC_000015.9:g.89876858T>C
3. NC_000015.9:g.89876801_89876800delGTC (del = deletion)

If column C contains ins in it, then the parse format is (condition 1)
15 89876827 89876828 - GCAGCA

If column C contains del in it, then the parse format is (condition 3)
15 89876801 89876802 GTC -

If column C does not contain del or ins in it, then the parse format is (condition 2)
15 89876858 89876858 A G


I also attached an example spreadsheet in the last post. I hope his helps and thank you for your help.

[shg]

OF, so it's easy to determine the parse format. What are the parsing rules for each format? Which column should be parsed?

[cmccabe]

Column C (Chromosomal Variant) should be parsed:

NC_000015.9:g.89876827_89876828insTGCTGC
Since ins is in that text:
15 89876827 89876828 - GCAGCA

NC_000015.9:g.89876801_89876802delGTC

Since del is in the text:
15 89876801 89876802 GTC -

NC_000015.9:g.89876858T>C
Since neither ins or del is in the text:
15 89876858 89876858 T C

I hope this helps. Thanks.

[shg]

NC_000015.9:g.89876827_89876828insTGCTGC
Since ins is in that text:
15 89876827 89876828 - GCAGCA

Where did the hyphen come from?

Where did GCAGCA come from?

NC_000015.9:g.89876801_89876802delGTC

Since del is in the text:
15 89876801 89876802 GTC -

Where did the hyphen come from?

NC_000015.9:g.89876858T>C

Since neither ins or del is in the text:
15 89876858 89876858 T C

Why does 89876858 appear twice?

[cmccabe]

NC_000015.9:g.89876827_89876828insTGCTGC
Since ins is in that text:
15 89876827 89876828 - TGCTGC
Where did the hyphen come from? the - is a special case and for ins the format above is used (inserted in column 4)

Where did GCAGCA come from? sorry, this was a typo should be TGCTGC (text after the ins)

NC_000015.9:g.89876801_89876802delGTC

Since del is in the text:
15 89876801 89876802 GTC -
Where did the hyphen come from? the - is a special case and for del the format above is used (inserted in column five)

NC_000015.9:g.89876858T>C

Since neither ins or del is in the text:
15 89876858 89876858 T C
Why does 89876858 appear twice? the 89876858 is the position and since this in a SNP the same start and end result in column 2 and 3

1 2 3 4 5
15 89876858 89876858 T C

Thank you very much.

[jindon]

Column C (Chromosomal Variant) should be parsed:

Try

Please Login or Register  to view this content.

[cmccabe]

The code seems to work but not as expected and I am not quite sure why. I have attached the workbook with the current tab after the code is run and the desired tab is how the data should look.

If in column C 23 or 24 is detected 23 = x and 24 = y, so the parse for 23 would be:

NC_000023.10:g.47433684T>C (column C) is parsed to

X 47433684 47433684 T C

If in column C 1-9 is detected then the leading 0 is removed from the parse:

NC_000006.11:g.146056472G>T (column C) is parsed to

6 146056472 146056472 G T


VB

Please Login or Register  to view this content.

Thank you very much for your help.

[jindon]

Slightly different from your original data.

Please Login or Register  to view this content.

[cmccabe]

The code works well except for one minor thing.

If in column C 23 or 24 is detected 23 = x and 24 = y, so the parse for 23 would be:

NC_000023.10:g.47433684T>C (column C) is parsed to

X 47433684 47433684 T C

Thank you for your help this is a amazing code .

[jindon]

Just replace...

Please Login or Register  to view this content.

[cmccabe]

Thank you!

[shg]

Good job, Jindon!

[jindon]

Good job, Jindon!

Thanks.........

[jindon]

Duplicate.