Auto-filling a Series

forgameslive

New member
Joined
Aug 4, 2013
Messages
1
Reaction score
0
Points
0
Hi,


I am trying to automatically get the following series filled in but have failed thus far:


1,2,3,4,5,6,7,11,12,13,14,15,16,17,21,22,23....... (after 7, it skips 3 digits)


Similarly,


Excel has the "Fill Weekdays" option. However, how do I also remove Friday along with Saturday and Sunday from the dates of a year?


Any help would be much appreciated!


Thanks!
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
How far do you need the series to extend? You can achieve the 'skipping dates' thing by using custom lists. See http://office.microsoft.com/en-nz/e...for-sorting-and-filling-data-HA010222142.aspx

But i don't think it will work for your number sequence question. Although you can use a formula.
Put this in row 1 and drag down:
=INT(ROW()/7-1/7)*10+IF(MOD(ROW(),7)=0,7,MOD(ROW(),7))
Then you could copy and paste special as values.
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
You could achieve this with macros.

For the numbers something like this
Code:
Sub Skip890()
    Dim rng As Range
    Dim cel As Range
    Dim cNum As Long
    
    'where ever you are putting the numbers
    Set rng = Range("C3:C38")
    
    'put the number you want to start at in cell A2
    cNum = Range("A2").Value
        
    For Each cel In rng
        cel.Value = cNum
        cNum = cNum + 1
        If Right(cNum, 1) = 8 Then
            cNum = cNum + 3
        End If
    Next cel
    
End Sub

maybe like this for the days
Code:
Sub SkipFriToSun()
    Dim rng As Range
    Dim cel As Range
    Dim cDa As Date
    
    'where ever you are putting the days
    Set rng = Range("D3:D38")
    
    'put the starting date in cell B2
    cDa = Range("B2").Value
    
    For Each cel In rng
        'if the day is Friday add 3 days to get Monday
        If Format(cDa, "ddd") = "Fri" Then
            cDa = cDa + 3
        End If
        
        'format this how ever you want it displayed
        cel.Value = Format(cDa, "Long Date")
        'get ready for next day
        cDa = cDa + 1
    Next cel
    
End Sub

I put this code into the sheets module, hope it's of some use, just an idea.
 
Last edited:

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
Here's a formula that handles any series of repeating numbers on the far right from 1 to 9. Just replace X with the number
=INT((ROW()-1)/x)*10+MOD(ROW(),-x)+x
 
Top