Reputation: 71

Excel auto increment id by 01 when cell changes value

I have a file that has three columns. ID which is blank, Name which has some names and PARENT_ID which stores the parent ID of the Name.

What I want to do is at the column ID to take the Parent id and add a two digit number which will increment by 01. For example we have 10 cats with parent id 1. I want at the column ID to take the parent id "1" and then add "01" for the first cat, "02" for the second cat and so on. So at the column ID I will have foreach cat an auto incrementing value 101,102,...110. Then the dogs start, so it will take the parent id which is "2" and start again foreach dog do add incrementig values 201,202... etc. Then the fish 301,302

Here is an example of what I am trying to do.

ID   NAME   PARENT_ID
101  cat    1
102  cat1   1
103  cat2   1
104  cat3   1
105  cat4   1
106  cat5   1
107  cat6   1
108  cat7   1
109  cat8   1
110  cat9   1
111  cat10  1
201  dog    2
202  dog1   2
203  dog2   2
204  dog3   2
205  dog4   2
206  dog5   2
301  fish   3
302  fish   3

The column name is not of concern, I just placed it for you to understand better.

I am not familiar with visual basic and I tried to accomplish this with formulas but with no luck.

Thank you for any help.

Upvotes: 0

Answers (3)

Steven K. Mariner

Reputation: 441

Not a VBA approach, but a formula approach -- If this is something like what you're looking for:

 Row  A    B      C          D            E          F
 1    ID   NAME   PARENT_ID  RunningName  RunningID  NewID
 2    101  cat            1  cat                  0  cat
 3    102  cat1           1  cat                  1  cat01
 4    103  cat2           1  cat                  2  cat02
 5    104  cat3           1  cat                  3  cat03
 6    105  cat4           1  cat                  4  cat04
 7    106  cat5           1  cat                  5  cat05
 8    107  cat6           1  cat                  6  cat06
 9    108  cat7           1  cat                  7  cat07
10    109  cat8           1  cat                  8  cat08
11    110  cat9           1  cat                  9  cat09
12    111  cat10          1  cat                 10  cat10
13    201  dog            2  dog                  0  dog
14    202  dog1           2  dog                  1  dog01
15    203  dog2           2  dog                  2  dog02
16    204  dog3           2  dog                  3  dog03
17    205  dog4           2  dog                  4  dog04
18    206  dog5           2  dog                  5  dog05
19    301  fish           3  fish                 0  fish
20    302  fish           3  fish                 1  fish01

...then I used the following formulas:

D2:  =if(a2="","",if(sum(C2)<>sum(C1),trim(B2),trim(D1)))
E2:  =if(a2="","",if(sum(C2)<>sum(C1),0,sum(E1)+1))
F2:  =if(a2="","",trim(D2)&if(sum(E2)=0,"",text(E2,"00")))

I then replicated those cells down the column as far as I cared to go. You can make the "Running" columns a very light grey text color so as to render them non-distracting to the user.

Hopefully this can help inspire you to craft a solution that works for you.

Upvotes: 0