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

T

TDaoud

New Member
Joined
Nov 13, 2013
Messages
15
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:
Sort by date Sort by votes
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
ABCDELMNO
1000 CASH - CHECKING - #
1005 CASH - MMA - #

<tbody>
[TD="align: center"]4[/TD]
[TD="bgcolor: #A5A5A5"]GL Account[/TD]
[TD="bgcolor: #A5A5A5"]Account Name[/TD]
[TD="bgcolor: #A5A5A5, align: right"]1/1/2013[/TD]
[TD="bgcolor: #A5A5A5, align: right"][/TD]
[TD="bgcolor: #A5A5A5"]Balance 3/31/2013[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #A5A5A5"]Assets[/TD]
[TD="bgcolor: #A5A5A5"]Liabilities[/TD]
[TD="bgcolor: #A5A5A5"]P&L[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"] -[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 500.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 500.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"] -[/TD]
[TD="align: right"][/TD]
[TD="align: right"] (500.00)[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 500.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
TB​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C4[/TH]
[TD="align: left"]="1/1/"&YEAR(TODAY())[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E4[/TH]
[TD="align: left"]="Balance "&MONTH(1)+2&"/"&DAY("31")&"/"&YEAR(C4)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E6[/TH]
[TD="align: left"]=SUMPRODUCT(--(GL!$B$10:$B36>=DATE(YEAR(TODAY()),1,1)),--(GL!$B$10:$B36<=DATE(YEAR(TODAY()),3,31)),GL!$H$10:$H36)+C6[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E7[/TH]
[TD="align: left"]=SUMPRODUCT(--(GL!$B$10:$B36>=DATE(YEAR(TODAY()),1,1)),--(GL!$B$10:$B36<=DATE(YEAR(TODAY()),3,31)),GL!$I$10:$I36)+C7[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]M6[/TH]
[TD="align: left"]=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)))))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]M7[/TH]
[TD="align: left"]=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)))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]





Excel 2010
BCDEFGHI
Transfer to CHK XXX from MMA XXXX

<tbody>
[TD="align: center"]4[/TD]
[TD="bgcolor: #A5A5A5, align: center"]First Quarter[/TD]
[TD="bgcolor: #A5A5A5, align: center"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #16365C"] Transaction Type[/TD]
[TD="bgcolor: #16365C"] Activity[/TD]
[TD="bgcolor: #16365C"] LP/GP[/TD]
[TD="bgcolor: #A6A6A6, align: center"] CASH[/TD]
[TD="bgcolor: #A6A6A6, align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #A5A5A5"]Date[/TD]
[TD="bgcolor: #A5A5A5"]Description[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #16365C, align: right"][/TD]
[TD="bgcolor: #16365C, align: right"][/TD]
[TD="bgcolor: #16365C, align: right"][/TD]
[TD="bgcolor: #A6A6A6"] Checking[/TD]
[TD="bgcolor: #A6A6A6"] MMA[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #A5A5A5, align: right"][/TD]
[TD="bgcolor: #A5A5A5, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #16365C, align: right"][/TD]
[TD="bgcolor: #16365C, align: right"][/TD]
[TD="bgcolor: #16365C, align: right"][/TD]
[TD="bgcolor: #A6A6A6"] xxxx[/TD]
[TD="bgcolor: #A6A6A6"] xxxx[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #A5A5A5, align: right"][/TD]
[TD="bgcolor: #A5A5A5, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #16365C, align: right"][/TD]
[TD="bgcolor: #16365C, align: right"][/TD]
[TD="bgcolor: #16365C, align: right"][/TD]
[TD="bgcolor: #A5A5A5, align: right"]1000[/TD]
[TD="bgcolor: #A5A5A5, align: right"]1005[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #C5D9F1"]1/1/2013[/TD]
[TD="bgcolor: #C5D9F1"]Balance[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C5D9F1, align: right"][/TD]
[TD="bgcolor: #C5D9F1, align: right"][/TD]
[TD="bgcolor: #C5D9F1, align: right"][/TD]
[TD="bgcolor: #C5D9F1, align: right"] -[/TD]
[TD="bgcolor: #C5D9F1, align: right"] -[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1/1/13[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #16365C"] Cash[/TD]
[TD="bgcolor: #16365C"] Cash transfer[/TD]
[TD="bgcolor: #16365C, align: right"][/TD]
[TD="bgcolor: #A6A6A6, align: right"] 500.00[/TD]
[TD="bgcolor: #A6A6A6, align: right"] (500.00)[/TD]

</tbody>
GL​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B4[/TH]
[TD="align: left"]=C41[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B8[/TH]
[TD="align: left"]=TB!C4[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H8[/TH]
[TD="align: left"]=VLOOKUP(H7,TB!$A$6:$K$41,3,FALSE)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]I8[/TH]
[TD="align: left"]=VLOOKUP(I7,TB!$A$6:$K$41,3,FALSE)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]I10[/TH]
[TD="align: left"]=-H10[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
Click to expand...

That is an option, but you'll limit your responses that way since many people can't/won't access file sharing sites.
 
Upvote 0
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:
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Need to add two sets of minutes with custom formatting and round
Replies
1
Views
620
bh24524
B
L
  • Question Question
Need help figuring out a formula to calculate prices based on 4 conditions
Replies
1
Views
1K
Legacy 483325
L
R
  • Question Question
Help converting Google Sheets Challenging Formula
Replies
8
Views
931
ragomes
R
Z
  • Solved
Need help on a formula Maybe SumIfs
Replies
3
Views
506
zone709
Z
S
  • Solved
Seperate Data In One Cell Across Multiple Cells
Replies
6
Views
2K
Alex Blakenburg
A

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top