1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel 2010 IF - nightmare

Discussion in 'Business Applications' started by Bpierson, Jan 15, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. Bpierson

    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
     
  2. Ent

    Ent Trusted Advisor

    Joined:
    Apr 11, 2009
    Messages:
    5,467
    First Name:
    Josiah
    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?
     
  3. Bpierson

    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?
     

    Attached Files:

  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    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
     
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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/974849

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice