Forum Discussion
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 Accounts with tasks'!C:C;MATCH(SMALL('10 Accounts with tasks'!N:N;A2);'10 Accounts with tasks'!N:N;0))
But I want to add extra conditions to it, and just don't find how. Normally within a INDEX MATCH you can add multiple conditions, but I don't get it right.
So what if I want the 20 accounts that are visited the longest time ago with Status "Active" from my sales people (Assigned to full name) "Vee" or "Jay" and that are a Pharmacy (account record type)?
Thanks!!!
11 Replies
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})
- See, you could have moved on with your life after inserting that simple pivot table 😛
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
That could be
=IFERROR( INDEX('10 Accounts with tasks'!C:C, AGGREGATE(15,6,1/ ('10 Accounts with tasks'!H:H="Pharmacy")/ ('10 Accounts with tasks'!A:A="Jay")* ROW('10 Accounts with tasks'!N:N), ROW()-ROW($B$1) )),"")Thank you very mutch!!! But I forgot 1 thing...
Reason for that rapport will make it all clear...
I want our sales rep's to have a rapport of the 20 accounts that they have visited the longest time ago. Because for every visit they make a task with a "day visited", we have to select first for all accounts the last day they have been visited, and then select out of that the 20 oldest... is this possible within 1 formula?
Dennis Depoorter Does this pivot table work as expected?
This does what it has to do (but I forgot 1 thing), but I forgot to tell you I'm allergic to pivots *smiley*🙂
Dennis Depoorter That is an allergy you should be addressing! 🙂
Seriously, pivot tables have a lot of advantages over putting things in formulas. Efficiency and less risk of error are the two most significant ones.
