Hi Raj,

Thanks for using Community Hub.

Can I check how you're running / exporting that report? It looks as if it's picking up 3 Charts of Accounts, one after the other.

Regards,

Andy
Sage UKI

Hi Andy

Thanks for your response. I was using Nominal Codes, COA, Edit, print preview and Export to excel option to download the report.

Raj

Hi Raj,

I checked with sage Technical Support and unfortunately we can't make any changes to how this report outputs, any manipulation would need to be done in Excel after export. 

The "duplicate" lines aren't duplicates as such as they relate to different categories, ie Line 3 is Category 1, Line 50 is Category 2 and so on.

Regards,

Andy
Sage UKI

Thanks Andy

If you export the data to excel, then it is only where the two columns:

• ChartOfAccountsSubCategory.ChartOfAccountsCategoryID
• ChartOfAccountsCategory.ID

(columns D & E) are equal does the nominal range exist in that area.

Add an extra column at the end of the exported table (column K):

1. In K2 type a heading (eg Chk) and the new column will be created (table originally ending in column J)
2. In K3 type the formula¹: =[@[ChartOfAccountsSubCategory.ChartOfAccountsCategoryID]]=[@[ChartOfAccountsCategory.ID]]
3. Filter the new column to "FALSE"
4. Select and delete all the rows of the table which are visible
5. Delete the new column (K)

And you should now have a table which contains each range only once.  You can then sort on column G. 

If you record a macro when you do these steps and save it, when you next export the data, you should be able to re-use the macro and save yourself time.

¹It is actually easier to:

1. in K3 Type the first =
2. Click on D3 (first cell in column ChartOfAccountsSubCategory.ChartOfAccountsCategoryID)
3. Type the next =
4. Click on E3 (the first cell in column ChartOfAccountsCategory.ID)

Excel with automagically copy the formula down the column.