+ Reply to Thread
Results 1 to 7 of 7

put 1 in first 7 rows, put 2 in next seven rows, and so on

  1. #1
    Forum Contributor
    Join Date
    11-05-2014
    Location
    Charlotte
    MS-Off Ver
    2013
    Posts
    181

    put 1 in first 7 rows, put 2 in next seven rows, and so on

    Is there a less manual way to do this? I have 500 rows of data. I need to put the value "1" in the first 7 rows. I then need to put "2" in the second 7 rows, "3" in the next 7 rows, "4" and so on and so forth.

  2. #2
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: put 1 in first 7 rows, put 2 in next seven rows, and so on

    I think this formula will do the trick. Fill down through your rows:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: put 1 in first 7 rows, put 2 in next seven rows, and so on

    In A1

    =INT((ROW()-1)/7+1)

    copy down

  4. #4
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: put 1 in first 7 rows, put 2 in next seven rows, and so on

    in A1

    =ROUNDUP(ROW()/7,0)

    copy down

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: put 1 in first 7 rows, put 2 in next seven rows, and so on

    Seems like all formulas suggested will work if the start in cell A1

    This should work in any cell

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  6. #6
    Registered User
    Join Date
    10-19-2013
    Location
    England
    MS-Off Ver
    Excel 2007/2010
    Posts
    10

    Re: put 1 in first 7 rows, put 2 in next seven rows, and so on

    or row and number independent? something like:
    =IF(COUNTIF($A$1:A1,A1)=7,A1+1,A1)
    (currently assuming A1 has a number in it, and is the first in the sequence of 7)

    A little less overhead if the range is restricted, but put in cell A8 with numbers in up to A7 here;
    =IF(COUNTIF(A1:A7,A7)=7,A7+1,A7)

    And probably a good idea in all these cases to convert the formulas to values afterwards if they're not going to change.
    Last edited by Googlyhead; 03-02-2016 at 12:51 PM.

  7. #7
    Forum Contributor
    Join Date
    11-05-2014
    Location
    Charlotte
    MS-Off Ver
    2013
    Posts
    181

    Re: put 1 in first 7 rows, put 2 in next seven rows, and so on

    Cantosh- that worked perfectly!

    There were a few others that worked as well. I wasn't expecting so many responses. Very helpful! Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 02-16-2015, 11:46 AM
  2. Replies: 6
    Last Post: 06-07-2014, 10:07 AM
  3. Merge Duplicate Rows unique values into single rows for an infinite amount of columns/rows
    By aimeecrystalaid in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-21-2013, 08:43 PM
  4. [SOLVED] Delete blank rows between data rows, shift rows up, then repeat
    By excelactuary in forum Excel General
    Replies: 2
    Last Post: 03-11-2013, 11:53 AM
  5. Replies: 6
    Last Post: 03-04-2013, 12:03 AM
  6. Replies: 5
    Last Post: 11-12-2012, 08:38 PM
  7. Replies: 6
    Last Post: 08-18-2012, 05:00 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1