Thanks Thanks:  0
Likes Likes:  0
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 34

Thread: Accountant needs Excel help, very difficult formula set up!!!!

  1. #1
    New Member
    Join Date
    Nov 2013
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb Accountant needs Excel help, very difficult formula set up!!!!

    Hey!

    I am a tax accountant trying really hard to impress my manager...long story short one of our big clients give us a very un organized Excel sheet of there trial balance... and I want to find a quick way to organize it...below is an example of what the excel looks like and what I want to look like... (Bold represent columns and rows of an excel and periods are just fillers)

    A .................B ............C .................D.....
    1.Cash......... .............$1,234 .........$1,235
    2. .........................................................
    3.1000....................................................
    4.Petty Cash.............. $1,456CR .......1,567CR
    5...............................................................
    6.1001.....................................................

    We have over 4,000 accounts ... so we have couple of issues... I need to take the account number "1000" and place it in B next to cash. I also need to take any number in column C or D with a "CR" and turn that number into a negative. So it should look like this....

    A...................... B..................... C ......................D
    1.Cash.............. 1000.............. $1,234 .................$1,235
    2. Petty Cash..... 1001............. -$1,456............... -$1,567
    3...............................................................................
    4...............................................................................

    Make sense? Can you please help!!!
    Last edited by TDaoud; Nov 13th, 2013 at 03:52 PM.

  2. #2
    Board Regular
    Join Date
    Nov 2008
    Posts
    1,484
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Accountant needs Excel help, very difficult formula set up!!!!

    Post a doc, your data is confusing to look at. I have created my own accounting package (complete substitute for quickbook) using macro.

    Here is a sample of what I used to pull information into the TB, you would multiple it by -1 if the number is a positive or put an

    Excel 2010
    A B C D E L M N O
    4 GL Account Account Name 1/1/2013 Balance 3/31/2013 Assets Liabilities P&L
    5
    6 1000 CASH - CHECKING - # - 500.00 500.00
    7 1005 CASH - MMA - # - (500.00) 500.00
    TB

    Worksheet Formulas
    Cell Formula
    C4 ="1/1/"&YEAR(TODAY())
    E4 ="Balance "&MONTH(1)+2&"/"&DAY("31")&"/"&YEAR(C4)
    E6 =SUMPRODUCT(--(GL!$B$10:$B36>=DATE(YEAR(TODAY()),1,1)),--(GL!$B$10:$B36<=DATE(YEAR(TODAY()),3,31)),GL!$H$10:$H36)+C6
    E7 =SUMPRODUCT(--(GL!$B$10:$B36>=DATE(YEAR(TODAY()),1,1)),--(GL!$B$10:$B36<=DATE(YEAR(TODAY()),3,31)),GL!$I$10:$I36)+C7
    M6 =ABS(IF($A$2="First Quarter",E6,IF($A$2="Second Quarter",G6,IF($A$2="Third Quarter",I6,IF($A$2="Fourth Quarter",K6,0)))))
    M7 =ABS(IF($A$2="First Quarter",E7,IF($A$2="Second Quarter",G7,IF($A$2="Third Quarter",I7,IF($A$2="Fourth Quarter",K7,0)))))





    Excel 2010
    B C D E F G H I
    4 First Quarter Transaction Type Activity LP/GP CASH
    5 Date Description Checking MMA
    6 xxxx xxxx
    7 1000 1005
    8 1/1/2013 Balance - -
    9
    10 1/1/13 Transfer to CHK XXX from MMA XXXX Cash Cash transfer 500.00 (500.00)
    GL

    Worksheet Formulas
    Cell Formula
    B4 =C41
    B8 =TB!C4
    H8 =VLOOKUP(H7,TB!$A$6:$K$41,3,FALSE)
    I8 =VLOOKUP(I7,TB!$A$6:$K$41,3,FALSE)
    I10 =-H10
    Last edited by shyy; Nov 13th, 2013 at 04:03 PM.

  3. #3
    New Member
    Join Date
    Nov 2013
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Accountant needs Excel help, very difficult formula set up!!!!

    any help at all would be great!

  4. #4
    New Member
    Join Date
    Nov 2013
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Accountant needs Excel help, very difficult formula set up!!!!

    Don't see an option to post an excel on this thread or even post a picture of a snip-it

  5. #5
    Board Regular
    Join Date
    Nov 2008
    Posts
    1,484
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Accountant needs Excel help, very difficult formula set up!!!!

    Use google doc and share a file or box to upload the file.

  6. #6
    Board Regular
    Join Date
    Mar 2012
    Posts
    951
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Accountant needs Excel help, very difficult formula set up!!!!

    Assuming your data are consistant in the format for the OP. You should consider using the OFFSET() function. Seems pretty straightforward....

    =offset($a$1,(row()-1)*3)
    It'll grab the third row down, for every one row you drag down the formula.

    This same method can be adapted for all your columns.

  7. #7
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Redmond, WA
    Posts
    29,535
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Accountant needs Excel help, very difficult formula set up!!!!

    Welcome to the Board!

    If you follow the HTML Maker link in my sig you'll be able to post screen shots.

    Use google doc and share a file or box to upload the file.
    That is an option, but you'll limit your responses that way since many people can't/won't access file sharing sites.

  8. #8
    Board Regular DutchDiggy's Avatar
    Join Date
    Sep 2013
    Location
    Holland
    Posts
    210
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Accountant needs Excel help, very difficult formula set up!!!!

    will there be more accounts? like

    Cash
    .....
    1001 District 1
    1002 District 2
    And yet he learned so little....

    NOTE (copy/pasted from DeBeuz) Sometimes I forget to replace the Dutch separator ";" -> "," and decimal point "," -> "." in my replies; Sorry.

  9. #9
    Board Regular
    Join Date
    Nov 2008
    Posts
    1,484
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Accountant needs Excel help, very difficult formula set up!!!!

    Basically the formulas you should look into are SUMPRODUCT or SUMIF, Nest IF statements to get what you want. Also ABS returns all the value to a positive (for Debit). For credit put an if statement so if its less than 0, display if not don't.

    I just noticed you have the CR inside the cell with the amount. You will need to use a search function to search for "CR". If you could I would re-do your setup completely. Have the CR amounts go to a column that has CR as the header. This way you can do calculation without having to use the SEARCH function. Will get messy.
    Last edited by shyy; Nov 13th, 2013 at 04:11 PM.

  10. #10
    New Member
    Join Date
    Nov 2013
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Accountant needs Excel help, very difficult formula set up!!!!

    firm has it blocked...for a good reason too i guess

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •