Nov 06 2022 08:17 AM
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.
Nov 06 2022 10:12 AM
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)))
Nov 06 2022 06:36 PM
Thank you for your comment @dscheikey . But I'm using Excel 2019 version where FILTER function not available.
Nov 07 2022 02:28 AM
SolutionIn 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,""))
Nov 07 2022 07:18 AM
@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.
Nov 07 2022 08:52 AM
Nov 07 2022 02:28 AM
SolutionIn 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,""))