Tech Support Guy banner
  • Please post in our Community Feedback thread for help with the new forum software! If you are having trouble logging in, please Contact Us for assistance.
Status
Not open for further replies.
1 - 4 of 4 Posts

·
Registered
Joined
·
3 Posts
Discussion Starter · #1 ·
I consistently do an interface process between two software programs. Export in to Excel, cleanup and then import in to the Accounting Software. The export file is always large (well, 1,200 rows or so) and have multiple columns (date, source, reference, etc.) The amounts dump in to one column (col 3). Another column states if the amount is a Debit or Credit (col 4). This is an accounting interface cleanup/import file so I need to verify each account number balances. What statement can I write to state if a cell in col 3 says credit, make the cell - number in col 4 negative. Then I would sort by account number and do a subtotal at each change in account number (col 5) to verify the data before I do the import. I don't know how to write the statement. TIA
 

·
Retired Trusted Advisor
Joined
·
5,465 Posts
I realize that this has potentially sensitive information which you cannot share on an online forum like this. However it would be easier for anyone to see quite what you're working with (the fact that you're importing and exporting to other software makes the precise layout all important) if you could post such a sample. Would it be possible to sanitize a bit of a copy of the excel file so that we can see what it looks like?
 

·
Registered
Joined
·
3 Posts
Discussion Starter · #3 ·
Here are a few lines from one of the data exports. You can see the change in the account number (col 1). I want the amount column to change to a negative number if the Type column says credit. That way I can sort by account number and do a subtotal at each change in account number with positive and negative numbers totaling. As it is now, it will just ADD the numbers as it doesn't know some of them are negative. Did I explain that appropriately?
 

Attachments

·
Registered
Joined
·
8,546 Posts
Bpierson said:
I consistently do an interface process between two software programs. Export in to Excel, cleanup and then import in to the Accounting Software. The export file is always large (well, 1,200 rows or so) and have multiple columns (date, source, reference, etc.) The amounts dump in to one column (col 3). Another column states if the amount is a Debit or Credit (col 4). This is an accounting interface cleanup/import file so I need to verify each account number balances. What statement can I write to state if a cell in col 3 says credit, make the cell - number in col 4 negative. Then I would sort by account number and do a subtotal at each change in account number (col 5) to verify the data before I do the import. I don't know how to write the statement. TIA
Depends what you mean by "statement".

You could write a formula, e.g.:

=IF(C2="Credit",-D2,D2)

in column G.

Or use a macro (AKA "VBA", AKA "script"), such as:

Sub test()
For Each Cell In Range("C2:C17")
If Cell = "Credit" Then
Cell.Offset(, 4) = -Cell.Offset(, 1)
Else
Cell.Offset(, 4) = Cell.Offset(, 1)
End If
Next Cell
End Sub
 
1 - 4 of 4 Posts
Status
Not open for further replies.
Top