Forum Discussion
Short out short stock item.
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,""))
For this task you can use the FILTER() function in combination with TEXTJOIN().
G6 = A1
=TEXTJOIN(", ",TRUE,FILTER($B$2:$B$12,($E$2:$E$12<0)*($A$2:$A$12=$G6)))
Thank you for your comment dscheikey . But I'm using Excel 2019 version where FILTER function not available.
HansVogelaarshowed you how to do it without the FILTER() function. I could not have known that Excel 2019 does not have a FILTER() function. I looked up the filter function on the help page beforehand. The availability in Excel 2019 is shown there.
- Thank you for your support.
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.