Forum Discussion

fhceq's avatar
fhceq
Copper Contributor
Sep 29, 2022

List of type of materials

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.

excel
Formulas and Functions

5 Replies

    • fhceq's avatar
      fhceq
      Copper Contributor
      Sep 30, 2022
      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.
      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Sep 30, 2022

        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.

Resources