How to Export Vendor Audit into Excel and Sort on Vendor Totals
In Ledger & Payables, click Reports→Vendors→Vendor Audit, select the Date Range and Vendors you would like to report and click Preview Report.
- Export the report using CSV (comma separated value) and open in Microsoft Excel. **Note** Please use the preceding link to export your file as you must save the file to disk before opening the file or it will open in a text editor.
- Insert two new blank columns (A and B) to the left of the vendor Name.
- In column A, insert the following formula and copy down to all rows in your spreadsheet
- =IF(ISNUMBER(C9)=TRUE,"",IF(C9="","",IF(C9=" ","",VLOOKUP("Vendor Total:",F9:H9999,3,FALSE))))
- Copy column A, then right-click into column B and choose Paste Special. Choose Values and click OK.
- Delete Column A (which contains the formula) and the values which were in column B are now in column A.
- Sort the report on Column A (either Ascending or Descending) and delete the extra rows.
Please sign in to leave a comment.