If adjancent cell contains text then ignore, otherwise copy and pastemultiple values in one column t

A

acoustic.refugia

Hi all

I currently have a column (say column A) that contains a list of numbers.

In column B, in every 15th row, e.g. row 15, 30, 45 etc, there may or may not be a comment. Where there is a comment, this nullifies the 15 rows of numbers in column A, e.g. if there is a comment in B30, the numbers between A30 and A44 are nullified. The comments may be one or two words but vary enormously so I cannot say it will be either this word or that word.

Where there are no comments in column B, I would like to copy and paste the15 corresponding numbers in column A into column C. For example, if there is no comment in B30, I would like Excel to copy the numbers between A30 and A44 and paste them in between C30 and C44.

I would like to find a formula which I can drag all the way down to the endof the list which will achieve this.

Thank you and I look forward to your replies!
 
R

Ron Rosenfeld

Hi all

I currently have a column (say column A) that contains a list of numbers.

In column B, in every 15th row, e.g. row 15, 30, 45 etc, there may or may not be a comment. Where there is a comment, this nullifies the 15 rows of numbers in column A, e.g. if there is a comment in B30, the numbers between A30 and A44 are nullified. The comments may be one or two words but vary enormously so I cannot say it will be either this word or that word.

Where there are no comments in column B, I would like to copy and paste the 15 corresponding numbers in column A into column C. For example, if there is no comment in B30, I would like Excel to copy the numbers between A30 and A44 and paste them in between C30 and C44.

I would like to find a formula which I can drag all the way down to the end of the list which will achieve this.

Thank you and I look forward to your replies!

Your description is not entirely clear.

I assume that by "comment", you mean any content in the cell itself, and not a comment attached to the cell.
I must also assume that your data starts in row 15, since there is no row zero to contain something that would nullify A0:A14

Given that, try this formula:

C15: =IF(OFFSET(B15,-MOD(ROW(),15),0)="",A15,"")

and fill down as far as required.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top