Questions? We have answers.

Data Formatting Guide: The Basics

Article author
Kate H
  • Updated

Summary

This guide is to help you understand the best ways to approach every file you see to make it easy to ingest by MindBridge! 


Solution

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

  1. Remove unnecessary header content (any 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)
    mceclip0.png

     

  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) mceclip3.png

     

  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):mceclip0.png

     

    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:mceclip1.png

     

  4. Verify that the debit and credit columns balance once the unnecessary data rows have been filtered out.mceclip5.pngmceclip7.png

     

  5. Flip signs if necessary to make debits and credits all default positive balance
    • 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:mceclip8.png
    • Now the flipped sign Credit total equals the Debit total. Replace the original Credit column values with these new values:
      mceclip10.png
    • If only a certain range of account numbers need to have signs flipped, see this guide to flipping signs for a range of accounts.

     

  6. Fill in the blanks of the new column containing the 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 on Go To Special...
      mceclip11.png
    • Select Blanks, then click OK
      mceclip12.png
    • 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: mceclip15.png
    • Enter a reference to the cell above, =A2 in this example, then hit Ctrl+↵Enter (Windows) or ⌘Cmd+↵Enter (Mac) on your keyboard:mceclip13.png
      This will fill the formulas down in all the blank cells in the selection, thereby dragging down the account information
      mceclip14.png
    • Finally, select the column, copy and paste values: mceclip16.png

     

  7. 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 - character, select the account column, then under Data, Data Tools, select Text to Columns
      mceclip19.png
    • In the Text to Columns wizard, make sure Delimited is selected
      mceclip20.png
    • Under Other, type "-"
      mceclip21.png
    • 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:
      mceclip22.png
    • 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
    mceclip23.png
    • The split-up account number and account description will now fill the new columns:
      mceclip24.png
    • Rename the new columns as applicable:
      mceclip26.png

     

  8. 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).

    Anything else on your mind?

    Head over to the community with your thoughts and inquiries! You can also chat with us, or, submit a request for further assistance.


Share this:

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.