CNA L&P - Reports: How to Export Vendor Audit into Excel and Sort on Vendor Totals.

PROBLEM: I need a simple report of what I paid my vendors within a date range sorted by the Vendor Total.

SOLUTION: In ConnectNow 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 values) and open in Microsoft Excel.
 
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.
Have more questions? Submit a request

Comments

0 comments

Please sign in to leave a comment.