Issue/Symptom/Question

Once I get the Cash Reconciliation report into Excel, how can I add the vendor name to it?

Environment

PENTA for Windows

Finance and Accounting

Golden

Excel

Resolution/Fix/Answer

1)  Run a query on the vendor table, keep a list of vendor names and Ids in Excel, or export the Workbench report to Excel (Workbench sprint/version dependent)

     select * from vendor, in Golden

2)  Copy the vendor Id and vendor name into a new Excel Tab

3)  Use a vlookup formula in Excel on the tab to add the vendor name

    =vlookup(vendor id cell, the step 2 range, return the second column,find exact match)   

example below that means =vlookup(c2,sheet1!A:B,2,false)

4)  Once the Vendor name exists, if you want to create a pivot table to combine same day payments to a vendor, use a concat formula

    =concat(text(date cell,"date format"), " - ", vendor name cell)    might need to use concatenate instead of concat dependent on Excel version

5)  Create Pivot Table to combine the like entries into one amount

A new tab will be created