filling data in non-sequential rows

[msampson]

I have a peptide sequence that is 4500 amino acids long. I have the numbers 1 - 4500 in column A and the corresponding letter code for the amino acid in that position in column B. I have done a bunch of other calculations and isolated small series of amino acids within the entire sequence (example: positions 25-42, 153-166, 381-297, etc).
I would like to fill column C with either a blank space or, if it is in one of the selected series, the letter code again. So that the first 24 rows of column C would be blank but rows 25-42 would mirror the letter shown in B. Then blank spaces again until we reach row 153, etc.

I've used a vlookup to paste the letters with the chosen parts of the sequence, but now I want to space them out along the entire 4500 so that I can see if there are overlapping areas between several sets of criteria for choosing series (these would go in columns D, E, etc).

 

[Teeroy]

It should be a simple test (using If or Match) but we need to know how and where are you defining your series of interest (example: positions 25-42, 153-166, 381-297, etc)? Do you have a Table on another sheet?

 

[msampson]

they are in a table but there is no space between say 42 and 153, 166 and 381, etc. I do list each number in the series in each row.

 

[Teeroy]

Try the following array formula:

C1: =IF(OR(A1=Sheet2!A$1:A$1000),B1,"")

confirm with CTRL + SHIFT + ENTER (not just enter) and then fill down.

This formula assumes that the list of positions in the sequence that you are interested in is in column A (A1-A1000) of "Sheet2".