CODEWITHSUNDEEP
excel
garej
garej

Reputation: 565

Hierarchical auto-numbering (with three levels) in Excel

What if we have a three-level hierarchy and need to enumerate only "B"s (according to the pattern), but some "C-"s interfere with "B"s.

Problem: to get column B result from a given column A. 

A+  
B   1
B   2
С-  
B   3
A+  
B   1
С-  
B   2
A+  
С-  
B   1
B   2
B   3

P.S. The task arose from the need to enumerate complex hierarchy in Excel.

Imagine that A - level 1; B - level 2; C - level 3. (with some abuse of logic in the example above as C- in the pattern goes after A, which in practice is usually not the case).

The simple case of two-level hierarchy is shown here.

Upvotes: 0

Views: 2165

Answers (2)

Dan Donoghue
Dan Donoghue

Reputation: 6206

I also went with a helper column (as much as I detest them personally) to show the row of each A+

Put this in C1: =ROW(A1)

Put this in C2: =IF(A2="A+",ROW(A2),C1)

It uses an expanding range with a rebasable starting point. Drag down as far as your data goes.

Put this in B2: =IF(OR(A2="C-",A2="A+"),"",IF(A1="A+",1,MAX(INDIRECT("B" & C2 & ":B" & ROW(A1)))+1))

Drag down as far as your data goes.

Hope that helps. Here are the results I received:

A+      1
B   1   1
B   2   1
C-      1
B   3   1
A+      6
B   1   6
C-      6
B   2   6
A+      10
C-      10
B   1   10
B   2   10
B   3   10

Upvotes: 1

Grade 'Eh' Bacon
Grade 'Eh' Bacon

Reputation: 3823

Easiest would be to add in two intermediary helper columns, the first of which we'll call column C. Here, we will count only which "A+" we're on, like so [starting at C2; C1 is hardcoded as 1]:

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

This will increment every time a new row has "A+" in column A.

Then column D will track the highest # reached so far, for that iteration in column C [starting at D2; D1 is hardcoded at 1]:

=IF(A2="A+",0,if(A2="B",B2,D1))

This will restart at 0 for each new "A+", and for each "B" it will take the value shown in column B. Then for each "C", it will simply repeat the value from the row above (the previous "B" reached).

Finally you can put in your sort, as follows [starting at E1]:

=IF(A1="B",B1,"")

This will show BLANK for either "A+" or "C", and will show the B-count if column A = "B".

Upvotes: 2

Related Questions