# Data Formatting Guide - Tudor Jonathon Plowman-Samson
• Updated

This guide is broken into two parts:

1. Formatting an Opening Balance to Import into MindBridge
2. Formatting a General Ledger to Import into MindBridge

A raw Excel export from Tudor will appear look like this: # Part 1: Formatting an Opening Balance to Import into MindBridge

Begin by filtering the “opening balance” in the column containing the opening balance (column F in this example). Copy and paste the filtered opening balances into a new workbook and save it as a new file for import. Consolidate debit and credit values into a single column called “Balance” by using the formula below. Debits will be positive, credits will be negative.

=IF(ISNUMBER(I2),I2,K2*-1)

Note: This formula searches for an available debit value. If a debit value is present, it will be used. If no debit value is found, then the credit value is multiplied by the negative one to create a negative value. Copy and paste the values for a new “Balance” column. Remove all columns except "Account Number", "Account Description" and "Balance." # Part 2: Formatting the General Ledger

Save a copy of the General Ledger (GL) file as a .csv. Re-open the newly saved .csv file in Excel, which is now formatted as shown below: Remove all rows above the “date” cell. Create two new columns named "Account Number" and "Account Description." Copy the account number by using the following formula:

=IF(IFERROR(SEARCH("account",C2),FALSE), MID(C2,10,4),A1)

Note: this formula is searches for the text “account” in column C.
If “account” is found, then the formula will take the four digit account number by counting 10 characters into the cell, then taking the next four digits.
If “account” is not found, the formula copies the cell above in the B column. This process allows for the formula to be copied down the entire GL. Copy the account description using the following formula:

=IF(IFERROR(SEARCH("account",C2),FALSE), MID(C2,24,100),B1)

Note: This formula uses the logic applied above, but instead captures the account description. Copy both formulas to the bottom of the worksheet, then copy and paste each column as values to remove the formulas. Remove unnecessary rows of data by filtering the “Date” column.

Using the filter, deselect the date values: Delete the filtered rows: Remove blank columns and rename remaining the following columns: Your final data file will look like this: 