Skip to main content
Solved

GL import scenario debit/credit amounts


coleenmcnally22
Jr Varsity II
Forum|alt.badge.img

I have a csv file that I am using for a GL transaction import scenario. The debit and credit amounts are in one column, debits positive number, credits negative numbers.  How can I get the import scenario to populate the debit and credit fields properly?

thanks, coleen

Best answer by mikedavidson07

  1. In excel Sort the column by the amount.
  2. Insert another column next to this column and label it “Original”.
  3. Go to your sorted column with the data, select all negative numbers and copy them to your new column (shown as original column below).  Make sure they are removed from your original column.
  4. Create a new column and label it “Debit”
  5. Create a formula in your Debit column as show =G2*1 (this will reverse the number).
  6. Confirm the column H now has amounts reversed.

  7. Select all of column H, then right-click your mouse and select copy.

  8. In the same culumn H in cell H2, right-click your mouse and select paste special and select values as forumla.  this will get rid of the formula.  You can also now delect column G “Original”.

    This approach fixes the data in excel and usually just import in the Journal Transaction entry but would also work in an import scenario.  

View original
Did this topic help you find an answer to your question?

2 replies

mikedavidson07
Jr Varsity II
Forum|alt.badge.img+1
  • Jr Varsity II
  • 45 replies
  • Answer
  • January 28, 2021
  1. In excel Sort the column by the amount.
  2. Insert another column next to this column and label it “Original”.
  3. Go to your sorted column with the data, select all negative numbers and copy them to your new column (shown as original column below).  Make sure they are removed from your original column.
  4. Create a new column and label it “Debit”
  5. Create a formula in your Debit column as show =G2*1 (this will reverse the number).
  6. Confirm the column H now has amounts reversed.

  7. Select all of column H, then right-click your mouse and select copy.

  8. In the same culumn H in cell H2, right-click your mouse and select paste special and select values as forumla.  this will get rid of the formula.  You can also now delect column G “Original”.

    This approach fixes the data in excel and usually just import in the Journal Transaction entry but would also work in an import scenario.  


  • Freshman I
  • 1 reply
  • July 2, 2024

I just found below solution for this, in case anyone else later read this post. 
Used below formula to assign Net value to Receipt or Disbursement based on the value of Net field:
Disbursement=IIf( CDec([Net]) < 0, -CDec([Net] ) , '0' )
Receipt=IIf( CDec([Net]) > 0, CDec([Net]) , '0' )

 


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings