Find positions in a main sequence not covered by internal sequence

[ehansen]

Dear Mr Excel,

I have a dna sequence, one word of 10 letters (aattggccaa)

Furthermore I have two columns; the start and end position of a number of internal sequences within the main sequence shown above.

Ex
2 5 means the sequence from 2 to 5 (attg)
6 6 means the 6th position (g)

I need a way to extract the positions of the main sequence that is not covered by any of the shorter internal sequences. In this case positions 1 and 7-10.

Can you please help me. I looks easy but in the reality the main sequence is 250-300 letters and the internal represents a myriade of sequences with different lenght and starting points.

Big thanks,

Erik

 

[sanrv1f]

see if this helps,

Excel Workbook
	A	B	C	D
1	Main seq	Start	end
2	aattggccaa	2	5	agccaa
3	aaaccaggataaat	5	9	aaactaaat
4	aaacccaaaccggtac	7	11	aaacccggtac
Sheet1

Excel 2003

Cell Formulas
Range	Formula
D2	=REPLACE(A2,B2,C2-B2+1,"")

 

[ehansen]

Dear Sankar, thanks for your reply.

What I need is the positions (in numbers) of the letters in the main sequence not covered by any of the internal sequences.
Ex
The main sequence (10 letters) has two internal sequences with the positions 2-5 and 8-9. I would like to get an output telling me that the positions 1, 6-7 and 10 is not covered by any internal stretch of letters.

Thanks,

Erik

 

[sanrv1f]

could you post few lines of sample data and the expected results

 

[ehansen]

Here are a sample

The main sequence is 10 letters long: aattggccaa
It has two "internal sequences"
position 2-5; here shown in bold aattggccaa
position 7-9; here shown in bold aattggccaa

I would like to have an output file showing the positions of those letters not covered by any of the internal sequences.
The positions should be 1, 6 and 10; in bold aattggccaa;

Thus the inverse of the sum projection of the internal sequences

Thanks,

Erik

 

[Peter_SSs]

Erik

Some questions:

1. How is your data presented/ And how do you want the results presented? Is it like my sample below? If not, please post a small screen shot or clarify in words.

2. Are you looking for a formula approach? Or a macro approach? Or either?

3. For the sample below, are my results correct?

Excel Workbook
	A	B	C	D	E
1		Data		Results
2	Sequence	Start	End	Start	End
3	abcdefghijklmnopq	1	1	5	7
4		2	4	14	15
5		8	12	17	17
6		13	13
7		16	16
8
Seq