How to Count the Number of Items in a List

I’ve been working on a new multi-shape that you will hopefully find useful for presentations and process documentation. The Automatic Chevron Process Shape uses a new ShapeSheet technique for counting items in a list that I’ll share with you here.

As I was building it, I was faced with the challenge of calculating how many items are in a list.

It’s easy enough to enter “One;Two;Three;Four” into a shape’s text or into a ShapeSheet cell, but how do you know that there are four items in the list?

Manipulating Lists and Text in the ShapeSheet

Any time you start messing around with lists in the ShapeSheet, you’ll employ the list functions: INDEX and LOOKUP to help you out. INDEX will help you find a specific item in a list and LOOKUP will tell you the position of an item in a list.

User.index = INDEX( 2, "A;B;C" ) = "C" ...0-based!
User.lookup = LOOKUP( "B", "A;B;C" ) = 1 ...0-based!

But if you are turning the visibility of geometry or sub-shapes on and off in response to the number of items in a list, the task gets trickier.

The Visio ShapeSheet has a number of text-manipulation functions. If you haven’t discovered them yet, here’s a list to tempt your curiosity:

• BLOB
• CHAR
• INDEX
• LEFT
• LEN
• LISTSEP
• LOOKUP
• LOWER
• MID
• REPLACE
• REPT
• REWIDEN
• RIGHT
• SHAPETEXT
• STRSAME
• STRSAMEEX
• SUBSTITUTE
• TEXTHEIGHT
• TEXTWIDTH
• TRIM
• UNICHAR
• UPPER

That’s a good set of tools, but unfortunately there isn’t a function for counting the numbers of a list, so it is difficult to build a SmartShape that can automatically accommodate a varying number of list items.

How NOT to Count Items

When I first thought about tackling this problem, I thought; “Oh no, I’m going to have to do some sort of brute-force hack to figure this out.” Perhaps a block of user cells that test for the existence of an “ith” item.

• If there’s no 2nd item in the list, then there is 1 item
• …
• If there’s no 5th item in the list, then there are 4 items
• If there’s no 6th item, then there are 5 items
• …
• If there’s no 20th item, then there are 19

Rather ugly and inelegant, but it would work. And since the ShapeSheet is a fixed matrix that can’t really do loops, there are a lot of cases where you have to pre-build a number of elements, cells, or formulas so this isn’t totally out of the question.

But you’d end up with a less-than-beautiful bank of “what-if” user-cells that did something like this:

User.items = "A;B;C"
User.isItem0 = IF( ISERR( 0 + INDEX( 0, User.items, ";", 0 ) ), 1, 0 )
User.isItem1 = IF( ISERR( 0 + INDEX( 1, User.items, ";", 0 ) ), 1, 0 )
User.isItem2 = IF( ISERR( 0 + INDEX( 2, User.items, ";", 0 ) ), 1, 0 )
User...
User.total = User.isItem0 + User.isItem1 + User.isItem2 + ...

These formulas are a bit convoluted, to say the least. The extended form of INDEX specifies the list separator “;” and a return value if no item is found (the zero). So if an item is found, a string is returned, if not then zero is returned. We then check for the error of adding 0 to a string using ISERR. If we get an error, that means we found an item. What a hack!

And the whole thing breaks if the items list contains actual numbers, a-la: “1;2;3” because Visio tries to make numerical sense of strings if at all possible, so the whole ISERR bit will fail.

Bottom line: don’t use the above method!

Count Smarter!

Then I thought about the length of the list. The number of characters in the list is the number of characters in all the items, plus “items minus one” times the length of the list-separator. The length of “A;B;C” is three-plus-two. Then length of “One;Two;Three;Four” is fifteen-plus-three.

Well, we know the length of our list-separator, since we define it ourselves. And we can get the length of the entire list using the LEN function. Then we can substitute “” for our list-separator to get the length of the list without the list-separators in it.

Here’s how we would calculate the difference in lengths:

User.items = "A;B;C"
User.noSep= SUBSTITUTE( User.items, ";", "") = "ABC"
User.diff = LEN( User.items ) - LEN( User.noSep)  = 2

So the number of items is User.diff + 1 = 3.

We can streamline this:

User.items = "A;B;C"
User.itemCt = LEN( User.items )-LEN( SUBSTITUTE( User.items, ";", "" ) ) + 1

This can be generalized to allow for any type of list-separator, of any length:

User.items = "A;B;C"
User.sep = ";"
User.itemCt = ( LEN( User.items )-LEN( SUBSTITUTE( User.items, User.sep, "" ) ) ) / LEN( User.sep ) + 1

You could replace User.sep with Prop.Sep so that users of the shape could enter their own list-separator via a Shape Data field (custom property field). And User.items  could also come from a Shape Data field or even the shape’s text!

Related Articles

If you are interested in shapes that “grow” according to user-input, you might be interested in the following articles. The free downloads in these posts don’t use the list-counting method I’ve outlined here, but they could be easily adapted to use it, if a designer wanted to manipulate the shapes with data lists.

Have a look at:

• Turn It Up With the NW Airlines Volume Gauge!
• Number Your Pages Visually
• Pre-wired Sankey Diagram Shapes
• Marketing Pyramid Shapes
• Circular Multi-arrows