List of type of materials

Occasional Contributor

I have been trying to list the types of materials in a list that relates the material (Table 1) with its type according to the final product (Table 2), so that I would obtain a list of type of materials without repetition and that is dynamic according to the product it's chosen (Table 3).
I made an example manually showing how it would look if I choose the product "hydrated ethanol".

Please, does anyone have any idea how to approach this situation?


Restrictions: it needs to work on Office 2016 without VBA nor Power Query.

5 Replies

@fhceq FILTER() may work for you.

=FILTER(ProductMaterial[Material Name],ProductMaterial[Product name]=K5)

 

Harun24HR_0-1664508533782.png

 

Thank you, @Harun24HR, for your prompt response.
Unfortunately, there may have been some confusion.
I'm trying to list the material type according to the selected product. It is necessary to cross Table 1 with Table 2. Table 2 lists the materials of each product (sugarcane, hydrated ethanol and anhydrous ethanol). In table 1 the materials listed in Table 2 are grouped according to the type of material they are.
So, in Table 3, I would need to list the the type of materials ("water from hydro source", "reuse water"...) that are in the material types column of Table 1 without repeating, and this is dynamic according to the product (sugarcane, hydrated ethanol and anhydrous ethanol) that is chosen.
FILTER function does not work on Office 2016.

@fhceq 

Excuse my saying so but I believe you have boxed yourself into a corner.  The obvious solutions are PowerQuery

let
    Source = Excel.CurrentWorkbook(){[Name="ProductMaterial"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product name", type text}, {"Material Name", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Product name] = SelectedProduct)),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"Material Name"}, MaterialType, {"Material name"}, "MaterialType", JoinKind.LeftOuter),
    #"Expanded MaterialType" = Table.ExpandTableColumn(#"Merged Queries", "MaterialType", {"Material Type"}, {"Material Type"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded MaterialType",{"Product name", "Material Name"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Material Type", Order.Ascending}})
in
    #"Sorted Rows"

or a 365 Dynamic Array solution

= LET(
    materialName, FILTER(ProductMaterial[Material Name], ProductMaterial[Product name]=product),
    materialType, XLOOKUP(materialName, MaterialType[Material name], MaterialType[Material Type],"Not found"),
    SORT( UNIQUE(materialType))
  )

It may well be possible to put a solution together using only traditional spreadsheet techniques, but I suspect the cost of doing so would exceed the cost of obtaining several 365 licences.

Thank you so much, @Peter Bartholomew!
Unfortunately, it is not up to me the decision to buy the 365 licences, I wish I could.

@fhceq 

Bad luck, it will be interesting to see whether you can deliver a solution for a lower cost than a license.

I think the primary need to find a workaround for the FILTER function.  Basically, you can use a sequence number based upon row numbers.  Then test and return each number only if the corresponding criterion is met.  The SMALL function will produce a consecutive list of indices that meet the criterion.  Use the list as indices within the INDEX function to return the filtered list.  Then use a further INDEX/MATCH to look up the material type.  I am just thankful that I do not need to do these things anymore!