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

 

[TDaoud]

No sure what question you are posing, I don't know what VBA is, if your asking if my computer or system can handle it, it can.

 

[shyy]

Some firms restrict VBA access, he is asking if you are allowed to use macros.

Look at post #17, is that what you want?

 

[TDaoud]

yes my firm is allowed to use macro's I tried to set one up before but I know nothing on the subject...


Maybe this makes it easier for you guys...this is the unfixed version of what I have...I just copied and pasted what was on post #17...

Post #17 shows how to get rid of the "CR" and replace with the negative value. but doesn't fix the account number and name issue


It is consistant with the spacing for all 4,000 accounts it's three rows in between each account number...not sure if that helps...

Excel 2010

	A	B	C	D
1	1000
2	Cash		1234	1235
3
4	1001
5	Petty Cash		1456CR	1567CR
6
7

<TBODY>
</TBODY>

Sheet1
​

 

[TDaoud]

Should look like this....


Excel 2010

	A	B	C	D
1
2	Cash	1000	1234	1235
3	Petty Cash	1001	(1456)	(1567)
4
5
6
7

<TBODY>
</TBODY>

 

[DutchDiggy]

In your first post it was the other way around, first the name with the values and 2 rows down the account number.

We'll stick with the latest order... let's have a go at VBA...

 

[TDaoud]

Post #23 is correct...sorry!!! wow this thread is great! Also on post #17, it seems like the forumla is alittle bit confusing....can we adjust it so it does the V-lookup formula to in different colums not rows? Also just tried Post #17 and it still shows up with cr in my excel

 

[DutchDiggy]

I don't have the answer for the extracting of the CR, but this macro will re-arrange the data as pleased. (try it on a copy of your workbook, because it can't be undone if applied)

Sub verplaatsen()
Dim LastEntry As Integer
LastEntry = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row


Dim myrange As Range
Set myrange = ThisWorkbook.Sheets("sheet1").Range("A1:A" & LastEntry)


For Each c In myrange
If IsNumeric(c.Value) = True Then
c.Copy Destination:=c.Offset(1, 1)
c.ClearContents
End If
Next




myrange.Select
For E = LastEntry To 1 Step -1
    If Range("A" & E).Value = "" Then
    Range("A" & E).Select
        Selection.EntireRow.Delete shift:=xlUp
    End If
Next
End Sub

Just make a macro and substitute the macro-line with the code and run the macro

 

[shyy]

Just curious... why is your format of data so inconsistent? Is this being manually done or are you using some software and importing it into Excel? If you are manually doing this, I would re-think on how to make this process more efficient.

Since your account is on the same row as the amount, just perform a VLOOKUP using the name of the account and pulling it from a COA table (or use an offset function to pick the cell to the top). COA doesn't change, all account number should be unique with an account name. So if you have a COA already established, just use that to pull the code. For the negative amount to look like (XXXXX) just change the format to accounting.

I switched in this example...
Excel 2010

	A	B	C	D
1	1000
2	Cash		1234	1235
3	1001
4	Petty Cash		1456CR	1567CR
5
6	COA TABLE
7	Cash	1000
8	Petty Cash	1001
9
10
11
12
13
14	Cash	1000	1,234	1,235
15	Petty Cash	1001	(1,456)	(1,567)

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B14 =VLOOKUP(A14,$A$7:$B$8,2,FALSE) C14 =IFERROR(IF(SEARCH("CR",VLOOKUP($A14,$A$2:$D$4,COLUMN(C2),FALSE))>0,-1*VALUE(SUBSTITUTE(VLOOKUP($A14,$A$2:$D$4,COLUMN(C2),FALSE),"CR","")),""),VLOOKUP($A14,$A$2:$D$4,COLUMN(C2),FALSE)) D14 =IFERROR(IF(SEARCH("CR",VLOOKUP($A14,$A$2:$D$4,COLUMN(D2),FALSE))>0,-1*VALUE(SUBSTITUTE(VLOOKUP($A14,$A$2:$D$4,COLUMN(D2),FALSE),"CR","")),""),VLOOKUP($A14,$A$2:$D$4,COLUMN(D2),FALSE)) B15 =VLOOKUP(A15,$A$7:$B$8,2,FALSE) C15 =IFERROR(IF(SEARCH("CR",VLOOKUP($A15,$A$2:$D$4,COLUMN(C4),FALSE))>0,-1*VALUE(SUBSTITUTE(VLOOKUP($A15,$A$2:$D$4,COLUMN(C4),FALSE),"CR","")),""),VLOOKUP($A15,$A$2:$D$4,COLUMN(C4),FALSE)) D15 =IFERROR(IF(SEARCH("CR",VLOOKUP($A15,$A$2:$D$4,COLUMN(D4),FALSE))>0,-1*VALUE(SUBSTITUTE(VLOOKUP($A15,$A$2:$D$4,COLUMN(D4),FALSE),"CR","")),""),VLOOKUP($A15,$A$2:$D$4,COLUMN(D4),FALSE)) <tbody> </tbody>

<tbody>
</tbody>

 

[TDaoud]

WOW!!! worked like a charm!!!!! now i just have to figure out the "CR" issue. thanks so much!!!

 

[TDaoud]

The format of the data is so inconsistant because the client uses a software that produces their trial balance on a pdf... when they export to excel it is extremely inconsistant. The macro above ran perfectly and works...I just need to fix the "CR"