Convert dr / cr to values in tally exported file

Page no : 2

muhammed salim (Manufacturing Co. ) (22 Points)
Replied 04 July 2017

Find the attached file with solution.

	Steps

1	select cell from your sheet 'Receivable From Customer (Prog)' D3
2	Press Ctrl+F3 any where (here I use A1)
3	Select New
4	Name as "Cell_format"
	Formula '=GET.CELL(53,'Receivable From Customer (Prog)'!D3)'


5	In your sheet insert the formula and drag down
6	paster the formula '=IF(RIGHT(Cell_format,2)="Cr",-D3,D3)'
7	copy 1 right and down as much

Note :	for stable the value use copy and paste special

Attached File : 804098 20170704022752 182267 1334665 prog outstanding data.xls downloaded: 165 times

kiran mestry (2 Points)
Replied 24 January 2018

Thanks .......

Priyanka Reddy (CFO) (22 Points)
Replied 30 August 2018

Dear Vaibhav,

This Macros has helped me a lot. Thank you so much for it.

CA Priyanka Reddy

SARJERAO KHOT (Manager Accounts) (22 Points)
Replied 04 December 2018

Great / Awesome

AMIT PATHAK (Accountant) (27 Points)
Replied 09 December 2018

Hey @ Vaibhav Thanks for the great file. its saved my lot of time and other's also.

Regrad,

Amit Pathak

1 Like

@VaibhavJ (Believe!! Live your dreams!) (33511 Points)
Replied 19 December 2018

Great, I am glad it helped you.

Prithwis Mukerjee (2 Points)
Replied 11 August 2019

The macro is quite smart. Thanks. Wonder why on earth does Tally export in this un-usable format ?

@VaibhavJ (Believe!! Live your dreams!) (33511 Points)
Replied 21 August 2019

Tally believes in keeping things simple.

Sandeep Bharmoria (2 Points)
Replied 16 May 2021

I have simplest solution, copy desired column from excel, to notepad and paste it back to adjacent column in excel let's say j12 column .

1. Now write following in next column k12. (i am assuming its ledger)

=right(j12, 2) this will print all Dr and Cr in k column.

Now copy and save the values of k column.

2.find and replace all Dr and cr of J column with blank.

3. Now in L12 column, write this formula

=if(k12="Dr", j12, j12*-1)

This will solve your format problem and repeat this on other columns.

We have a product for forensic analysis and fraud investigations, which take care of these automatically. Visit us at www.iacuityfintech.com or contact gaurav.batheja @ iacuityfintech. Com.

Hope this helps,

Sandeep Thakur

Ritesh Bijlani (23 Points)
Replied 12 September 2023

Replace Dr/Cr with +/- in Excel of Data Exported from Tally by 2 ways.

1) In Excel, select the amounts and then go to Ctrl + F then type _Cr (Spacebar Cr) then go to Lookin menu and select values then find all option then select all resulted lines and colour it from home menu and close Ctrl+F. You will find all Cr as coloured and you can apply formula in blank cell =Concatenate("-", Cell)

2) In Tally, Export data in ASCII Mode then that exported file will open in text format then copy all data from that in paste in Excel sheet then select all data and go to Text to Data Column menu then Delimiter then select comma menu then finish.