Solved: Excel 2010 IF - nightmare

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Bpierson

Thread Starter
Joined
Jan 15, 2011
Messages
3
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
 

Ent

Josiah
Retired Trusted Advisor
Joined
Apr 11, 2009
Messages
5,467
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?
 

Bpierson

Thread Starter
Joined
Jan 15, 2011
Messages
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

Joined
Jul 1, 2005
Messages
8,546
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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top