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 | |||
Thanks .......
Dear Vaibhav,
This Macros has helped me a lot. Thank you so much for it.
CA Priyanka Reddy
Great / Awesome
Hey @ Vaibhav Thanks for the great file. its saved my lot of time and other's also.
Regrad,
Amit Pathak
Great, I am glad it helped you.
The macro is quite smart. Thanks. Wonder why on earth does Tally export in this un-usable format ?
Tally believes in keeping things simple.
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
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.
CCI
Pro
CAclubindia
