Forum Discussion

Dennis Depoorter's avatar
Dennis Depoorter
Brass Contributor
Feb 20, 2020

Select the x accounts who are visited the longest time ago with extra filters

Hi all,   I want to make a report in excel that gives me a list of the (by example) 20 accounts who are visited the longest time ago. That works fine with the following formula:   =INDEX('10 Acc...
excel
Formulas and Functions
Patrick2788's avatar
Patrick2788
Silver Contributor
Feb 21, 2020

Dennis Depoorter 

I enjoyed this one:

 

=INDEX(FILTER('10 Accounts with tasks'!B:E,('10 Accounts with tasks'!A:A="Jay")+('10 Accounts with tasks'!A:A="Vee")*('10 Accounts with tasks'!H:H="Pharmacy")*('10 Accounts with tasks'!M:M="Active")),SEQUENCE(20,1,1,1),{2,1,4})

    • JKPieterse's avatar
      JKPieterse
      Silver Contributor
      Feb 25, 2020
      See, you could have moved on with your life after inserting that simple pivot table 😛
      • Dennis Depoorter's avatar
        Dennis Depoorter
        Brass Contributor
        Feb 25, 2020

        JKPieterseBut it wouldn't be the report I want 🙂

         

        This is just a small part of a very big report! I should combine more then 30 pivots on one sheet to show the same info. Therefore I choose to use formulas instead of pivot's

         

        Also to update the data,... I don't need to refresh the pivot tables, only click 1 button of an add-in with salesforce to retrieve all the new data.

         

        Those 2 reasons make me choose that formulas are better (for this report) then pivots. I use pivots, but only to show data in a quick easy way, without formatting the way management it want to see.

  • Dennis Depoorter's avatar
    Dennis Depoorter
    Brass Contributor
    Feb 24, 2020

    Patrick2788 

    Hi Patrick, happy to hear you had fun with it.

    THANKS for the help so far!

     

    I tested the formula on the full excel file (see attachment), and there are still some doubles in. I uploaded a new file with the doubles in orange and red. This is also a file with a lot more data (anonymized because of privacy reasons).

     

    To purpose of this file is to provide it to our sales reps so they see what pharmacists they haven't visited in a long time, an need to visit ASAP!

     

    Thanks,

    Dennis

Resources