How can I view recent closed opportunities for a list of companies I have in an Excel spreadsheet?

Question

I have a .csv list of companies that I have been monitoring in Salesforce and would like to view the most recent opportunity. Is there a way to generate a report that compares a customized company list to closed opportunities?

Answer 1

How many companies are in the list? If it is just a handful, you could:

• set up filters in an "Accounts with Opportunities" report
• filter by Account name (and closed opportunities)
• summarize by Account name
• order by (possibly) last modified date DESC
• ignore anything after the first line

This would be the least painful method I imagine, but doesn't scale very well if it is a large number of companies or there have been a lot of closed opportunities that would bloat your report.

Answer 2

reporting on accounts and their most recently closed opportunity

1. Create a roll-up field on account from Opportunities and take the MAX of the CloseDate.
2. Create a formula field on opportunity as follows: IF(Account.Most_Recent_Close_Date__c == CloseDate,"TRUE","FALSE")
3. Create a summary report on Opportunities, filter by that formula = TRUE, and group by account name. The standard opportunities report type has the account fields so you should be able to pull in everything you need.

filtering the list of accounts in the report

You could certainly filter by account name as Michael suggested but that isn't really scalable and you will hit the filter criteria length limit pretty quickly. I would recommend creating a checkbox field on the account as a flag to determine whether it should be in the report.

If you have the account ID or an external ID in your csv you could use it to quickly set the checkbox to TRUE on all those accounts with the dataloader or just use a listview and go through and mass-edit the checkbox on the appropriate accounts.