SOLVED

New Contributor

# 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 22 31 -9 B1 k1 27 36 -9 B1 x1 32 21 11 B1 m1 32 38 -6 B1 n1 42 34 8

Short Materials:

 A1 z1,v1 B1 c1,z1,k1,m1

I'm using office 2019.

6 Replies

# Re: Short out short stock item.

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)))``

# Re: Short out short stock item.

Thank you for your comment @dscheikey . But I'm using Excel 2019 version where FILTER function not available.

best response confirmed by shohagppl (New Contributor)
Solution

# Re: 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,""))

# Re: Short out short stock item.

@Hans Vogelaarshowed 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.

Help

# Re: Short out short stock item.

Thanks @Hans Vogelaar & dscheikey. Because I've learn two way.

# Re: Short out short stock item.

Thank you for your support.