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?

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

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!

Download Demo Workbook