List all results from a table that match a value

[Dmobbs]

Hi,

I am trying to make a list based on the values in a table.

In Row A I have a list of locations, in Column 1 I have a list of departments. Within the table I have various cells with "Pass" written in.

I am looking for a way to list all the departments of each location that have "pass".

e.g. if Cell C2, D5, E3 and E7 all have Pass I would like the list to include "A2 C1", "A5 D1", "A3 E1" & "A7 E1"

Thank you in advance.

 

[Special-K99]

You talk about row A and column 1.
Row A ? You mean column A surely ?
Column 1 ? You mean row 1 don't you ?
Columns go up and down like the column in a newspaper or the column that supports a building.
Rows go across.

Post the data so we know exactly what you mean.

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[Dmobbs]

Sorry, Monday morning

As a shortened example. If I have the table below.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]North[/TD]
[TD]South[/TD]
[TD]East[/TD]
[TD]West[/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD][/TD]
[TD]Pass[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Technical[/TD]
[TD]Pass[/TD]
[TD][/TD]
[TD][/TD]
[TD]Pass[/TD]
[/TR]
[TR]
[TD]Services[/TD]
[TD][/TD]
[TD][/TD]
[TD]Pass[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Commercial[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Finance[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Pass[/TD]
[/TR]
</tbody>[/TABLE]

I am looking to have a way to automatically create the following list

North Technical
South Sales
East Services
West Technical
West Finance

Then create a new list if the cells which have returned a Pass result change.

 

[sandy666]

is that what you want?

if so try PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Type[/td][td=bgcolor:#5B9BD5]North[/td][td=bgcolor:#5B9BD5]South[/td][td=bgcolor:#5B9BD5]East[/td][td=bgcolor:#5B9BD5]West[/td][td][/td][td=bgcolor:#70AD47]Region[/td][td=bgcolor:#70AD47]Type[/td][td=bgcolor:#70AD47]Value[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Sales[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Pass[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#E2EFDA]South[/td][td=bgcolor:#E2EFDA]Sales[/td][td=bgcolor:#E2EFDA]Pass[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Technical[/td][td]Pass[/td][td][/td][td][/td][td]Pass[/td][td][/td][td]North[/td][td]Technical[/td][td]Pass[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Services[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Pass[/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#E2EFDA]West[/td][td=bgcolor:#E2EFDA]Technical[/td][td=bgcolor:#E2EFDA]Pass[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Commercial[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]East[/td][td]Services[/td][td]Pass[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Finance[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Pass[/td][td][/td][td=bgcolor:#E2EFDA]West[/td][td=bgcolor:#E2EFDA]Finance[/td][td=bgcolor:#E2EFDA]Pass[/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table10"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Type"}, "Attribute", "Value"),
    #"Reordered Columns" = Table.ReorderColumns(#"Unpivoted Other Columns",{"Attribute", "Type", "Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Attribute", "Region"}})
in
    #"Renamed Columns"[/SIZE]

 

[Dmobbs]

That is what I want, I am very new to PowerQuery. I managed to use the getdata function to create the original table for loads of other tables but now struggling to follow the recommendation above.

 

[sandy666]

Hm,
add header to source table (here: Type)
Select whole source table (here blue) then use from the ribbon Data - From Table
it will open new window PowerQuery Editor
select column Type then UnPivot Other Columns (from the ribbon or from right click menu)
Re-order columns as you want and rename headers as you want
then from the ribbon use Close & Load

hope it's clear

 

[Dmobbs]

Thank you that's great

 

[sandy666]

You are welcome

if you want to learn more look at:

• PQ tutorial
• Power Query from MS
• Power Query from MS 2

have a nice day