Forum Discussion
search for a solution that can replace the FILTR function
Hello Everyone,
I am writing to you today, so that I can share with you my Excel problem, which I have been meeting with for a few days. Indeed, I want the Excel table to search for references based on the Time and date. To know who it can be 3 references for a single time slot.I used a formula that worked long before, but today doesn’t work anymore. However when I enter the values manually, the formula works. If you have solutions to propose to me, or other formulas to use I am open to any proposal.
Normally, there is the FILTER formula, but does not work only on Microsoft365.
Basically, I want Excel to take the references from the first sheet (capture 1) and put it on the second sheet (capture 2) based on the date and time and to return all references having the same time and date without duplicate.
The formule that I use is: =IFERROR(INDEX('DATA '!$D$4:$D$330;MATCH(0;COUNTIF(B3:B$3;'DATA '!$D$4:$D$331)+IF('DATA '!$C$4:$C$331<>PLNV!$A$4;1;0)+IF('DATA '!$B$4:$B$331<>PLNV!$B$2;1;0);0));"")
DATA & PLNV are the name of my sheets.
I hope that I was quite clear in my explanation.
Thank you for your help and involvement.
15 Replies
I have taken another look at the problem and would note that no values have actually been filtered from the dataset. Broadly speaking a simple top to bottom sort on datetime will give you the sequence you require. Conditional formatting could be used so that the first instance only is shown for any given timeslot and borders are shown between timeslots.
Of course, I would use SORTBY whereas you are stuck with either a manual process or cumbersome ranking and lookup functions.
