UNIQUE & VSTACK formula needs 1 change

[thewiseguy]

Hi all. Currently I am using the following formula:

=LET(u,UNIQUE(VSTACK(SORT(Input!$Q$4:$Q$503),SORT(Input!$V$4:$V$503),SORT(Input!$X$4:$X$503),SORT(Input!$Z$4:$Z$503),SORT(Input!$AD$4:$AD$503),SORT(Input!$AF$4:$AF$503),SORT(Recycle_Calc!A23:A26),SORT(Input!$AK$4:$AK$503),SORT('Project Level Items (BOM)'!B4:B50))),FILTER(u,u<>""))

I would like to change the last statement: SORT('Project Level Items (BOM)'!B4:B50 to return the value in this range but ONLY if the value in column D = "Labor"

Any help is greatly appreciated!

 

[RoryA]

Change:

SORT('Project Level Items (BOM)'!B4:B50)

to:

SORT(FILTER('Project Level Items (BOM)'!B4:B50,'Project Level Items (BOM)'!D4:D50="Labor"))

 

[thewiseguy]

Change:

SORT('Project Level Items (BOM)'!B4:B50)

to:

SORT(FILTER('Project Level Items (BOM)'!B4:B50,'Project Level Items (BOM)'!D4:D50="Labor"))

Thank you Rory!

 

[thewiseguy]

Change:

SORT('Project Level Items (BOM)'!B4:B50)

to:

SORT(FILTER('Project Level Items (BOM)'!B4:B50,'Project Level Items (BOM)'!D4:D50="Labor"))

Hi Rory - I am having an issue now. It seems the entire function will only work if there is data in "Project Level Items (BOM) D4:D50."

I want the function to SORT all of the other areas, even if there is no data in "Project Level Items (BOM) D4:D50."

Any help is greatly appreciated!

 

[RoryA]

You can handle no matching filter data like this:

SORT(FILTER('Project Level Items (BOM)'!B4:B50,'Project Level Items (BOM)'!D4:D50="Labor",""))

 

[thewiseguy]

its telling me there is a missing opening or closing perenthesis


=LET(u,UNIQUE(VSTACK(SORT(Input!$Q$4:$Q$503),SORT(Input!$V$4:$V$503),SORT(Input!$X$4:$X$503),SORT(Input!$Z$4:$Z$503),SORT(Input!$AD$4:$AD$503),SORT(Input!$AF$4:$AF$503),SORT(Input!$AK$4:$AK$503),SORT(FILTER('Project Level Items (BOM)'!B4:B50,'Project Level Items (BOM)'!D4:D50="Labor",""))