Sep 29 2022 06:39 PM
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.
Sep 29 2022 08:29 PM
@fhceq FILTER() may work for you.
=FILTER(ProductMaterial[Material Name],ProductMaterial[Product name]=K5)
Sep 30 2022 06:06 AM
Sep 30 2022 08:59 AM
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.
Oct 05 2022 01:30 PM
Oct 05 2022 03:45 PM
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!