Go Ad-Free on CAclubindia with Daily Mobile Updates. Subscribe Now!

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

Prodip Das (xx) (427 Points)

22 December 2015

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?

Reply
Follow
Share

29 Replies

CA Prateek Garg (CA in Practice) (1447 Points)
Replied 23 December 2015

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.

Upgrade to CAclubindia Pro. Subscribe Now!

Prodip Das (xx) (427 Points)
Replied 23 December 2015

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: 2379 times

CA Prateek Garg (CA in Practice) (1447 Points)
Replied 23 December 2015

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: 2401 times

2 Like

Prodip Das (xx) (427 Points)
Replied 23 December 2015

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.

CA Prateek Garg (CA in Practice) (1447 Points)
Replied 24 December 2015

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

1 Like

Upgrade to CAclubindia Pro. Subscribe Now!

Prodip Das (xx) (427 Points)
Replied 26 December 2015

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: 1612 times

2 Like

Yasdani (General Manager - Finance & Accounts) (21 Points)
Replied 04 May 2016

Thank you for posting such a valuable information

V P Narasimhan (Accounts Manager) (791 Points)
Replied 20 June 2017

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

Vishwal (Student) (42 Points)
Replied 08 February 2018

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. :)

4 Like