Forum Discussion
Solved
Short out short stock item.
Product Component Stock Required Qty Short/Excess A1 z1 22 31 -9 A1 y1 40 40 0 A1 x1 32 21 11 A1 u1 43 40 3 A1 v1 37 43 -6 B1 c1 30 35 -5 B1 z1...
In Excel 2019, you can use an array formula confirmed by pressing Ctrl+Shift+Enter:
The products A1 and B1 are in A15:A16 in the screenshot above. The formula in B15 is
=TEXTJOIN(",",TRUE,IF(($A$2:$A$12=A15)*($E$2:$E$12<0),$B$2:$B$12,""))
Thank you for your comment dscheikey . But I'm using Excel 2019 version where FILTER function not available.
In Excel 2019, you can use an array formula confirmed by pressing Ctrl+Shift+Enter:
The products A1 and B1 are in A15:A16 in the screenshot above. The formula in B15 is
=TEXTJOIN(",",TRUE,IF(($A$2:$A$12=A15)*($E$2:$E$12<0),$B$2:$B$12,""))
- Thanks HansVogelaar & dscheikey. Because I've learn two way.