Skip to main content

Notifications

Small and medium business | Business Central, N...
Suggested answer

Tracking down accounts that have never been used. Chart of accounts cleanup

(0) ShareShare
ReportReport
Posted on by 10

Help please.

I am trying to establish which accounts in our general ledger have never been used and can be deleted. I have created a jet report which has found a number of accounts that have never had a balance since day one, however, I can't establish if there have been transactions at any stage, ( ins and outs ). I don't have access to development tools. Is there a way other that possibly exporting hundreds and thousand of transactions and analysing.

Many thanks,  Ivor

  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 74,036 Super User 2024 Season 2 on at
    RE: Tracking down accounts that have never been used. Chart of accounts cleanup

    Hi, I tend to handle this outside the system, like using Excel.

    For example, export all General Ledger Entries first. (Using Configuration Package)

    pastedimage1645401561781v1.png

    Add a PivotTable for G/L Account No. (It's best to consider Bal. Account No. as well.)

    pastedimage1645401751224v2.png

    Then you will get the G/L account details that have been used.

    pastedimage1645401805267v3.png

    Hope this will help.

    Thanks.

    ZHU

  • Suggested answer
    JAngle Profile Picture
    JAngle 33,159 on at
    RE: Tracking down accounts that have never been used. Chart of accounts cleanup

    You can delete a GL if there are no postings in the current accounting period (income statement). Instead of a nl(“sum”) use a nl(“count”) with a date filter for the current accounting period with g/l entry table. I’d run that alongside a nl(“rows”) for the g/l account table. The nl(“count”) could be part of a if statement. If the count is above 1 then false and if below true. The true values are the delete contenders. Use cell b1 to add a keyword of hide+? and put another if statement in to assess the result of the count formula. If true display the word “show”, if false display “hide”. Your list of rows will only be the valid candidates. The count for balance sheet codes will have to be for a much bigger date range

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Tracking down accounts that have never been used. Chart of accounts cleanup

    Ivor,

    That is a good one. What if you summarized all GL Account that had Transactions Posted. Then do a VLOOKUP to the GL Accounts listing and for those that have entries cross them out and the remainder are the ones no entries have posted.

    Just a thought.

    Hope it helps.

    Thanks,

    Steve

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Announcing Category Subscriptions!

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,359 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,370 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans