TEXTJOIN criteria that are summed based on uniqueness

[lampshade22]

Hi all

I am trying to achieve a summary of some data using the TEXTJOIN function.
The output depends on which Order No. the product has been allocated to. I would like to combine all the weights of each product in the output, regardless of whether they are Forecast Yield or Actual Yield. If the order allocation has any forecasted yields in it, I want to denote this with an asterisk. I have tried but I can't work out how to sum up the weights by product.

My desired output is highlighted in yellow.

The formula in C3 is:

=TEXTJOIN(CHAR(10),TRUE,IFS($E$8:$E$25<>B3,"",($E$8:$E$25=B3)*($C$8:$C$25<>""),$B$8:$B$25&" x "&ROUND($C$8:$C$25,1)&"Kg *",($E$8:$E$25=B3)*($D$8:$D$25<>""),$B$8:$B$25&" x "&ROUND($D$8:$D$25,1)&"Kg"))

[/CODE]

I hope that makes sense. Any help would be greatly appreciated!

Thanks

 

[hagia_sofia]

The following is based on the assumption that there is always either "Forecast" or "Actual" filled (but never both):

=LET(
p,$B$8:$B$25,
s_1,$C$8:$C$25,
s_2,$D$8:$D$25,
s,--(s_1&s_2),
o,$E$8:$E$25,
a,--(NOT(ISBLANK(s_1))),
f,FILTER(HSTACK(p,s,o,a),o=B3),
u,UNIQUE(CHOOSECOLS(f,1)),
t,DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,ROUND(SUM(FILTER(CHOOSECOLS(f,2),CHOOSECOLS(f,1)=y)),1)))),1),
y,DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,SUM(FILTER(CHOOSECOLS(f,4),CHOOSECOLS(f,1)=y))))),1)>0,
TEXTJOIN(CHAR(10),,u&" x "&t& " kg"&IF(y=TRUE," *","")))

 

[lampshade22]

Wow, thank you.
One last thing, how can I prevent it from displaying a CALC error if the array is empty?

 

[hagia_sofia]

Hello, just wrap the last part starting with TEXTJOIN into IFERROR function...