Questions? We have answers.

Data Formatting Guide - Microsoft Great Plains (GP)

Article author
Jonathon Plowman-Samson
  • Updated

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:

microsoft great plains export file in excel
 

Part 1: Creating the Opening Balances File

  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).
    exported file in excel with items to delete in red. Items to not delete highlighted in green.

  2. Insert two columns at the start of the file, and name them Account Number and Account Description.
    columns added to excel called 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.
    excel example with above formulas applied

  5. Filter the dataset by column H for anything that contains “Begin.
    excel filter screen

  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”.
    example file in excel
 

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


Part 2: Formatting the General Ledger

  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.
    copy and paste icon in excel
  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
    excel headings example

  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:
      filter screen with dates deselected
    • Remove non-posted date text:
      delete row button in excel

  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:
    copy and paste icon in excel

  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.


Share this:

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.