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 materi...
excel
Formulas and Functions
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.

    • fhceq's avatar
      fhceq
      Copper Contributor
      Oct 05, 2022
      Thank you so much, PeterBartholomew1!
      Unfortunately, it is not up to me the decision to buy the 365 licences, I wish I could.
      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Oct 05, 2022

        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!

Resources