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...

material types.xlsx16 KB

excel

Formulas and Functions

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

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
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!

Forum Discussion

List of type of materials

Resources