+ Reply to Thread
Results 1 to 14 of 14

Help figuring out how to automate a pattern

  1. #1
    Registered User
    Join Date
    05-31-2013
    Location
    Seattle
    MS-Off Ver
    Excel 2013
    Posts
    48

    Help figuring out how to automate a pattern

    Hi all,

    I'm working on a project for work and there's thousands of rows of data. The issue I'm dealing with involves two columns "date" and "month": Here is an example:
    Date Month
    8/10/2015 1
    9/7/2015 2
    9/19/2015 2
    10/20/2015 3
    11/8/2015 4
    12/9/2015 5
    12/15/2015 5
    12/28/2015 5
    1/28/2016 6
    2/7/2016 7
    2/13/2016 7
    2/28/2016 7
    3/25/2016 8
    4/10/2016 9
    5/8/2016 10
    5/15/2016 10
    5/30/2016 10
    5/31/2016 10
    and so on... and so forth...



    So basically I want the "month" column to count 1,2,3,4 (more like "1,1,1,1,1,2,2,2,2,3,3,3,3,3,3,3,3,4,4......) consecutively into infinity with corresponding changes in the "date" columns month. Because there may be multiple dates listed for certain months, there may be more 1's than 2's, etc etc. So in the example above, all of August 2015 is labeled "1" while September 2015 is labeled "2" and so on, and if the year changes from 2015 to 2016 then the count continues and does not restart. Anyone have any input or advice on how to accomplish this?

    Currently, I am just filling in the "month" column by typing in 1,1,1,1,1,2,2,2,2,2,3,3,3,3,3,4,4,4,4,4 manually but I would love to automate this process if possible.

    Thank you for any help! You guys are amazing here!

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,981

    Re: Help figuring out how to automate a pattern

    What should a date in August 2016 be? 1 or 13?
    Glenn



  3. #3
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,173

    Re: Help figuring out how to automate a pattern

    Can you put a helper value in C1 and enter this formula in C2?
    =IF(MONTH(A2)<>IFERROR(MONTH(A1),0),C1+1,C1)
    Attached Files Attached Files

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,981

    Re: Help figuring out how to automate a pattern

    If it should be 1 again, this will do it:

    =IF(MONTH(A1)-7<1,MONTH(A1)+5,MONTH(A1)-7)

  5. #5
    Registered User
    Join Date
    05-31-2013
    Location
    Seattle
    MS-Off Ver
    Excel 2013
    Posts
    48

    Re: Help figuring out how to automate a pattern

    Quote Originally Posted by Glenn Kennedy View Post
    What should a date in August 2016 be? 1 or 13?
    Hi Glen,

    So that depends on whether there is any data for June and July of 2016. In the example I gave, the last month with data is May 2016 which is labeled as month 10. If August 2016 was immediately the next data provided, then it would be labled as month 11 (the next month in the consecutive order). But if there were dates for June 2016 and July 2016 before August 2016, then June 2016 would be labeled month 11, July 2016 would be 12, and August 2016 would be month 13 yes. Hope that makes sense, let me know if not and I will try to be more specific Thank you again!!!!

  6. #6
    Registered User
    Join Date
    05-31-2013
    Location
    Seattle
    MS-Off Ver
    Excel 2013
    Posts
    48

    Re: Help figuring out how to automate a pattern

    Quote Originally Posted by Glenn Kennedy View Post
    If it should be 1 again, this will do it:

    =IF(MONTH(A1)-7<1,MONTH(A1)+5,MONTH(A1)-7)
    Unfortunately it would not be 1 again, but thank you for this input!!! There should not be any months with the same number ever in this circumstance.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,981

    Re: Help figuring out how to automate a pattern

    How about this (assumes data start in A1):

    =DATEDIF(42217, A1,"m")+1

    (42217 being the number corresponding to Aug 2014)

  8. #8
    Registered User
    Join Date
    05-31-2013
    Location
    Seattle
    MS-Off Ver
    Excel 2013
    Posts
    48

    Re: Help figuring out how to automate a pattern

    Quote Originally Posted by JieJenn View Post
    Can you put a helper value in C1 and enter this formula in C2?
    =IF(MONTH(A2)<>IFERROR(MONTH(A1),0),C1+1,C1)
    FANTASTIC! This worked perfectly. Wow seems so simple. Thank you so much!!!!

    Do you mind explaing why/how it works so perfectly? What does the "Month" and "A1" part of the formula do? You rock!

  9. #9
    Registered User
    Join Date
    05-31-2013
    Location
    Seattle
    MS-Off Ver
    Excel 2013
    Posts
    48

    Re: Help figuring out how to automate a pattern

    Quote Originally Posted by Glenn Kennedy View Post
    How about this (assumes data start in A1):

    =DATEDIF(42217, A1,"m")+1

    (42217 being the number corresponding to Aug 2014)
    Hi Glen,

    Jenn's worked! I may try this though just to test it out .. if I do I will get back to you. Thank you again for all of your help.

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,981

    Re: Help figuring out how to automate a pattern

    OK. Just be aware that if there is a month missing from your list, JieJenn's formula may not behave correctly!!

  11. #11
    Registered User
    Join Date
    05-31-2013
    Location
    Seattle
    MS-Off Ver
    Excel 2013
    Posts
    48

    Re: Help figuring out how to automate a pattern

    Quote Originally Posted by Glenn Kennedy View Post
    How about this (assumes data start in A1):

    =DATEDIF(42217, A1,"m")+1

    (42217 being the number corresponding to Aug 2014)
    OK you were right about Jenn's formula not working if there is a month missing. I'd like it to even count missing months. so what do you mean by 42217 corresponds with August 2014. Doesn't 201408 (2014-08) correspond with August 2014?

  12. #12
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,483

    Re: Help figuring out how to automate a pattern

    I suggest this http://www.cpearson.com/Excel/datetime.htm#SerialDates to explain how Excel stores date/time information.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  13. #13
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,173

    Re: Help figuring out how to automate a pattern

    Give this one a try. Turns out you don't need the helper value 0
    =IF(A2="","",IF(MONTH(A2)<>IFERROR(MONTH(A1),0),C1+IFERROR((YEAR(A2)-YEAR(A1))*12+MONTH(A2)-MONTH(A1),1),C1))
    Attached Files Attached Files

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,981

    Re: Help figuring out how to automate a pattern

    No. Excel hasn't a clue about dates. To it, a date is a special formatting that's applied to a number: the number ( fraction) of days since 00 Jan 1900 (yea, don't ask...). So when Excel sees 42217, formatted as a date, YOU see August 1st 2015; being 42217 days sice the zeroth January 1900.

+ 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. [SOLVED] Lookup and return rows based on pattern start and pattern end
    By JDI in forum Excel General
    Replies: 18
    Last Post: 11-16-2014, 11:44 PM
  2. Copy down hypertext cell range pattern & cell reference formula pattern
    By Underexcelling in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-10-2014, 03:23 AM
  3. Figuring out a pattern
    By tgraves18 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-06-2013, 08:25 PM
  4. [SOLVED] Would like to automate creating new 3 shift pattern
    By rosshkerr in forum Excel General
    Replies: 7
    Last Post: 08-19-2012, 04:20 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1