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

[TDaoud]

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!!!

 

[shyy]

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

<tbody>
</tbody>

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))))) <tbody> </tbody>

<tbody>
</tbody>

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)

<tbody>
</tbody>

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 <tbody> </tbody>

<tbody>
</tbody>

 

[TDaoud]

any help at all would be great!

 

[TDaoud]

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

---

 

[shyy]

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

 

[pplstuff]

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.

 

[Smitty]

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.

 

[DutchDiggy]

will there be more accounts? like

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

 

[shyy]

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.

 

[TDaoud]

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