FORMULA REQUIRED: Multi cell search displaying multi cell list

%3CLINGO-SUB%20id%3D%22lingo-sub-1042849%22%20slang%3D%22en-US%22%3EFORMULA%20REQUIRED%3A%20Multi%20cell%20search%20displaying%20multi%20cell%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1042849%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20help.%20I%20have%20been%20searching%20for%20a%20formula%20that%20I%20can%20use%20but%20I'm%20not%20having%20any%20luck.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20creating%20a%20new%20spreadsheet%20for%20a%20new%20work%20group%20I%20am%20in%20that%20requires%20a%20list%20from%20data%20manually%20entered.%20I%20am%20only%20interested%20in%20ACTIVE%20rows%20under%20the%20Status%20Field%20(Green%20Column)%20that%20relate%20to%20the%20specific%20employee%20(Blue%20Column).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEssentially%20I%20want%20a%20formula%20to%20create%20a%20list%3A%3C%2FP%3E%3CP%3EIf%20the%20%3CSTRONG%3EBlue%20Column%3C%2FSTRONG%3E%20contains%20%3CEM%3ESam%3C%2FEM%3E%20AND%20the%20%3CSTRONG%3EGreen%20Column%3C%2FSTRONG%3E%20contains%20%3CEM%3EActive%3C%2FEM%3E%20THEN%20create%20a%20list%20of%20the%20related%20Yellow%20Column%2C%20Red%20Column%20and%20Purple%20Column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20very%20basic%20image%20of%20the%20spreadsheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20excellent!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1042849%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-1042956%22%20slang%3D%22en-US%22%3ERe%3A%20FORMULA%20REQUIRED%3A%20Multi%20cell%20search%20displaying%20multi%20cell%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1042956%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F475575%22%20target%3D%22_blank%22%3E%40TheDon4500%3C%2FA%3E%26nbsp%3B-%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20personally%20would%20pass%20the%20%5BEmployee%5D%20as%20a%20parameter%20to%20Power%20Query%20and%20return%20a%20filtered%20table%20as%20described%20in%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexceleratorbi.com.au%2Fpass-excel-parameter-power-query%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceleratorbi.com.au%2Fpass-excel-parameter-power-query%2F%3C%2FA%3E.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20826px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F159661i5F9D4D6D6EF6C8CD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222.gif%22%20title%3D%222.gif%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20only%20downside%20is%20that%20a%20Refresh%20is%20required%20so%20depending%20on%20your%20data%20that%20may%20not%20be%20a%20feasible%20solution.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Table1%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20%23%22Changed%20Type%22%20%3D%20Table.TransformColumnTypes(Source%2C%7B%7B%22Employee%22%2C%20type%20text%7D%2C%20%7B%22Status%22%2C%20type%20text%7D%2C%20%7B%22Supplier%22%2C%20type%20text%7D%2C%20%7B%22Driver%22%2C%20type%20text%7D%2C%20%7B%22Notes%22%2C%20type%20text%7D%7D)%2C%0A%20%20%20%20%23%22Filtered%20Rows%22%20%3D%20Table.SelectRows(%23%22Changed%20Type%22%2C%20each%20(%5BEmployee%5D%20%3D%20Parameter))%2C%0A%20%20%20%20%23%22Filtered%20Rows1%22%20%3D%20Table.SelectRows(%23%22Filtered%20Rows%22%2C%20each%20(%5BStatus%5D%20%3D%20%22Active%22))%2C%0A%20%20%20%20%23%22Removed%20Columns%22%20%3D%20Table.RemoveColumns(%23%22Filtered%20Rows1%22%2C%7B%22Employee%22%2C%20%22Status%22%7D)%0Ain%0A%20%20%20%20%23%22Removed%20Columns%22%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F159662i3F332B8015308DE2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222.1.png%22%20title%3D%222.1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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