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

 

[DutchDiggy]

Okay... for now the grande finaly!

change the macro to this.. it would do all...

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


Dim therange As Range
Set therange = ThisWorkbook.Sheets("sheet1").Range("C1:D" & LastEntry)




For Each D In therange
    If InStr(1, D, "CR") > 0 Then
    D.Value = Application.WorksheetFunction.Substitute(D, "CR", "")
    D.Value = D.Value * -1
End If
Next
End Sub

 

[TDaoud]

Wow!! that is amazing!!!! works like charm! this is so great i didn't think it was possible!!!!! thanks so much

 

[DutchDiggy]

You're welcome! Remember! This macro only runs when you've set your table like you did. So if you get another sheet and the rows are set up another way, it would scramble.

If you ever have the chance to post a sample of the input you're getting before the setup you've manually made we could figure out a way to descramble it without the manual labour you've already done to set this up.

but for now! It worked!

thanks for the reply it worked! Have a good day!

 

[Smitty]

That maybe something on your system then, since the SkyDrive folder is completely open/unrestricted.