title: Categorizing bank transactions in Excel
body: <p>I have an exported list of bank transactions in Excel, which I'd like to categorize as quickly and simply as possible.  I suspect this is possible just with array formulas, but a VBA function would be equally useful.</p>
  
  <p><strong>The Scenario</strong></p>
  
  <p>My spreadsheet header looks like this:</p>
  
  <pre><code>| A    | B           | C      | D        |
  ==========================================
  | Date | Description | Amount | Category |
  ------------------------------------------
  </code></pre>
  
  <p>Date, Description and Amount come pre-populated from my bank.  I fill in the category for each transaction in column D.</p>
  
  <p>This is fine, but time-consuming, because each category has to be entered individually and manually.</p>
  
  <p><strong>The Need</strong></p>
  
  <p>I want categories to auto-populate in rows where I haven't entered a manual category, based on rules that are dynamically generated and applied.</p>
  
  <p>The output I want looks like this:</p>
  
  <pre><code>| A        | B             | C       | D           | E         | F           |
  ==============================================================================
  | Date     | Description   | Amount  | Manual cat. | Rule      | Auto cat.   |
  ------------------------------------------------------------------------------
  | 04/08/12 | Starbucks NYC |  -$5.42 | Coffee      | starbucks | Coffee      |
  | 04/09/12 | Wal-Mart 468  | -$54.32 | Supermarket | wal-mart  | Supermarket |
  | 04/10/12 | Starbucks SF  |  -$3.68 |             |           | Starbucks   |
  </code></pre>
  
  <p>As you can see, I have entered 'manual' categories in Column D.  Wherever I have done this, I have entered a categorization 'rule' in Column E.  Excel is then using my entries to auto-populate Column F,</p>
  
  <p>The logic is simple:</p>
  
  <ul>
  <li><p>Where I enter a manual category, Excel does two things:</p>
  
  <ol>
  <li>Populates my manual category in Column F.</li>
  <li>Create a rule using the text entered in Column E.</li>
  </ol></li>
  <li><p>Where Excel comes across a description that contains text used in one of my rules, it populates the relevant category in Column F.</p></li>
  </ul>
  
  <p><strong>The Benefit</strong></p>
  
  <p>This would make it very simple to whiz down the list of transactions, adding categories and associated rules.  Transactions without a category would be auto-populated where a rule applies, and left blank where a rule does not apply.  Transactions where a rule has been applied giving the wrong category could be corrected, and a new rule supplied.</p>
  
  <p><strong>My best attempt so far</strong></p>
  
  <p>I have created a way of doing this using only formulas, but it has three drawbacks:</p>
  
  <ol>
  <li>It necessitates the creation of as many columns as transactions. </li>
  <li>There is no convenient way of listing the categories and associated rules.</li>
  <li>There is no way of changing the order the rules are applied in.</li>
  </ol>