I am given a list which has 1000 accounts. I am asked to find opportunities between stage0-5 that are present on these 1000 accounts. I should differentiate these 1000 accounts into 2 ways.

1 If Account has opportunity which is in stage 0-5, it should be seperated from the account that has no opportunity or opportunity that is in stage 6 or above. I need to differentiate the 1000 accounts into 2 lists so that we know which account has opportunity and which doesn't.

Your reply would be really of great help. Thanks

  • 3
    How are you trying to find them? Reports? SOQL? We need more information about the tools you have available and what you're tried so far. May 28, 2015 at 5:32
  • I am trying to use vlookup in excell. If you know any better ways let me know.
    – George
    May 28, 2015 at 6:03

3 Answers 3


Set AccIDs=new Set()// fill this set with account ids in your list List=[Select id from Account where Id IN (select AccountId from opportunity where stage='0' || stage='1'....... && AccountId IN AccIDs)];//this will result into parent accounts of opp having stages 0-5.

Now remove the obtained account IDs from the list of Ids you have to get the the ones having no opportunity or opportunity that is in stage 6 or above.

This will help you,just you need to modify or construct your query.throw kudos if you like my answer !


This may work:

  1. Loop over all the Opportunities where Stage is between 0-5 and capture their AccountId.
  2. Use Aggregate SOQl to count the number of Opportunities each Account is having. Something like this:

    [SELECT AccountId AcctId, Count(id) OppCount FROM Opportunity WHERE AccountId in: acctIds GROUP BY AccountId]

  3. Now, you can loop over the result to differentiate account where count = 0 and count != 0.

Please try and lemme know if it works.

  • I was trying to use VLookup in Excell, do you think that would work.?
    – George
    May 28, 2015 at 6:02
  • That may work but i would suggest you to use apex. May 28, 2015 at 15:41

I believe you could use roll up summary fields to the accounts, and than make a report on the accounts filtered on the roll up summary results ?

Rather than receiving 1000 accounts, I'd ask for the selection criteria of these accounts, so that you can include these in your reports too.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .