+ Reply to Thread
Results 1 to 6 of 6

Offset every 4th row problem

  1. 06-22-2017, 05:45 AM #1
    iantix
    iantix is offline
    Forum Contributor
    Join Date
    08-21-2014
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    232

    Question Offset every 4th row problem

    Hi all,

    I have a list of data - text codes - in column C.

    I need column A to;

    - pick up the first code in A2
    - repeat that in the following 3 cells A3,A4 and A5
    - THEN yellow highlighted A6 has an offset function which works, picking up the next code in cell C3
    - BUT after that I want to be able to copy this set of 4 down across a long column - effectively showing each code across 4 rows
    - therefore I need to figure out how the formula in cell A10 can stay referencing the next code (in cell C4) without jumping down by 3 cells and skipping some codes

    Just can't figure the logic out - any help appreciated please on a hot sultry day.

    Ian
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 06-22-2017, 05:52 AM #2
    BoredWorker
    BoredWorker is offline
    Forum Contributor
    Join Date
    03-22-2017
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    230

    Re: Offset every 4th row problem

    Hi

    in A2, copy down to last row.

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


    this ROUNDUP((ROW()-1)/4,0 function to remain 1 for 4 row
    (ie, 1,1,1,1,2,2,2,2,3,3,3,3)

    you may change the 4 in the formula to change the number of repeat.
    Last edited by BoredWorker; 06-22-2017 at 05:55 AM.
    Hope you can learn every time you visit here.

    If you still confuse on how it work, kindly ask or go to
    i) Formula - Formula (Ribbon) > Formula Auditing (Section) > Evaluate Formula > Evaluate; or
    ii) VBA/Code - Click F8 to see how it work step by step.

    It it take care of your question, Please:
    Mark tread as [Solved] [Thread Tools->Mark thread as Solved]    ;and
    Click *Add Reputation to thank anyone solved your question.
    Reply With QuoteRegister To Reply
  3. 06-22-2017, 06:00 AM #3
    iantix
    iantix is offline
    Forum Contributor
    Join Date
    08-21-2014
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    232

    Re: Offset every 4th row problem

    Damn - that's genius! Thanks so much - hadn't occurred to use an INDEX with it - and ROUNDUP new to me.

    Will deconstruct in other uses to understand.

    Thank YOU!
    Reply With QuoteRegister To Reply
  4. 06-22-2017, 06:01 AM #4
    samba_ravi
    samba_ravi is online now
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Offset every 4th row problem

    a2=IFERROR(INDEX($C$2:$C$14,CEILING(ROWS(A$2:A2)/4,1)),"")
    try this and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.
    Reply With QuoteRegister To Reply
  5. 06-22-2017, 06:06 AM #5
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Offset every 4th row problem

    or this in A2:
    =INDEX($C$2:$C$14,ROUNDUP(ROWS(A$2:A2)/4,0))

    Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
    Last edited by leelnich; 06-22-2017 at 06:08 AM.
    Reply With QuoteRegister To Reply
  6. 06-22-2017, 06:10 AM #6
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Offset every 4th row problem

    Happy to help, thank you for the rep!-Lee
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Problem with Offset
    By Fredma in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-18-2014, 12:20 PM
  2. Problem with Offset
    By Fredma in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-18-2014, 08:23 AM
  3. Problem with Offset vba
    By Mamud in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 06-30-2013, 09:12 PM
  4. Offset Problem!
    By watzmann in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-09-2009, 08:14 AM
  5. Problem OFFSET
    By John21 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-11-2006, 08:31 AM
  6. Problem with an offset
    By floep in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-28-2006, 10:45 AM
  7. Offset Problem
    By morrida3 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 09-16-2005, 03:02 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1