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

D

dimuthumme

New member
Joined
Mar 17, 2014
Messages
4
Reaction score
0
Points
0
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
 

Attachments

  • Non zero date selection for 24 hours without macro.xlsx
    42.3 KB · Views: 18
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?
 
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
 

Attachments

  • Non zero date selection for 24 hours without macro.xlsx
    42.3 KB · Views: 22
Last edited:
Dear Martindwilson,

THank you very much. It really worked. Great.
many thanks.
 
You must log in or register to reply here.
Back
Top