ProductComponentStockRequired QtyShort/ExcessA1z12231-9A1y140400A1x1322111A1u143403A1v13743-6B1c13035-5B1z12231-9B1k12736-9B1x1322111B1m13238-6B1n142348 Short Materials:A1z1,v1B1c1,z1,k1,m1 I'm using office 2019.

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

Forum Discussion

shohagppl

Copper Contributor

Nov 06, 2022

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

excel

Formulas and Functions

HansVogelaar
Nov 07, 2022
shohagppl

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

6 Replies

dscheikey
Bronze Contributor
Nov 06, 2022
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)))

shohagppl.xlsx10 KB
- shohagppl
  Copper Contributor
  Nov 07, 2022
  Thank you for your comment dscheikey . But I'm using Excel 2019 version where FILTER function not available.
  - dscheikey
    Bronze Contributor
    Nov 07, 2022
    shohagppl
    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.
    
    Help