Questions? We have answers.

Data Formatting Guide: Consolidating Duplicate Trial Balance Accounts

Article author
Kate H
  • Updated

Summary

MindBridge requires that trial balance files have one unique account per row. Sometimes you will receive trial balances with duplicate account numbers, usually because several subsidiaries were manually combined into one.


Resolution

Here is how to consolidate the duplicate account numbers:

  1. First copy and paste the account column (header and all entries) into its own column in another Excel workbook.
  2. In the new workbook, with the column still selected, go to Data -> Remove Duplicates
  3. Since the "Account" header has been copied, you should leave "My list has headers" checked
  4. Use an INDEX()/MATCH() formula to pull the first account description in the original TB for each account number:
img1.png
=INDEX('sheetname'!$B:$B,MATCH($A2,'sheetname'!$A:$A,0))
 
Where the account number is in A2, sheetname is the name of the worksheet/workbook of the original TB with account number in column A and account description in column B. 
  1. Use a SUMIF() formula to sum up the amount for each unique account:

    img1.png
    =SUMIF('sheetname'!$A:$A,$A2,'sheetname'!$C:$C)
    Where the account number is in A2, sheetname is the name of the worksheet/workbook of the original TB with account number in column A and amount in column C.
  2. Finally, copy and paste values so that your new workbook, which you will be importing into MindBridge, does not have formulas.

     

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.