Create Array of Distinct Values in a Column Based on Another Column

[zgadson]

Could use some help! Prefer to use a formula over VBA, but I'm not sure if that's possible. Based on the attached screenshot, I'd like to create a new column with an array of Aisles based on matching ITM.DESC.
IE, for my item "23 OZ ORGANIC MARINARA", I'd like a new column to show "M1, M2"

If I need to do it with VBA that's fine, but I'd like to avoid having to manually run a macro every time I update my sheet.

Thanks!
Zach

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C	D	E
1	Aisle	In use?	Latitude	Itm.Desc	Column1
2	CA110SA	Yes	54.615912	Cumbria	CA110SA
3	DY140JB	Yes	52.409181	Shropshire	DY140JB
4	OL146XB	Yes	53.680058	West Yorkshire	OL146XB, BD63FH, BD206QX, HD73EB, HD33GS, HX77LP
5	DL131JZ	Yes	54.73854	County Durham	DL131JZ, DH85EJ, DL120RY
6	NE479LQ	Yes	54.895638	Northumberland	NE479LQ
7	S361GJ	Yes	53.472455	South Yorkshire	S361GJ
8	BD63FH	Yes	53.767997	West Yorkshire	OL146XB, BD63FH, BD206QX, HD73EB, HD33GS, HX77LP
9	BD206QX	Yes	53.88118	West Yorkshire	OL146XB, BD63FH, BD206QX, HD73EB, HD33GS, HX77LP
10	OL139RN	Yes	53.692248	Lancashire	OL139RN
11	HD73EB	No	53.582804	West Yorkshire	OL146XB, BD63FH, BD206QX, HD73EB, HD33GS, HX77LP
12	TQ137PE	Yes	50.544961	Devon	TQ137PE
13	DH85EJ	Yes	54.853973	County Durham	DL131JZ, DH85EJ, DL120RY
14	DL85JZ	Yes	54.323588	North Yorkshire	DL85JZ, DL83SH
15	DL83SH	Yes	54.299948	North Yorkshire	DL85JZ, DL83SH
16	ST102LA	Yes	53.046617	Staffordshire	ST102LA
17	HD33GS	Yes	53.659793	West Yorkshire	OL146XB, BD63FH, BD206QX, HD73EB, HD33GS, HX77LP
18	HX77LP	No	53.748059	West Yorkshire	OL146XB, BD63FH, BD206QX, HD73EB, HD33GS, HX77LP
19	DL120RY	Yes	54.623951	County Durham	DL131JZ, DH85EJ, DL120RY
Work

Cell Formulas
Range	Formula
E2:E19	E2	=TEXTJOIN(", ",1,FILTER([Aisle],[Itm.Desc]=[@[Itm.Desc]]))

 

[zgadson]

Tried it but got an error saying "That function isn't valid". I'm using Excel 2019 if that makes a difference.

 

[Fluff]

Filter is only available in 365, try

+Fluff v2.xlsm
	A	B	C	D	E
1	Aisle	In use?	Latitude	Itm.Desc	Column1
2	CA110SA	Yes	54.615912	Cumbria	CA110SA
3	DY140JB	Yes	52.409181	Shropshire	DY140JB
4	OL146XB	Yes	53.680058	West Yorkshire	OL146XB, BD63FH, BD206QX, HD73EB, HD33GS, HX77LP
5	DL131JZ	Yes	54.73854	County Durham	DL131JZ, DH85EJ, DL120RY
6	NE479LQ	Yes	54.895638	Northumberland	NE479LQ
7	S361GJ	Yes	53.472455	South Yorkshire	S361GJ
8	BD63FH	Yes	53.767997	West Yorkshire	OL146XB, BD63FH, BD206QX, HD73EB, HD33GS, HX77LP
9	BD206QX	Yes	53.88118	West Yorkshire	OL146XB, BD63FH, BD206QX, HD73EB, HD33GS, HX77LP
10	OL139RN	Yes	53.692248	Lancashire	OL139RN
11	HD73EB	No	53.582804	West Yorkshire	OL146XB, BD63FH, BD206QX, HD73EB, HD33GS, HX77LP
12	TQ137PE	Yes	50.544961	Devon	TQ137PE
13	DH85EJ	Yes	54.853973	County Durham	DL131JZ, DH85EJ, DL120RY
14	DL85JZ	Yes	54.323588	North Yorkshire	DL85JZ, DL83SH
15	DL83SH	Yes	54.299948	North Yorkshire	DL85JZ, DL83SH
16	ST102LA	Yes	53.046617	Staffordshire	ST102LA
17	HD33GS	Yes	53.659793	West Yorkshire	OL146XB, BD63FH, BD206QX, HD73EB, HD33GS, HX77LP
18	HX77LP	No	53.748059	West Yorkshire	OL146XB, BD63FH, BD206QX, HD73EB, HD33GS, HX77LP
19	DL120RY	Yes	54.623951	County Durham	DL131JZ, DH85EJ, DL120RY
Work

Cell Formulas
Range	Formula
E2:E19	E2	=TEXTJOIN(", ",1,IF([Itm.Desc]=[@[Itm.Desc]],[Aisle],""))

 

[zgadson]

Gets me one step further at least! Aisles repeat as if it's including every aisle for that Item. I'm not an idiot when it comes to formulas, but this one is eluding me. Even tried an INDEX, MATCH and it still adds repeated Aisles, but with this at least the count of repeats seems to match the number of times an item is in an Aisle.

=TEXTJOIN(", ",1,IF([ITM.CODE]=[@[ITM.CODE]],INDEX([AISLE],MATCH(0,COUNTIF($B$3:B9,[AISLE]),0)), ""))

Nothing more annoying than messing with a formula for hours at a time, so I do sincerely appreciate your help!

 

[Fluff]

I'm afraid I have no idea how to do that with 2019, unless your happy with a UDF.

 

[zgadson]

Nah, trying to keep it simple. However, I did actually find a workaround using INDEX, MATCH on a hidden sheet to make a list of unique values, then took your TEXTJOIN suggestion to manually pull everything into a single cell, delineated with commas between. =A1 & ", " & A2 & ", " ... etc.

Thanks again!

 

[Fluff]

Glad you sorted it & thanks for the feedback.