# Restart count everytime value in adjacent column changes

1. ## 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.

With kind regards and thanks in advance,
Niklas

2. ## Re: Restart count everytime value in adjacent column changes

In c2:

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

3. ## 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")

4. ## Re: Restart count everytime value in adjacent column changes

Hello Glenn,

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

5. ## 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. ## Re: Restart count everytime value in adjacent column changes

Try this:

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

7. ## 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.

8. ## 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. ## Re: Restart count everytime value in adjacent column changes

You must be doing something wrong.

10. ## 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. ## Re: Restart count everytime value in adjacent column changes

You're welcome!

12. ## Re: Restart count everytime value in adjacent column changes

Graag gedaan!!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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