Thanks:  0
Likes:  0

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

1. ## 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...............................................................................

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

3. ## Re: Accountant needs Excel help, very difficult formula set up!!!!

any help at all would be great!

4. ## 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. ## Re: Accountant needs Excel help, very difficult formula set up!!!!

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

6. ## 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.

7. ## 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. ## Re: Accountant needs Excel help, very difficult formula set up!!!!

will there be more accounts? like

Cash
.....
1001 District 1
1002 District 2

9. ## 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.

10. ## Re: Accountant needs Excel help, very difficult formula set up!!!!

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

## User Tag List

#### Posting Permissions

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