Learn about the process of transforming for import into MindBridge a general ledger file exported from MYOB AccountRight.
This guide assumes that you have exported an Excel file from MYOB AccountRight in the following format, as detailed in the MYOB AccountRight: Export the general ledger guide.
Transform the data
- In order to ingest a file into MindBridge, the data needs to be in an ungrouped format. This means that the columns have data present on every row. As you can see from the screenshot above, the entries from the MYOB AccountRight Excel export are grouped by Account, with all of the individual postings sitting below a single account reference.
- There are rows containing the beginning balance present which cannot be utilized by MindBridge. These will need to be removed.
- Ideally the first row of the file should be the header rows. Any extra rows above the header row should be removed.
- Start by deleting all rows above the main column headers and delete any blank columns to the left of your data. There is also a hidden blank row below the column headers. Ensure this is deleted also. Once complete row 1 should contain the column headers, and row 2 should contain the first Account Code and Description.
- Insert two new columns for the Account Number and Account Description. Add these as blank columns to the left of your data set and give them titles. This can be accomplished by right clicking on the "A" on the top left corner of the spreadsheet just under the ribbon and selecting "insert". Repeat this twice to insert 2 new columns.
- Add a filter to all of your columns. To do this select all the data by pressing the button to the left of the letter "A" and above the number "1" at the top left corner of the spreadsheet just under the ribbon. Once all the selected have been selected, navigate to the Data Ribbon and click on the Filter button.
- We now need to remove all the Beginning balance rows as these values represent the sum total of the account groups. In our final ungrouped file, these rows will not be necessary. In Column C filter any row containing the word Beginning. Select all of the rows, and right-click to delete. Once deleted remove this filter.
- We now need to transfer the Account Numbers and Descriptions into our newly created Columns A & B. Filter Column C to remove any blank rows. This should leave only the Account Numbers listed in Column C, and Account Descriptions in Column D.
- In cell A2, input the formula =C2 as the cell value. This will copy over the cell content of C2 into A2. Input =D2 inside of cell B2, this will similarly copy over the cell content of D2 into cell B2. Fill down the formulas down columns "A" and "B" by clicking and holding onto the bottom right handles of cell "A2" and "B2" respectively then dragging downwards until the last row containing data within the spreadsheet. The end result should be an Account Number and Description in every visible cell. The end result should be an Account Number and Description in every visible cell.
- Remove the filter in column "C" by going to the Data ribbon and clicking on the "Clear" button.
- We now need to fill in the blank cells in Columns A and B with the relevant Account Numbers and Descriptions.
- Select all of the data in Column "A" by clicking on the letter "A" below the ribbon. Then on the Home tab of the ribbon click on Find & Select > Go to Special. From that menu select Blanks and click OK. You should now have only the blank cells highlighted.
- Do not click on any single cell but instead click on the formula bar at the top of the screen. In the formula bar enter =A2, once entered click CTRL+Enter.
- If done correctly all of the blank cells should now be filled with the value above.
- Repeat steps 9 and 10 with the data in Column B. Once complete you should have a value in every row for both columns.
- Next select all the data in Columns A and B. Right click and click Copy. Right click again and select Paste Special > Values. This will remove all the formulas and leave only the results.
- Add a filter to Column C to exclude blank values.
- Highlight all the visible rows and right-click to delete everything (excluding the header). Once done remove the filter on column C.
- Finally, delete Column C as this is no longer needed. You should now be left with a valid Account Number and Description in every row.
Previous step: MYOB AccountRight: Export the general ledger
Anything else on your mind?