Summary

Learn the best ways to approach your files so MindBridge can easily import and read them.

Overview

Let's start with approaches to more common issues when formatting simpler files:

1. Remove unnecessary header content (any blank rows or rows unrelated to the required fields or other transaction-specific column information). Be sure to keep the first row as a header row (containing the name of each column)
   
   
   
2. Remove any rows that pertain only to totals or balances (i.e. “Open balance”, “closing totals”), or that have a blank posted date (each transaction must have an affiliated posted date)
   
   
3. Date column note: especially in larger files, sometimes dates are not always in an acceptable format even if they might look fine at first glance. Sometimes dates change format far down in the middle of the file (violation of row requirement 2 of the Data Requirements):
   
   You can select the entire column, hit Ctrl+1 on PC or ⌘1 on Mac for Format Cells to set all dates to the preferred YYYY-MM-DD format:
   
   
4. Verify that the debit and credit columns balance once the unnecessary data rows have been filtered out.
   
   Debit and Credit columns must contain default positive values. 
   • Insert a new column, copy the values in the Credit column (or whichever column has the amounts whose signs need to be flipped, then Paste Special -> Subtract in the new column:
     
     
   • Now the flipped sign Credit total equals the Debit total. Replace the original Credit column values with these new values by copying and pasting as values:
     
     
     
   • If only a certain range of account numbers need to have signs flipped, see this guide to flipping signs for a range of accounts. Flip signs if necessary to make debits and credits all default positive balance.
5. Fill in the blanks for account number and description, so that each individual transaction row has a properly designated account number and description beside each transaction (i.e., dragging down to ungroup).
   • From Home, go to Editing -> Find & Select, click Go To Special...
     
     
     
   • Select Blanks, then click OK
     
     
     
   • Without unselecting (i.e., don't use the keyboard and don't click on any of the cells within the worksheet), navigate to the first blank cell by only using the scrollbars or mouse scroll. It should already be selected by default, cell A3 in this example:
     
     
   • Enter a reference to the cell above, =A2 in this example, then hit Ctrl+↵Enter (Windows) or ⌘Cmd+↵Enter (Mac) on your keyboard:
     
     This will fill the formulas down in all the blank cells in the selection, thereby dragging down the account information.
     
     
     
   • Finally, select the column, copy and paste values:
     
     
6. Split the account number and description into two separate columns if not already in that format. Note the account number is not always the same length within the same file.
   • Insert two new columns.
   • Then, noting that in this example, account numbers are separated from account descriptions by a delimiter "-", highlight the account column, then under Data, Data Tools, select Text to Columns.
     
     
     
   • In the Text to Columns wizard, make sure Delimited is selected
     
     
     
   • Under Other, type "-"
     
     
     
   • To preserve account numbers as they are (i.e., keep leading and ending 0s and . characters and prevent conversion into numbers), select Text column data format for the account number:
     
     
     
   • Change the destination to $B$1 (or the address of the first cell of the first blank column created) to not overwrite the original data
     
     
   • The split-up account number and account description will now fill the new columns:
     
     
     
   • Rename the new columns as applicable:
     
7. Delete unnecessary and empty columns that contain information that is not considered useful for the analysis (if unsure and the file is not too large, leave the columns for later reference information for the client).