+ Reply to Thread
Results 1 to 10 of 10

Generating Incremental Order Number based on Date & Condition

  1. 11-29-2020, 12:14 PM #1
    excellon
    excellon is offline
    Registered User
    Join Date
    10-02-2019
    Location
    Dhaka, Bangladesh
    MS-Off Ver
    2016
    Posts
    36

    Generating Incremental Order Number based on Date & Condition

    Hello Experts,

    I want the order number as it appears in column C (please see the attached file) based on the date in column A.
    Required Format of the order number: YYMMDDNN-CC
    Where NN will be the Names increment for the same date.
    And CC will be the count increment for the same date & same name.

    I don't know if it is possible only with formula or not (if required VBA can be applied).

    Thanks in advance for any help.

    Regards
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 11-29-2020, 12:30 PM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,023

    Re: Generating Incremental Order Number based on Date & Condition

    One way:

    =TEXT(A2,"YYMMDD")&TEXT(SUM(INDEX(($A$2:$A2=A2)*($B$2:$B2<>"")/COUNTIFS($A$2:$A2,$A$2:$A2&"",$B$2:$B2,$B$2:$B2&""),0)),"00")&"-"&TEXT(COUNTIFS($A$2:A2,A2,$B$2:B2,B2),"00")
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  3. 11-29-2020, 01:02 PM #3
    excellon
    excellon is offline
    Registered User
    Join Date
    10-02-2019
    Location
    Dhaka, Bangladesh
    MS-Off Ver
    2016
    Posts
    36

    Re: Generating Incremental Order Number based on Date & Condition

    Hi Glenn,

    That was a real quick reply.

    And it worked like magic.

    Hats off to you!

    Thanks again.

    Regards
    Reply With QuoteRegister To Reply
  4. 11-29-2020, 01:03 PM #4
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,023

    Re: Generating Incremental Order Number based on Date & Condition

    You're welcome.


    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
    Reply With QuoteRegister To Reply
  5. 11-29-2020, 01:05 PM #5
    excellon
    excellon is offline
    Registered User
    Join Date
    10-02-2019
    Location
    Dhaka, Bangladesh
    MS-Off Ver
    2016
    Posts
    36

    Re: Generating Incremental Order Number based on Date & Condition

    I have done both.

    Thanks.
    Reply With QuoteRegister To Reply
  6. 11-29-2020, 02:26 PM #6
    excellon
    excellon is offline
    Registered User
    Join Date
    10-02-2019
    Location
    Dhaka, Bangladesh
    MS-Off Ver
    2016
    Posts
    36

    Re: Generating Incremental Order Number based on Date & Condition

    Hi Glenn,

    Can I ask a similar question though it is already marked as solved.

    What will be the formula if I want a 3-digit incrementing number in another column for the matching dates.

    That means, in your file, in column F, I want the values to be:

    001
    001
    001
    001
    002
    002
    002
    002
    002

    Sorry, if I am breaking any rule.
    Thanks in advance.
    Reply With QuoteRegister To Reply
  7. 11-29-2020, 02:33 PM #7
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,023

    Re: Generating Incremental Order Number based on Date & Condition

    No problem.

    =TEXT(SUM(1/COUNTIFS($A$2:$A2,$A$2:$A2&""),0),"000")


    See file.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 11-29-2020, 02:48 PM #8
    excellon
    excellon is offline
    Registered User
    Join Date
    10-02-2019
    Location
    Dhaka, Bangladesh
    MS-Off Ver
    2016
    Posts
    36

    Re: Generating Incremental Order Number based on Date & Condition

    Thanks, Glenn. You are awesome.

    But I was wrong to describe the requirement.

    I have a few dates in column AA having some blanks in between also.

    And I want the values in column V (attached file).

    Please help.

    Sorry for wasting your few moments.

    I know you can do it in seconds maybe.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 11-29-2020, 03:07 PM #9
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,023

    Re: Generating Incremental Order Number based on Date & Condition

    No... minutes, not seconds. I hit a snag!!

    =IF(AA2="","",TEXT(SUM(INDEX(($AA$2:$AA2<>"")/COUNTIFS($AA$2:$AA2,$AA$2:$AA2&"",$AA$2:$AA2,$AA$2:$AA2&""),0)),"000"))
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 11-29-2020, 03:30 PM #10
    excellon
    excellon is offline
    Registered User
    Join Date
    10-02-2019
    Location
    Dhaka, Bangladesh
    MS-Off Ver
    2016
    Posts
    36

    Re: Generating Incremental Order Number based on Date & Condition

    Thanks will not be enough for you Glenn.
    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. [SOLVED] Generating incremental invoice number based on date
    By excellon in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-19-2020, 04:07 PM
  2. Auto generating work order number
    By Shmoedoe in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 10-30-2016, 06:41 PM
  3. Need help auto generating a work order number in a user form
    By Losman in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 01-11-2016, 10:07 AM
  4. Generating order numbers that include current date
    By werdnaegni in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-05-2013, 05:37 PM
  5. Generating List based on Condition
    By andyb16 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-25-2012, 06:37 AM
  6. Unique Work Order number generating
    By bobvanderbilt in forum Excel General
    Replies: 0
    Last Post: 08-19-2012, 10:40 PM
  7. [SOLVED] Generating (in a random order)each number once from a given number
    By Neil Goldwasser in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-02-2005, 07:30 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