enter image description here

I want to be able to fill E2:J3 based on a formula using the available qty of mark “1A” in column B, the required qties for each is different (5 & 8), and the sequence of filling based on the occurrences of the product names in column A.

I am able to fill the range when the Marks and Titles either do not contain the product name (is for all 6 products) or all 6 are mentioned in the titles column (since sequence is known) or the qty reqd is consistent among all 6 products.

It gets tricky when the required qty of the marks changes and/or not all the products are named in the column title.

My thought process at this moment:

• =SUM(LEN(SUBSTITUTE(UNIQUE(FILTER(Shippable_Comps[Title],ISNUMBER(SEARCH(TEXTBEFORE(A1,” (“,1),Shippable_Comps[Title])))),”001″,”001_”))-LEN(UNIQUE(FILTER(Shippable_Comps[Title],ISNUMBER(SEARCH(TEXTBEFORE(A1,” (“,1),Shippable_Comps[Title]))))))
  This is to count the number of products. len(substitute(x))-len(x1) that I may be able to use to sequence marks that are not shared among all products

• COUNTIF(MarkColumn) to check if Mark is duplicated since there are majority non duplicate mark numbers

• SUMPRODUCT((MarkColumn=Mark)xQuantity Reqd)
  Challenge I am facing here is I want the first array to count the number of instances the precursor of the product is found in the cells and multiply it by the quantity reqd i.e.{2,4}x{5,8}. Whereas currently it is only giving TRUE/FALSE 1/0 {1,1}x{5,8}