Results 1 to 4 of 4

Thread: Selecting non zero dates and repeating 24 times( for hours) without macro

  1. #1

    Selecting non zero dates and repeating 24 times( for hours) without macro



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,

    I want to select the dates(from column H) in which the value(in column I) is greater than zero and paste them in column K(Attachment). At the same time, I want to expand each day for 24 hours. That means, I want to repeat the date 24 times while pasting in column K. Kindly help me. I would prefer the formula as I hv to repeat the same for large number of sheets.

    To make it more clear, I hv illustrated it in column N & O manually.

    Many thanks
    Attached Files Attached Files

  2. #2
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    To accomplish what you are trying to do, you will need to run some code not a formula.
    Do you know any programming with the VBA?

  3. #3
    in colummn j starting j2 put
    =IF(I2=0,"",MAX($J$1:J1)+1)
    in column k starting k2
    =IFERROR(INDEX(H:H,MATCH(CEILING(ROWS($A$1:A1)/24,1),J:J,0)),"")
    in column L starting L2
    =MOD(ROWS($A$1:A1)-1,24)
    select j2:L2
    fill down
    Attached Files Attached Files
    Last edited by martindwilson; 2014-04-14 at 02:53 AM.

  4. #4
    Dear Martindwilson,

    THank you very much. It really worked. Great.
    many thanks.

Posting Permissions

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