Forum Discussion

Legion's avatar
Legion
Trusted User
3 years ago
Solved

can I run a GL transaction list in excel that includes account code field on each line?

TFPG posted a question in 2015   "Can I run a report similar to other accounting packages that includes the account number field for each transaction? This way I can sort the data in excel by accou...
  • Mike_James's avatar
    Mike_James
    3 years ago

    Hi Legion , if you send the GL detail report to Excel, add a column between cols A and B, and enter the following formula to B12 (next to the first account number): =IF(ISNUMBER(FIND("-",C12)),C12,B11). The Find formula will only return a number if "-" is found in cells in column C.

     

    Copy that formula down all the rows of the report. You could extend it to only show values in data rows, but that can also be done by filtering out blanks in columns Src or Date.

     

    PS: (Edited) Before manipulating the spreadsheet further, you may need to copy the new column and paste it as values over itself, otherwise the formulae will give incorrect results. 

Mike_James's avatar
Mike_James
Ultimate Partner
3 years ago

Hi Legion , if you send the GL detail report to Excel, add a column between cols A and B, and enter the following formula to B12 (next to the first account number): =IF(ISNUMBER(FIND("-",C12)),C12,B11). The Find formula will only return a number if "-" is found in cells in column C.

 

Copy that formula down all the rows of the report. You could extend it to only show values in data rows, but that can also be done by filtering out blanks in columns Src or Date.

 

PS: (Edited) Before manipulating the spreadsheet further, you may need to copy the new column and paste it as values over itself, otherwise the formulae will give incorrect results. 

  • Legion's avatar
    Legion
    Trusted User
    3 years ago

    Thanks Mike. That will do the job nicely.

Related content