SOLVED

Short out short stock item.

New Contributor

 

ProductComponentStockRequired QtyShort/Excess
A1z12231-9
A1y140400
A1x1322111
A1u143403
A1v13743-6
B1c13035-5
B1z12231-9
B1k12736-9
B1x1322111
B1m13238-6
B1n142348

 

Short Materials:

A1z1,v1
B1c1,z1,k1,m1

 

I'm using office 2019.

6 Replies

@shohagppl 

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

 

dscheikey_0-1667758238966.png

 

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

@shohagppl 

In Excel 2019, you can use an array formula confirmed by pressing Ctrl+Shift+Enter:

S1953.png

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,""))

@shohagppl 

@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

dscheikey_0-1667834126274.png

 

Thanks @Hans Vogelaar & dscheikey. Because I've learn two way.
Thank you for your support.