How to filter debits and credits in excel

[sureshmandadpu]

Hi Every one,

When I export data from an accounting package (Tally in my case) to excel it is adding "Dr" and "Cr" to numbers. When I tried fileter "Dr" and "Cr" in exce, I coult not filter it. And in formula bar I can not see "Dr" and "Cr" in numbers. Please help me to segrigae "Dr" and "Cr" separately. I have attached file which was exported from Tally.


Regards
M. Suresh

 

[webmax]

hi
select the data and put filter type dr in search field you have get only the dr entries

 

[sureshmandadpu]

Hi..

It is not working with filters.

 

[xlstime]

which excel version u are using ?

 

[NARAYANK991]

Hi Suresh ,

You need to check the settings in Tally , so that the data when imported into Excel , is retrieved correctly ; or there may be settings which you need to set when you import the data into Excel.

Can you post the actual data file which is created by Tally , rather than the resulting Excel file after the data has been imported into it ?

The Dr and Cr are displayed , but they are not part of the cells themselves , and I doubt that it will be possible to do anything in this file. Nor are they derived through cell formatting , since the values themselves are all positive.

Narayan

 

[kchiba]

it is the way your cells are format, you will note that the cells, have a special format "0.00 DR" and "0.00 Cr".
I am not sure how you can change that, you may have to change the way that Tally formats the cell.

 

[sgmpatnaik]

@Suresh

Hello

As per my knowledge there is no option to set the setting for excel it is directly export from Tally to Excel but there we have one feature that is we can enable the color option when exporting.

as per your giving excel file please select the Cr Value Cells and Color them with your choice then select the Range B2:C14 and select the sort option then you find the below option

1. Sort By

2. Sort On

3. Order

Now set the 1st point to Name of the Vendor

2nd Point Cet Color

3rd Point Select your desired color and press ok now it is sorted

Hope it will solve your problem other please inform

Thanks

Patnaik

 

[SirJB7]

Hi, sureshmandadpu!

If you don't happen to make Tally obey you while following the previous suggestions, you'd do the job with this procedure:

Sub SillyToClever()
    ' constants
    Const ksWS = "Sheet1"
    Const ksList = "SillyTallyList"
    Const ksDebit = """Dr"""
    Const ksCredit = """Cr"""
    ' declarations
    Dim rng As Range
    Dim I As Long, J As Integer, K As Integer, A As String
    ' start
    Set rng = Worksheets(ksWS).Range(ksList)
    With rng
        Range(.Offset(0, 1), .Offset(.Rows.Count, 2)).ClearContents
    End With
    ' process
    With rng
        For I = 1 To .Rows.Count
            A = .Cells(I, 1).NumberFormat
            J = InStr(A, ksDebit)
            K = InStr(A, ksCredit)
            .Cells(I, 1).Offset(0, Sgn(J) * 1 + Sgn(K) * 2).Value = .Cells(I, 1).Value
        Next I
    End With
    ' end
    Set rng = Nothing
    Beep
End Sub

Check this file for proper named range definitions:
https://dl.dropboxusercontent.com/u/60558749/How to filter debits and credits in excel - Debits and Credits from tally (for sureshmandadpu at chandoo.org).xlsm

Regards!

 

[lohithsriram]

Hi Suresh,

Here is a way.

1) Place your cursor on cell D3 and go into the Define Name (Formulas -> Define Name/Name Manager)
2) Ener Name as "CRDR" (of your choice if you want) and under "Refers to" enter this formula =Get.cell(7,!C3)
3) On Cell D3, enter =CRDR, this should display the custom format applied on the cell like (0.00 "DR").
4) Enter the same formula =CRDR on all the Cells on D column
5) Apply autofilter on the D column, using custom filters, filter as desired.

 

[sunilspai]

Thank you sir !!!