• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Visio Guy

Smart graphics for visual people




  • Home
  • Hire Me
    • Hire Me
    • Résumé
  • Products
    • Products
    • Bubble Revision Shape
    • Layers to Pages Utility
    • Rack Unit Dimension Line
    • Radial Elements Tool with up to 100 Wedges
    • Text on a Circle Visio SmartShape
  • Index
    • Articles by Date
    • YouTube – VisioGuy
    • Download Information
    • Suggestion Box
    • Shop
    • Visio Art
    • Visio Links
    • – Visio Shapes & Stencils
    • – Visio Templates & Drawings
  • About
    • About
    • Donate
    • GitHub
    • jsFiddle
    • Reading List
    • Subscribe & Follow
      • – E-mail
      • – facebook
      • – Twitter
      • – RSS
    • Privacy Policy
  • Discussion Forum
You are here: Home / Visio Content / Shapes / How to Count the Number of Items in a List

How to Count the Number of Items in a List

May 25, 2009 By Visio Guy 9 Comments

count-items-in-listI’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
  • Tweet
  • More
  • Pocket
  • Share on Tumblr
  • Print
  • Email

No related posts.

Filed Under: Shapes, ShapeSheet

Previous Post: « VBA Class Property Generator
Next Post: Rate (Your) Network Diagram »

Reader Interactions

Comments

  1. Christoph says

    March 22, 2010 at 4:56 pm

    I’m working on a similar shape and found your article very interesting.
    I guess that you never released this shape?

    Is it possible to get this from you if you still have it?

    Thank you

    Christoph

  2. Visio Guy says

    March 22, 2010 at 5:20 pm

    Servus Christoph,

    There was no shape to release. The article tells how to implement a feature in Visio’s ShapeSheet…in whatever shape you would like!

    – Chris

  3. Christoph says

    March 22, 2010 at 6:06 pm

    I was referring to

    “It should be on offer within then next week or two.”

    I’m currently thinking of drawing sub-shapes (without visio) based on an user cell entry of a shape.

    Let’s assume I have a field of hyperlinks in a user cell separated by semicolons.

    What I want do to is do have this hyperlinks as a list vertically aligned left or right beside the shape and the user should be able to click each hyperlink even after I exported the graphic as html.

    Do you think this is possible without vba?

    Thank you
    Christoph

  4. Visio Guy says

    March 22, 2010 at 6:09 pm

    Heheh. Forgot about that. I’m not even sure anymore which shape I was talking about!

  5. Christoph says

    March 22, 2010 at 6:50 pm

    So, you’ve forgotten this shape

    That’s live.

    Anyway do you think that it is possible to solve the other part of my mail?

    Regards

    Christoph

  6. Visio Guy says

    March 23, 2010 at 2:10 pm

    Hi Christoph,

    Actually I did post it on August 4th, 2009. It’s this article + download: Automatic Chevron Process Shape

  7. Christoph says

    March 23, 2010 at 2:54 pm

    Cool.

    I came to same solution more or less. That’s wonderful.

    Danke & Fürti

    Christoph

Leave a Reply Cancel reply

Primary Sidebar

Buy Über Bubble Revision Shape
Purchase

Categories

Buy my book!

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Tag Cloud

A/V Artistic Effects BPM Code Connectors Control Handles Countries Custom Patterns Custom Properties Data Graphics Data Linking Data Visualization David Edson David Parker Fill Format Formulas Functions Geometry Gradient Images Links Maps Multi-shapes Network Programming repeating shapes Resources Right-Click Actions Scale Shape Data ShapeSheet ShapeSheet Formulas ShapeSheet Functions SharePoint shiny SmartShapes Sport Sports Text Themes Tools Transparency User-defined Cells Visio 2007 Visio SmartShapes

Top Posts & Pages

  • - Visio Shapes & Stencils
  • - Visio Templates & Drawings
  • Amazon AWS Visio Shapes
  • Dynamic Updating Org Charts in Visio!
  • Text on a Circle Visio Shape
  • Bubble Revision Shapes
  • Crayon Visio Network Shapes, Revisited
  • Map of World
  • Sankey Diagram Shapes for Visio
  • AV Engineering Diagrams with Symbol Logic ECAV

www.visguy.com - Visio Guy - since 2006

 

Loading Comments...