Summary
Learn about the process of formatting an excel file from Microsoft Dynamics Great Plains (GP).
Configuration Details
The document has two parts:
The raw excel export from Microsoft GP will look like this:

Part 1: Creating the Opening Balances File
- Begin by deleting the rows from the top to “Ranges:” This should be the top 5 rows (marked in red below). Be sure not to delete the line that says “Account:” first (marked in green below).
- Insert two columns at the start of the file, and name them Account Number and Account Description.
- Copy the Account Numbers and Descriptions using the formulas below:
- Account Number Formula: Column A: =IF(C2="Account:",D2,A1)
Note: If the cell contains the text “Account:”, then take the account number. If it does not contain “Account:”, copy from the cell above. This will enable us to copy account numbers until we reach a new account number. - Account Description Formula: Column B: =IF(C2="Account:",G2,B1)
Note: We’re re-using the above formula, but now copying the account description.
- Account Number Formula: Column A: =IF(C2="Account:",D2,A1)
- Copy both formulas to the bottom of the spreadsheet.
- Filter the dataset by column H for anything that contains “Begin.”
- Copy and Paste the Values into a new spreadsheet and then delete columns C-H.
- Sort column I, "Balance" onto Number Format (explained above).
- Save the file as the “FY## - Opening Balance Upload”.
Your Opening Balances File is now ready for import into MindBridge.
Part 2: Formatting the General Ledger
- Copy and paste the Account Number and Account Description columns as values. This removes the formulas from the newly created columns and keeps only the values.
- Rename your columns following the format below:
Column New Column Name Column C Posted Date Column D Transaction ID Column E Batch Column F Source Column H Org Master Column I Orig Master Name Column K Debit Column L Credit - Remove non-posted dates and non-required text by filtering the Posted Date column, deselecting the date values, and removing the non-date text:
- Filter the Posted Date column and deselect posted date values:
- Remove non-posted date text:
- Filter the Posted Date column and deselect posted date values:
- Create a new column to the left of the batch column and name it Transaction Type.
- Use the following formula to capture the transaction type: =LEFT(F2,5)
Note: We're copying the first five characters from the Transaction Type for the batch number. - Copy and paste the values for the new Transaction Type column to remove the formula:
- Delete the unused columns.
You are now ready to import your GL file into MindBridge.
Anything else on your mind?
Log in to MindBridge to chat with us, submit a request, or reach out to your assigned Customer Success Manager.