Here is how I run reports for positive pay. WARNING: You need MS Excel skills to accomplish this task as I describe below:
1) You will create two memorized reports:
a) Select the QB default Transaction Detail by Account Report:
i) Only Columns needed: "Num"; "Amount"; "Date"; "Name" (in this order from left to right)
ii) Dates to run report I have set for "today" because I do it for the same day, but you set up the date range that you do the work.
iii) Sort by Name (A-Z)
iiii) Memorize, name, and organize this report in a way that makes sense to you.
b) Select the QB default Vendor contact list report:
i) Only Columns needed: "Vendor"; "Print on Check as"; (in this order from left to right)
ii) Filter for ONE bank account.
iii) Sort by Vendor (A-Z)
iiii) Memorize, name, and organize this report in a way that makes sense to you.
2) Run both newly memorized reports each time you need to upload a file to your PositivePay system. Export them both into excel via .csv file format. Open both files in excel.
3) Now, in excel:
a) Combine both files into one file (workbook). So you go from two workbooks with one spreadsheet each - to one workbook with two spreadsheets.
b) Delete row two (which is the name of your bank account listed in QB)
c) In column E of the transaction report/spreadsheet, run a VLOOKUP formula. The lookup value is going to be the "name" column in the transaction report. The table reference is both columns of the vendor list, column will be the number 2 and then "FALSE" for exact match. Hit enter and copy the formula down to the end of the transaction report.
d) If you have a "0" result, then there is nothing entered in the "print on check as" of the vendor report. Find those and fix them in both QB and the excel file you are currently working on.
e) Copy and paste-value column E to replace the "name" in Column D. Delete column E.
f) Add your account number, fix the date format, fix amounts, and add your positivepay action to your excel file.
g) upload to PositivePay site.
Good luck and I hope this helps!
