Formula from input to list all outputs which data comes from a table

[samzie83]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Input[/TD]
[TD]Apple
[/TD]
[TD]Ref No
[/TD]
[TD]Item 1
[/TD]
[TD]Item 2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]Apple
[/TD]
[TD]Banana
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Output
[/TD]
[TD]Banana
[/TD]
[TD]2
[/TD]
[TD]Berries
[/TD]
[TD]Coconut
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Watermelon
[/TD]
[TD]3
[/TD]
[TD]Watermelon
[/TD]
[TD]Apple
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Berries
[/TD]
[TD]4
[/TD]
[TD]Banana
[/TD]
[TD]Coconut
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Lettuce
[/TD]
[TD]5
[/TD]
[TD]Berries
[/TD]
[TD]Apple
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]6
[/TD]
[TD]Lettuce
[/TD]
[TD]Cucumber
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]7
[/TD]
[TD]Apple
[/TD]
[TD]Lettuce
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[samzie83]

Hi, I am trying to figure out from the list on the right, how I can set an Input "Fruit" and then the Output will populate in order down the table listing all Outputs that have the Input on the same line.

 

[sandy666]

you can try PowerQuery (aka Get&Transform)

load source table into PQ
create input table and load into PQ

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Ref No[/td][td=bgcolor:#5B9BD5]Item 1[/td][td=bgcolor:#5B9BD5]Item 2[/td][td][/td][td=bgcolor:#5B9BD5]Input[/td][td][/td][td=bgcolor:#70AD47]Output[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]Apple[/td][td=bgcolor:#DDEBF7]Banana[/td][td][/td][td=bgcolor:#DDEBF7]Banana[/td][td][/td][td=bgcolor:#E2EFDA]Apple[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

2​

[/td][td]Berries[/td][td]Coconut[/td][td][/td][td][/td][td][/td][td]Coconut[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

3​

[/td][td=bgcolor:#DDEBF7]Watermelon[/td][td=bgcolor:#DDEBF7]Apple[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

4​

[/td][td]Banana[/td][td]Coconut[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

5​

[/td][td=bgcolor:#DDEBF7]Berries[/td][td=bgcolor:#DDEBF7]Apple[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

6​

[/td][td]Lettuce[/td][td]Cucumber[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

7​

[/td][td=bgcolor:#DDEBF7]Apple[/td][td=bgcolor:#DDEBF7]Lettuce[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

then use :

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table1),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Ref No", "Item 1", "Item 2"}, {"Ref No", "Item 1", "Item 2"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Custom", "Output", each if [Input] = [Item 1] then [Item 2] else if [Input] = [Item 2] then [Item 1] else null),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column",{"Output"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Output] <> null))
in
    #"Filtered Rows"[/SIZE]

if you change input "fruit" use Ctrl+Alt+F5 to refresh query table