FORMULA REQUIRED: Multi cell search displaying multi cell list

Copper Contributor

Hi,

 

I need help. I have been searching for a formula that I can use but I'm not having any luck.

 

I am creating a new spreadsheet for a new work group I am in that requires a list from data manually entered. I am only interested in ACTIVE rows under the Status Field (Green Column) that relate to the specific employee (Blue Column).

 

Essentially I want a formula to create a list:

If the Blue Column contains Sam AND the Green Column contains Active THEN create a list of the related Yellow Column, Red Column and Purple Column.

 

I have attached a very basic image of the spreadsheet.

 

Any help would be excellent!!

 

Thanks in advance.

2 Replies

@TheDon4500 -

 

I personally would pass the [Employee] as a parameter to Power Query and return a filtered table as described in https://exceleratorbi.com.au/pass-excel-parameter-power-query/.

2.gif

 

The only downside is that a Refresh is required so depending on your data that may not be a feasible solution.

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Status", type text}, {"Supplier", type text}, {"Driver", type text}, {"Notes", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Employee] = Parameter)),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([Status] = "Active")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Employee", "Status"})
in
    #"Removed Columns"

2.1.png

 

 

 

Hi @TheDon4500 

 

Is a slicer on a table or pivot table any good?

 

You can just copy paste into the new sheet then. 

 

clipboard_image_0.png