How to fill cells in Excel in particular sequence?

[Adel Makram]

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?

 

[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 ?​

 

[Adel Makram]

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 ?​

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.

 

[BvU]

I tried this but it does not work.

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:

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

 

[Adel Makram]

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:

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

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?

 

[BvU]

Developer tab | Macros

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

I am not familiar with it?

Very useful feature of Excel.

Have you tried it?

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