QuickBooks Tech Trends

Tally QuickBooks Purchase Orders by User in Excel

Written by David Ringstrom

Earlier this year I described two techniques for tallying QuickBooks customer sales by City or State in Excel. That article prompted a question by a reader named Casey, who needed to tally how many users within QuickBooks had sent purchase orders. The goal was to track purchase order accuracy, but Casey couldn’t create a report that showed which user had sent purchase orders. You, too, have probably had that sense that your data is trapped under glass because the field you need isn’t available on a report. I’ve written in the past how to use VLOOKUP in Excel to merge two QuickBooks reports, but I had to take a different approach to solve Casey’s problem.

Mining the Audit Trail Report

My first step in researching Casey’s issue was to create a custom transaction report in QuickBooks that would display all purchase orders (the Purchases choices on the Reports menu only allows you to create reports that display open purchase orders). I used the sample product-based business in the desktop versions of QuickBooks, in case you’d like to recreate what you see me describe here. To access this company:

  1. Choose File.
  2. Choose Close Company.
  3. Click Open a Sample Company from the No Company Open dialog box.
  4. Choose Sample Product-Based Business.

QuickBooks Purchase Orders

Figure 1: I used the sample product-based business as the basis for this article.

Now let’s try to find a report that will meet Casey’s needs:

  1. Choose Reports.
  2. Choose Custom Reports.
  3. Choose Transaction Detail.
  4. Click the Filters tab when the Modify Report dialog box appears.
  5. In QuickBooks 2015 and later, type Trans in the Search Filters field, or scroll down until you find Transaction Type in the earlier versions of QuickBooks.
  6. Choose Purchase Order from the Transaction Type list.
  7. Click the Display tab of the Modify Report dialog box.
  8. In QuickBooks 2015 and later, type User in the Search Columns field, or scroll down until you find a field with the word User in its name. As you’ll see, even in QuickBooks 2015 the only user-related field available is called “User Edit?”—which returns a Y or N to indicate if a transaction has been edited.

Custom transaction detail report

Figure 2: Even custom reports often don’t include key fields that we need on a report.

This means we’ve hit a dead end with regard to mining the Custom Transaction Report. As is often the case with QuickBooks reports, we’re going to have to be a bit more creative:

  1. Choose Reports.
  2. Choose Accountant and Taxes.
  3. Choose Audit Trail.
  4. Set the From and To fields to match the desired date range, or choose All from the field where Today appears.
  5. Click the Customize Report button.
  6. Click the Filters tab when the Modify Report dialog box appears.
  7. In QuickBooks 2015 and later, type Trans in the Search Filters field, or scroll down until you find Transaction Type in the earlier versions of QuickBooks.
  8. Choose Purchase Order from the Transaction Type list.
  9. Click OK to display the report.

Audit Trail Report

Figure 3: The Audit Trail report includes the user name, but in some cases lists a purchase order more than once.

As shown in Figure 3, purchase orders may appear on the report more than once. As is often the case with QuickBooks reporting, we’ve solved one problem—finding a report that includes the user name—but we now have a new problem: any purchase orders that have been modified appear on the report two or more times. If you’ve read any of my articles in the past, you already know that I’ll bring Excel to bear on this problem:

  1. Click the Excel button on the Audit Trail report.
  2. Click Create New Worksheet.
  3. Depending upon your transaction volume, this could be a long report. To save time exporting to Excel choose Create a Comma Separated Values (.csv) File.
  4. Click Export.
  5. Choose a name for the file when the Create Disk File dialog box appears.
  6. Click Save to create the .CSV file.

Create a Comma Separated Values  File

Figure 4: Large reports export in seconds to a CSV file, instead of sometimes in minutes to Excel.

If you’re unsure how to open a CSV file in Excel, my first article for the Sleeter Report covers the steps in detail. Once you have the report open in Excel, the next step is to filter out the transactions that you don’t want by using Excel’s Filter feature. However, we need to carry out an extra step before we enable the Filter feature, or otherwise you’ll hit a dead end:

  1. In Excel, click the button in the top left-hand corner of the worksheet frame to select the entire worksheet.
  2. Choose the Data tab.
  3. Click the Filter button.
  4. Click the arrow in cell D1, which contains the word State.
  5. Clear the checkbox for Latest.
  6. Click OK.
  7. Click cell A2 of the spreadsheet.
  8. Press Ctrl-Shift-End to select all visible cells on the report.
  9. Press Ctrl-Minus to display the Delete prompt.
  10. Double-click Delete Row to close the dialog box without clicking OK.
  11. Click the arrow in cell D1.
  12. Choose Clear Filter from State.

filtered cells

Figure 5: You can quickly remove unnecessary data from a report by filtering and a couple of keyboard shortcuts.

At this point you’re left with a list of purchase orders that can be identified by user and by vendor. The easiest way to summarize this information is with a pivot table:

  1. Choose the Insert tab.
  2. Click Pivot Table.
  3. Click OK when the Create Pivot Table dialog box appears.
  4. Click Name from the Pivot Table Field list.
  5. Drag Account into the Values section of the Pivot Table field list.
  6. Drag Last Modified By to the Columns section.

Pivot Table

Figure 6: A pivot table in Excel shows purchase orders issued by vendor and QuickBooks user.

At this point you’ll have a report that gives you the number of purchase orders issued by user and by vendor. The sample Product-based business that I used in QuickBooks only has a single user, but if purchase orders had been issued by more than one user then I’d see that information. There is a caveat: as presently written, this report assumes that one QuickBooks user didn’t modify a purchase order entered by another user. The data that we kept on the audit trail reflects the last user to touch each transaction. If we want to determine which user originated the transaction, we’ll need to do some additional work in Excel, which I’ll discuss in an upcoming article.

 


Save pagePDF pageEmail pagePrint page

About the author

David Ringstrom

David Ringstrom, CPA, is the president of Accounting Advisors, Inc., an Atlanta-based spreadsheet and database consulting firm he started in 1991. Throughout his career, David has spoken at conferences on Excel, and he currently leads dozens of webinars each year on Excel, QuickBooks, and other software. He has served as the technical editor for over 25 books, including several editions in Wiley’s QuickBooks for Dummies and Quicken for Dummies series. In addition to writing for QuickBooks and Beyond, David is the Tech Editor at Large for AccountingWEB and Going Concern. He also offers live webcasts and self-study courses through CPE Link. His freelance articles on spreadsheets have been published as far afield as Pakistan. During training sessions, you’ll often hear David state, “Either you work Excel, or it works you!”

2 Comments

  • I have a problem with the vendor application. Our business generates spa products that are sold at festivals. Sometimes we have on line sales or sales
    from the office also. But we really do not have a vendor list with per se purchase orders. We have to reduce inventory after the sale, then increase inventory as needed. How would you recommend setting this up ? We purchased Account Pro and are unhappy with it but we do have the QuickBooks premier. Thanks

Leave a Comment