SOLVED

Help me add one more filter in my let formula

Copper Contributor

Hello,

I need some help with this let formula. The way it works now is that it combines multiple tables in one single table without blank rows.
I'm not able to add one more filter to it. I need to display entries in the final table based on one reference name. I'll explain below and attach the xls file to simplify things.
 
Each row in the sheets: tables A B C represents a different task. In the first column of each table A B C I assign the name of the person that is in charge of a particular task. 
Next to each table A B and C (in column N) I have a list with the names of each person. There will be a maximum of 5 names in that list (5 rows). Let's say these names:

Maria One

Alex Two

John Three

Mark Four

Larry Five

 
At the moment, the let formula will collect all entries & persons from tables A B and C and combine them in one final table Z. I created 5 new sheets and renamed them with the specific names.
Can you help me and add one more criteria in the LET formula, for example for sheet: Maria One, to gather all tasks from sheets: tables A B C and from person Maria One, and display them in sheet: Maria One
Many thanks!!
1 Reply
best response confirmed by alecsi (Copper Contributor)
Solution

@alecsi 

 

You can use this:

=LET(
    header, Table1[#Headers],
    stack, VSTACK(Table1,Table2,Table3),
    cleaned, FILTER(stack, TAKE(stack, , 1) <> ""),
    cleanedVstack, VSTACK(header, cleaned),
    FILTER(cleanedVstack, TAKE(cleanedVstack, , 1)="Maria One")
)

 

Change "Maria One" with each name on the corresponding sheet.

 

1 best response

Accepted Solutions
best response confirmed by alecsi (Copper Contributor)
Solution

@alecsi 

 

You can use this:

=LET(
    header, Table1[#Headers],
    stack, VSTACK(Table1,Table2,Table3),
    cleaned, FILTER(stack, TAKE(stack, , 1) <> ""),
    cleanedVstack, VSTACK(header, cleaned),
    FILTER(cleanedVstack, TAKE(cleanedVstack, , 1)="Maria One")
)

 

Change "Maria One" with each name on the corresponding sheet.

 

View solution in original post