# filling data in non-sequential rows

#### msampson

##### Board Regular
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).

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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?

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.

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".

Replies
9
Views
256
Replies
9
Views
206
Replies
0
Views
262
Replies
9
Views
516
Replies
6
Views
485

### Forum statistics

1,196,461
Messages
6,015,381
Members
441,889
Latest member
balolaptopgiaolong

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back