Convert tally exported cr. as (-) negative in excel



In general tally data(say Trial Balance) exported as Dr. or Cr. in excel. However for reporting purpose often there is requirement to represent Dr. amount as positive and Cr. amount as negative like (-). Is there any formula or technique available which can convert Dr. or Cr. as positive or negative?


Total likes : 1 times

 
Reply   
 
CA in Practice

Firstly detach the "Dr." and "Cr." from amount and take them into separate Column.

Then you can use "IF" function to convert the amounts in Negative or Positive.

 

 
Reply   
 

Prateek, thanks for your reply. However the problem is that though Dr. or Cr. visible in excel however while selecting the respective cell it only shows the numeric amount. So that the steps you have mentioned cann't apply here. For your understanding I am attaching herewith the excel file. Pls go thru the same.



Attached File : 82690 20151223131821 tally dr cr.xlsx downloaded 1203 times
 
Reply   
 
CA in Practice

The process would be a bit lengthy but you can do it as follows - 

1. Write any number (eg. 0) in each a column against each item. [Column F in attached File]. Number in each cell of column should be same.

2. Copy the Column in which numbers with Dr of Cr are written. [Column B in file]

3. Use "Paste Special" and Paste "formats" in the column in which "0" is written. [Column F]

4. Copy and Paste the Column B again in 2 more columns. 1 for Dr. another for Cr. [Column H and Column I in file]

5. Now apply "Filter" on column F. First select Dr Entries only. Select the Cr Column (i.e. column I in file) > Select visible cells only using "Go To" Command. Now only Dr Entries are selected. Delete them.

6. Similarly Now change the filter to select Cr entries only. Select the Dr Column (i.e. column H in file) > Select visible cells only using "Go To" Command. Now only Cr entries are selected. Delete them.

7. Remove filter. Now you have Dr. Entries only in Dr Column and Cr, entries only in Cr column.

8. Put formula in next column for "Dr. Column - Cr Column". You will have Cr. entries in negative.



Attached File : 290809 20151223190109 82690 20151223131821 tally dr cr.xlsx downloaded 1022 times

Total likes : 1 times

 
Reply   
 

Thanks for your effort. In case of compilation of bulk data it would be quite tidious and time consuming. If there any formula can be used it would be more convenient as  well effective. I am looking for any formula. Please take a note that I need to represent various sheets removing Dr. /Cr. and place the amount in as it is transaction format like Opening Balance, Transaction(Dr.)/Cr, Closing Balance only the positive & negative number. The method you have mentioned requires more manual work and time consuming. Only any suitable formula can perform well. However thanks for your effort.

 
Reply   
 
CA in Practice

In case you haven’t tried the process i would suggest you to try it for once. It is looking too lengthy only in written instructions. Once you understand the process it won’t take more than 5 minutes, irrespective bulkiness of your data.

There can’t be a single formula for this and you can only be able to do it with a series of commands.

 

Secondly, if you got the figures with negative and positive in a column you can simply copy and paste them anywhere.

 

Thanks & Regards


Total likes : 1 times

 
Reply   
 

Ya finally I have managed a function. Using such funtion Dr. or Cr. amount can be shown as positive or (-) negative. I would like to share here the  step by step procedure which may be useful for the forum members. 

Step1: Write the function: In excel file contains of Dr. or Cr. figure, please Right Click on any sheet-----> select View Code---> Right Click below the area of VBA project(blank area in left side)--->Click on Insert--->Select Module. Now write the function as follows:

Function CreditDebit(myCell As Range) As Double
Dim strVal As String
Dim myMult As Integer

strVal = myCell.Text

If Right(strVal, 2) = "Cr" Then
    myMult = -1
Else
    myMult = 1
End If

CreditDebit = myCell.Value * myMult

End Function
 

Close the window

Now the function CreditDebit is ready which will convert Dr or Cr amount as positive or negative

Step2: Now if you write the function as =CreditDebit in any cell refering the cell contain amount with Dr or Cr it will convert the amount as positive or negative.

For example in attached file let you type the function in cell G7 =CreditDebit(B7) from where data to be converted. Here we need to copy the entire data in G7:J33. So select the range from G7:J33-----> Press F2 and CTRL+ENTER, it will copy the function in entire range.

The above function will covert Dr. or Cr.  as positive or negative.

Now you can hide column B to E to show the converted data. Or may copy the value of converted data and paste in your desired reporting range.

 



Attached File : 82690 20151226191509 tallycredit debit.xls downloaded 820 times

Total likes : 1 times

 
Reply   
 
General Manager - Finance & Accounts

Thank you for posting such a valuable information

 
Reply   
 
Accounts Manager

You may try this. Go to format - Numbers - make the entire Cr cell in Red color they try your OB+Dr - Cr+ end Balance.It may work out

 
Reply   
 
Student

You can copy the data from MS Excel to MS word to convert Dr/Cr into +/- by replacing the same & then copy that data back to your excel file..
No hassles of using vba. :)


Total likes : 2 times

 
Reply   
 

LEAVE A REPLY

Your are not logged in . Please login to post replies

Click here to Login / Register  

 


×

  CCI MENU