CODEWITHSUNDEEP
excel
garej
garej

Reputation: 565

Hierarchical auto-numbering in Excel

Is there a way to get auto-numbering in column "B" of the form given in the pattern below without VBA? (Densities of "A" and "B" are arbitrary; the only rule is that it cannon be two and more A's 'in a row', and "A" always goes first.)

A   
B   1
B   2
B   3
A   
B   1
B   2
A   
B   1
B   2
B   3

Some auxiliary calculations (columns) if needed are fine.

P.S. The extension of the task is here.

Upvotes: 1

Views: 1148

Answers (2)

Brennan Zubrick
Brennan Zubrick

Reputation: 36

Hi Garej: Based on the example you provided you could accomplish this with an "IF-THEN" formula in excel. Just put your letters in a blank excel document, paste the formula in cell B2, and you should be good. You need hardcode ="A" in the formula to be whatever "A" is. Here is the formula:

=IF(A2="A","",IF(A2=A1,B1+1,1))

If you don't want to hard code the ="A", you could also change the beginning from:

  A2="A"

to 

 A2=$A$1

which would allow you to have a dynamic value instead of "A" so the full code would be....

 =IF(A2=$A$1,"",IF(A2=A1,B1+1,1))

The logic behind this code is as follows: Look at the letter in the current row... IF the letter in the current row is "A" [or matches the contents of $a$1], then leave the number blank. IF the letter in the current row is the same as the letter in the previous row, then add one to the number in the row above. IF the letter in the current row is different than the row above [and is not "A" or $A$1], then start over at 1.

Excel Solution

Upvotes: 2

Grade 'Eh' Bacon
Grade 'Eh' Bacon

Reputation: 3823

Put the following starting in cell B2 (B1 can be hardcoded as 1):

=IF(A2="A","",if(A1="A",1,B1+1))

This simply looks at the prior row - if Column A in the prior row said "A", then we know that this current row is a new one starting at 1. If it wasn't "A" in the row above, we know this is a continuation of the current count. It first checks the current row - if the current row is "A", we know we want this one to be blank.

Upvotes: 1

Related Questions