Excel Hierarchical List

Did you ever start working on an Excel spreadsheet and realize you needed it to do something you didn’t know how to do?  I’ve been working on the next article in my Excel BTS Project Tracker series.  I needed an Excel Hierarchical List, and I didn’t know how to do that.   I have to learn a couple new things to do that and I wanted to share that with you.

The trial and error approach is an agonizing way to find the key to learning something new.   Often, there is a deadline, your boss wants the spreadsheet done pretty quickly.  What if one of your coworkers recently presented an Excel spreadsheet that was very good, and you feel like yours needs to be equally good or it will suffer by comparison?

Excel Telecom Tricks

I need to list all the BTS and every task for each BTS.  You might need to do something similar.  Examples I can think of include documenting port cards in an RNC, or maybe DS-3 circuit IDs and DS-1 assignments, or mapping trunk group and trunk numbers.  Anyplace where there is hierarchical numbering you might have to do something similar. 

Of course, there are applications for this outside of Telecoms, too.  How about sample tests grouped together, or deliveries addressed to different postcodes, or student IDs in different grades.  I’m sure you can think of many more.

I tell you, these days the workplace can be so competitive.  You might quickly be left behind if you aren’t continually upgrading your skills by reading the right blog.  Excel is definitely one of the skills you need to constantly be improving.

Excel Hierarchical List

Excel Hierarchical List
Excel Hierarchical List

My tracker has 24 BTS.  Yours might have many, many more than that.  The Project Tracker has 9 tasks which to complete at every BTS.  I thought I’d put the BTS number in one column and the task number in the adjacent column, then add the same BTS number in each row with the next task number.  When the last task number is listed the next row starts over with a new BTS number and the first task. (See the image for an abbreviated list.)  That means 2 cells per row times 9 tasks times 24 BTS is 432 cells to fill in.  That’s way more work than I want to do.  So I wondered if there is a way for Excel to do this automatically.

Download Demo Workbook to Follow Along

In these situations, using an Excel Hierarchical List, there is one number which continually cycles, and the other number increments when the first number starts over.  The number which cycles over and over, I’ll call that the  Cycler.  The other one I’ll call the Incrementor.  How to do this?  Because I’m setting this up in columns, I need the count of rows starting with the first row, starting from the beginning.  I also need to know how many items are in the Cycler.  Lastly, I need to know how many Incrementors there are. With these three bits of information, I can create lists like this.

The Cycler

Let’s start by creating the Cycler column.  Whenever you have a list of numbers which increments by 1 and cycles over and over, it should make you think of the Modulus operator in mathematics.  The Modulus operator returns the remainder when a number is divided by a divisor.  The Divisor is the same as the count of items in the Cycler.  So with 9 tasks in my project, the value of my Divisor is 9. 

How about the count of rows?  This is easy to get using Excel’s ROWS function.  ROWS takes a range and returns the count of rows in that range.  As the range will grow row by row, I’ll anchor it on the first row by making the first cell in the range an absolute reference.  Add “$” signs to make this an absolute reference.  The second half of the range is a relative reference.  The first entry in the Cycler column looks like this $G$2:G2, the second $G$2:G3, the third $G$2:G4, and so on.  Notice how the second part of the range reference increments with the row number.

With this incrementing range, the ROWS function returns 1 in the first row, 2 in the second, 3 in the third, and so on.  This gives us the number used by the MOD function.  Here is how MOD is used:

=MOD(number,divisor)

The Number is the count returned by ROWS.  The divisor value is entered into cell C2, which I’ve made into an Excel Named Range called Divisor.  I entered the named range in the formula.

Start with 0 or 1

This won’t quite give us the result we want.  It’s the ever-present Telecoms issue of whether any numbering scheme starts with 0 or 1.   Basically, the Modulus operator starts with 0, while the ROWS function starts at 1.   To align the result of ROWS with the result of MOD, I have to subtract 1 from the ROWS result while also adding 1 to the MOD result. Here is the final formula:

=MOD(ROWS($G$2:G2)-1,Divisor)+1

Incrementor

The incrementor is pretty much the opposite of the Cycler.  The Cycler returns the remainder when divided by the Divisor.  I want the Incrementor to always return the whole number when the rows count is divided by Divisor.  And when the Cycler rolls over and restarts at 1, that’s when the Incrementor should, well, increment.  I  can reuse much of the Cycler formula for the Incrementor formula.

The same formulation of ROWS and Divisor can be used. If the ROWS count is divided by Divisor, that’s about the same as what the MOD function does. If we make it return just the integer part, that is the iteration count. Here is the formula for the first iteration:

=INT(ROWS($F$2:F2)-1/Divisor)+1

To use the sample workbook to create your own hierarchical list,

  1. Enter a value for Divisor in cell C2
  2. In the first row formulas for the Incrementor and Cycler, select both cells
  3. Click and drag the “knuckle” in the lower right corner of the cell in column G
  4. Drag down far enough to produce the number of Incrementors you need.
  5. If you want to avoid the CPU cost of continually recalculating the cells in this list, highlight all cells in your list, copy, the Paste Special – Values. This converts all the formulas into simple numbers.

Although I can now generate hierarchical lists, I failed to completely automate it.  But mostly so.  And by using Excel functions, I reduce the chance of making errors.  Good job!

Conclusion

Using only 3 Excel functions, 2 of them pretty simple, I was able to quickly create a hierarchical list that is error-free and much faster than doing it manually for lengthy lists. I didn’t know how to do this when I started. But it was pretty easy to figure out.

If you know an easier way to do this, please post a comment!


Also published on Medium.

2 Replies to “Excel Hierarchical List”

  1. Hi Russell.

    I have just had a look at your latest (December) post and, broadly, would take a similar approach. I may use tables more than you to hold input data (particularly relevant for the dynamic range post).

    I think your array of ranges (with its use of relative addressing) is somewhat overkill for the task in hand of generating a counter. I tend to assign a named formula ‘k’ to the task, where the name refers to
    = ROW() – ROW(Table1[#Headers])

    I only use that definition within the table (where the rows may be assumed to be aligned). For more general use within multi-cell array formulas I would replace that definition by
    {= ROW(Table1) – ROW(Table1[#Headers])}
    so that the index always starts at 1 irrespective of its location on the worksheet.

    I am not convinced by your description of the data structure as a hierarchical list. I would see it more as a 2D array or cross-tab since the number of ‘cycler’ fields is defined to be the same for every level of ‘incrementor’.

    My formula for ‘Cycler’ would be the same as yours
    = 1 + MOD( k-1, n )
    but I have, in recent years, adopted a slightly different formula for ‘Incrementor’, namely
    = 1 + FLOOR( k-1, n ) / n

    That calculation requires only integer arithmetic but otherwise there is little to choose between it and INT().

    This may not be the level of automation you set out to achieve but at least the table can be extended simply by dragging the resize handle. A check on its ROWS property would not go amiss if you think there is a chance that you or an end user may forget to adjust the table. I will look out for your notifications of posts from now.

    Best Wishes for the New Year
    Peter

    1. Hi Peter, many thanks for your comment.

      I never forced myself to learn Excel Tables, and clearly I should. I have seen elsewhere that in some situations Excel Tables were simpler than the method I used. For example, there may be times when an Excel Table is simpler than using a Dynamic Named Range. Excel Tables might someday be a topic for its own blog post.

      I really appreciate how simply your formulas express the concept, eg = 1 + FLOOR( k-1, n ) / n. This is a much clearer expression than what I presented.

      Many thanks for your contributions! Russell

Comments are closed.