+ Reply to Thread
Results 1 to 12 of 12

Restart count everytime value in adjacent column changes

  1. #1
    Registered User
    Join Date
    03-02-2017
    Location
    Roerstreek
    MS-Off Ver
    2013
    Posts
    21

    Restart count everytime value in adjacent column changes

    Hello dear excelforum users,

    I have a question regarding the following:

    I have two columns Column A and Column B.
    Column A contains the first part of a productcode, it is the main productgroup. Main productgroups can be either 10,20 ,30 ,40, 50, 60, 70, 80 or 90
    Colunn B contains the second part of a productcode, it is a subgroup of the main productgroup. Subgroups can be either 01, 02, 03, 04 all the way to 99.
    Any combination of main and subgroup is possible but this doesn't mean the combination already exists. So 10-01 and 20-01 and 30-01 are possible.

    Now, in column C I want sequential numbering from 00001 all the way to 99999.
    So, 10-01-00001 and 20-01-00001 and 30-01-00001 are possible.
    The problem is, it has to restart the number at 00001 everytime the subgroup changes (I believe the maingroup is of no consequence).

    Is there a way to automate this process? The records is about 15.000 entries big and keeps growing, so doing it manually is not really an option.
    For an example I created by hand, please see the attached file.

    Hopefully I have been clear enough, if more information is required please ask.

    With kind regards and thanks in advance,
    Niklas
    Attached Files Attached Files

  2. #2
    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
    43,513

    Re: Restart count everytime value in adjacent column changes

    In c2:

    =IF(B2=B1,TEXT(D1+1,"00000"),TEXT(1,"00000"))
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 04-06-2017 at 06:22 AM.
    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.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2403 (Windows 11 22H2 64-bit)
    Posts
    78,073

    Re: Restart count everytime value in adjacent column changes

    Try this in C2 copied down:

    =TEXT(IF(AND([@Maingroup]=A1,[@Subgroup]=B1),C1+1,1),"00000")
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Registered User
    Join Date
    03-02-2017
    Location
    Roerstreek
    MS-Off Ver
    2013
    Posts
    21

    Re: Restart count everytime value in adjacent column changes

    Hello Glenn,

    thank you for your swift reply.
    Regarding the sequential numbering in your example, it is very much correct.
    Problem is I have made a mistake is my problem description. Looking at you solution, I believe the maingroup is of consequence.
    In the attached example file, in column C "Sequential" the way it should be is put in manually.

    Thanks again for your swift answer, hopefully I am making myself clear enough.

    With kind regards,
    Niklas
    Attached Files Attached Files

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2403 (Windows 11 22H2 64-bit)
    Posts
    78,073

    Re: Restart count everytime value in adjacent column changes

    Did you try my solution? It works in your file.

    =TEXT(IF(AND([@Maingroup]=A1,[@Subgroup]=B1),C1+1,1),"00000")

    Change to this if you no longer want the leading zeroes:

    =IF(AND([@Maingroup]=A1,[@Subgroup]=B1),C1+1,1)

  6. #6
    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
    43,513

    Re: Restart count everytime value in adjacent column changes

    Try this:

    =TEXT(IF(AND(B2=B1,A2=A1),D1+1,1),"00000")

  7. #7
    Registered User
    Join Date
    03-02-2017
    Location
    Roerstreek
    MS-Off Ver
    2013
    Posts
    21

    Re: Restart count everytime value in adjacent column changes

    Hello both,

    thanks for both your swift replies.

    AliGW, I modified you formula a bit because it gave me an error. I modified it to:
    =TEXT(IF(AND([@Maingroup]=A2;[@Subgroup]=B2);C2+1;1);"00000")
    It resulted in zeroes. You are right though that I do want to keep the leading zero's.

    Glenn, your solution returned #NAME?

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2403 (Windows 11 22H2 64-bit)
    Posts
    78,073

    Re: Restart count everytime value in adjacent column changes

    In C2 copied down:

    =TEXT(IF(AND(A2=A1;B2=B1);C1+1;1);"00000")

    NO ISSUE HERE in your own file!!!

  9. #9
    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
    43,513

    Re: Restart count everytime value in adjacent column changes

    You must be doing something wrong.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    03-02-2017
    Location
    Roerstreek
    MS-Off Ver
    2013
    Posts
    21

    Re: Restart count everytime value in adjacent column changes

    Well,

    apperantly I am quit the stupid guy :p

    All I had to do was change the text in the formula to Dutch since I use a Dutch version of EXCEL.
    Both your methods work perfectly! Thank you very much for your patience and quick and correct answers.

    With kind regards,
    Niklas

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2403 (Windows 11 22H2 64-bit)
    Posts
    78,073

    Re: Restart count everytime value in adjacent column changes

    You're welcome!

  12. #12
    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
    43,513

    Re: Restart count everytime value in adjacent column changes

    Graag gedaan!!

+ 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. Replies: 3
    Last Post: 12-16-2015, 05:56 PM
  2. Count column value then adjacent column cell fill color
    By MIGARDEIN in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-09-2015, 01:13 AM
  3. Restart a Count within a Column
    By cookthebooks80 in forum Excel General
    Replies: 3
    Last Post: 07-06-2014, 11:46 AM
  4. [SOLVED] Restart and increment numbers in column based on changed value in adjacent column
    By Kespin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-03-2013, 06:26 PM
  5. count sessions then restart 365 days after - help
    By itsnotfair in forum Excel General
    Replies: 2
    Last Post: 06-29-2011, 09:31 AM
  6. Replies: 4
    Last Post: 10-21-2010, 11:05 AM

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