Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

How to fill cells in Excel in particular sequence?

  1. Dec 20, 2016 #1
    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. jcsd
  3. Dec 20, 2016 #2

    BvU

    User Avatar
    Science Advisor
    Homework Helper
    Gold Member

    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 ?​
     
  4. Dec 20, 2016 #3
    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:

  5. Dec 20, 2016 #4

    BvU

    User Avatar
    Science Advisor
    Homework Helper
    Gold Member

    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 (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
     
     
  6. Dec 20, 2016 #5
    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?
     
  7. Dec 20, 2016 #6

    BvU

    User Avatar
    Science Advisor
    Homework Helper
    Gold Member

    Developer tab | Macros

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

    Very useful feature of Excel.

    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
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook

Have something to add?
Draft saved Draft deleted



Similar Discussions: How to fill cells in Excel in particular sequence?
Loading...