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:

1. 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).
   
   
   
2. Insert two columns at the start of the file, and name them Account Number and Account Description.
   
   
   
3. Copy the Account Numbers and Descriptions using the formulas below:
   1. 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.
   2. Account Description Formula: Column B: =IF(C2="Account:",G2,B1)
      
      Note: We’re re-using the above formula, but now copying the account description.
4. Copy both formulas to the bottom of the spreadsheet.
   
   
   
5. Filter the dataset by column H for anything that contains “Begin.”
   
   
   
6. Copy and Paste the Values into a new spreadsheet and then delete columns C-H.
7. Sort column I, "Balance" onto Number Format (explained above).
8. Save the file as the “FY## - Opening Balance Upload”.
   

Your Opening Balances File is now ready for import into MindBridge. 

1. 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.
   
2. 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

   
   
   
3. 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:
     
     
     
4. Create a new column to the left of the batch column and name it Transaction Type.
5. 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.
6. Copy and paste the values for the new Transaction Type column to remove the formula:
   
   
   
7. 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.