# How to fill cells in Excel in particular sequence?

1. Dec 20, 2016

Suppose I need to fill a row composed of 32 cells with a sequence of 4 cells with value 1 in a row followed by 4 cells with value 0 in a row and then again 4 cells of 1 and so on. How can I do this without copying those cells and repeating pasting them in their location?

2. Dec 20, 2016

### BvU

If you put the following in e.g. cell A4

=INT(MOD(MOD((COLUMN(A4)+3)/4,4),2))

You can copy that cell to the right ad libitum. But is that really what you want ?​

3. Dec 20, 2016

I tried this but it does not work.
I attach a pic to illustrate my thought. I wish to copy the 4 cells (1010) highlighted in dark green to the right so as to fill the rest of the row highlighted in light green. I wish the result to be similar to the next row highlighted in dark green. I need to do this in a fast way no matter how long will be the row. I wish to copy a formula like you did but I don`t want to copy (1010) and drag it down to the line before pasting it.

#### Attached Files:

• ###### Snap 2016-12-20 at 16.16.18.png
File size:
35.1 KB
Views:
28
4. Dec 20, 2016

### BvU

That is strange. It does exactly what you describe, so in that sense it should work.

It comes as no surprise to me that it does not do what you want (hence also my question in posst #2)

I wouldn't know of an easy way to copy blocks of varying length (in your case 4) to side by side locations a variable number of times without resorting to a macro:

Code (Text):

Sub MyCopy()

Selection.Copy

ncopies = InputBox("How many copies", "Block copy")

For icopy = 1 To ncopies
Selection.End(xlToRight).Select
ActiveCell.Cells(1, 2).Select
ActiveSheet.Paste
Next icopy

End Sub

5. Dec 20, 2016

Where do I put this macro? I am not familiar with it?
Sorry to know that no other easier way to accomplish that. I like the first formula you advised but in that formula where is 1 and 0 and why does column appear? Have you tried it?

6. Dec 20, 2016

### BvU

Developer tab | Macros

Under Options... you can associate it with e.g. CTRL+SHIFT+G

Very useful feature of Excel.

Of course I have.

In post #2 column() returns the column number; add 3, divide by 4
so 1,2, ..., 12, 13, 14 $\rightarrow$ 1, 1.25, ... 3.75, 4, 4.25 ​
take modulo 4 so that ...12, 13, 14 $\rightarrow$ ... 3.75, 0, 0.25
take modulo 2 so that 1,2, ..., 12, 13, 14 $\rightarrow$ 1, 1.25, 1.5, 1.75, 0, 0.25 ... 1.75, 0, 0.25
take int so that 1,2, ..., 12, 13, 14 $\rightarrow$ 1, 1, 1, 1, 0, 0 ... 1, 0, 0