Simple way to create conditional formatting rules for 100 different sets of numbers?

Discussion in 'Mac Apps and Mac App Store' started by jent, Apr 7, 2011.

  1. jent macrumors 6502a

    jent

    Joined:
    Mar 31, 2010
    #1
    I have over ten thousand rows of data I'm comparing. The first two columns are ten-digit codes, and after that is a bunch of extra information. The ten-digit codes are organized in such a way that 0101 is the first category, 0102 is the second category, all the way to 9998. If you break these four digits down further, you see that the format is as such: XXYY, where XX is the master category and YY is the sub-category. There are many gaps—for example, the first category/sub-category is 0101 but it stops at 0106, then goes to the next master category, 02, whose range is 0201 to 0210.

    I need to break down my list of over ten thousand items by category and sub-category, and I thought conditional formatting would be a good way to do that. Is there a way, in Excel 2011 or Numbers '09, that I can color the cells based on the first four digits they have (or by the whole ten-digit number, for example: 0101000000 to 0101999999), without needing to manually create a new rule for each group of four numbers? That would be over a hundred rules. And once I do that, are there functions that can analyze data within those groups? Thanks for any suggestions!
     
  2. GGJstudios macrumors Westmere

    GGJstudios

    Joined:
    May 16, 2008
    #2
    In Excel, conditional formatting is limited to 3 rules.
     
  3. CylonGlitch macrumors 68030

    CylonGlitch

    Joined:
    Jul 7, 2009
    Location:
    SoCal
    #3
    In Numbers, at least, you can have different rules on different pages that look at the data on the top level page. If it matches the rule, you have it set the cell to a color, and then set all of the default colors to CLEAR. Then you line up the pages one on top of another. The first page that matches the rule (only one should, right?) will set the color. With 100's of rules, it may be tricky, but I think numbers allows for 10 rules or so per cell; so 10 sheets isn't that bad. The hard part, making sure everything is aligned perfectly, and if you change a column width on one page, it has to be changed on ALL of the pages.

    Not sure how I would do it in Excel; not even sure if it is possible. :(
     
  4. CylonGlitch macrumors 68030

    CylonGlitch

    Joined:
    Jul 7, 2009
    Location:
    SoCal
    #4
    For analyzing the data, I would do something different. I would create a rules sheet that checks each row against the rules (I would have maybe 100 columns of rules, one for each rule so that you can see which rules turn true or false based on the data [easier to debug]) and then apply that down for every row of data. Maybe just put a 1 in the column if the rule is true, and a 0 if it is false.

    That way you can then run statistics on all of the rule conditions and see where things fall. Using the 1/0 approach you can then just sum the columns to tell you how many items fell into each rule. Also you can sum across the row to tell you how many rules that item applied to.
     

Share This Page