SOLVED

Filter a filtered list, returns error when duplicates.

%3CLINGO-SUB%20id%3D%22lingo-sub-2486608%22%20slang%3D%22en-US%22%3EFilter%20a%20filtered%20list%2C%20returns%20error%20when%20duplicates.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2486608%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20idea%20here%20is%20to%20return%20the%20name%20from%20the%20headers%20when%20date%20and%20project%20match.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20done%20a%20two%20step%20filtter.%20First%20I%20filter%20ther%20rows%2C%20and%20project%20names%20when%20date%20match.%3C%2FP%3E%3CP%3EOn%20that%20filtered%20list%2C%20I%20filter%20the%20names%20if%20the%20project%20match.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20works%20perfect%2C%20until%20I%20have%202%20similar%20dates.%20like%20february%20second%20in%20the%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20good%20ideas%20on%20how%20to%20solve%20this%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20Regards%3C%2FP%3E%3CP%3E-%20Geir%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2486608%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2486755%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20a%20filtered%20list%2C%20returns%20error%20when%20duplicates.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2486755%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9195%22%20target%3D%22_blank%22%3E%40Geir%20Hogstad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLET(area%2C%20--(I4%3DFILTER(Tabell1%5B%5BEmployee%201%5D%3A%5BEmployee%203%5D%5D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Tabell1%5BDate%5D%3DI3))%2C%0A%20rws%2C%20ROWS(area)%2C%0A%20IFERROR(%20FILTER(Tabell1%5B%5B%23Headers%5D%2C%5BEmployee%201%5D%3A%5BEmployee%203%5D%5D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20MMULT(SEQUENCE(%2Crws%2C1%2C0)%2Carea))%2C%0A%20%20%20%20%20%20%20%20%20%20%22---%22)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Regular Contributor

Hi,

 

The idea here is to return the name from the headers when date and project match. 

 

I have done a two step filtter. First I filter ther rows, and project names when date match.

On that filtered list, I filter the names if the project match.

 

It works perfect, until I have 2 similar dates. like february second in the example.

 

Any good ideas on how to solve this

 

Best Regards

- Geir

3 Replies
best response confirmed by Geir Hogstad (Regular Contributor)
Solution

@Geir Hogstad 

As variant

=LET(area, --(I4=FILTER(Tabell1[[Employee 1]:[Employee 3]],
                        Tabell1[Date]=I3)),
 rws, ROWS(area),
 IFERROR( FILTER(Tabell1[[#Headers],[Employee 1]:[Employee 3]],
                 MMULT(SEQUENCE(,rws,1,0),area)),
          "---")
)
Thank you Sergei, it worked perfect.

Best Regards
- Geir