Forum Discussion
List of type of materials
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.
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.
- fhceqOct 05, 2022Copper ContributorThank you so much, PeterBartholomew1!
Unfortunately, it is not up to me the decision to buy the 365 licences, I wish I could.- PeterBartholomew1Oct 05, 2022Silver Contributor
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!