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 | |||
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
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
@VaibhavJ
(Believe!! Live your dreams!)
(33516 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!)
(33516 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.
CAclubindia
