How to fill cells in Excel in particular sequence?

  • #1
612
13
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?
 

Answers and Replies

  • #2
BvU
Science Advisor
Homework Helper
14,480
3,752
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
612
13
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.
 

Attachments

  • Snap 2016-12-20 at 16.16.18.png
    Snap 2016-12-20 at 16.16.18.png
    35.1 KB · Views: 439
  • #4
BvU
Science Advisor
Homework Helper
14,480
3,752
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) :smile:

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:
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
612
13
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) :smile:

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:
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?
 
  • #6
BvU
Science Advisor
Homework Helper
14,480
3,752
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. :oldgrumpy:

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
 

Related Threads on How to fill cells in Excel in particular sequence?

Replies
1
Views
757
Replies
8
Views
1K
Replies
14
Views
1K
Replies
3
Views
15K
  • Last Post
Replies
4
Views
3K
  • Last Post
Replies
7
Views
27K
  • Last Post
Replies
4
Views
2K
  • Last Post
Replies
19
Views
2K
Replies
10
Views
2K
Top