How to check for MID value in Unique(Filter(Filter))) function?

[nahaku]

I have a large data of items stored on multiple locations, i need to get only locations what address has value 1 on 8 position of the cell. I can get check it partially with Mid(location,8,1) it returns correct value but I do not know how to implement it in to Unique(filter)) function to list all locations at once. I am getting #Value error when i use this:
=(TRANSPOSE(SORT(UNIQUE(FILTER(FILTER(Table3[Location],Table3[SKU code]=$A3),MID(Table3[Location],8,1)=1)))))
I belive it does not understand that it should check each row value for that MID part.

ABC with OutGoodsStats.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
2	name	locations					B3=	1	2	3	4	5
3	X00127M9TN	#VALUE!
4	V263-UKAKKOA171851	104.28.1.4
5	V230-UKAXK183829-N2	116.07.3.5	116.07.4.5	116.07.5.3	116.07.5.5	116.07.6.5	116.28.1.6	116.28.2.6	116.28.3.6	116.28.4.6	116.30.1.2	116.30.1.6	116.30.2.2	116.30.2.4	116.30.2.6	116.30.3.2	116.30.3.4	116.30.3.6	116.30.4.2	116.30.4.4	119.05.2.1
6	V088-UKATUN1003322-N1	116.13.1.3	116.33.2.1	116.33.2.3	116.33.2.5	116.33.3.1	116.33.3.3	116.33.3.5	116.33.4.1	116.33.4.3	116.33.4.5	116.33.5.3	116.33.5.5	116.33.6.3	116.33.6.5	116.41.2.5	116.42.1.2	116.42.2.2	116.45.5.1
7	V290-UKE117555	114.04.1.4	114.12.2.4
8	V007-UKED162675	111.03.2.3	111.03.3.3	111.03.5.3	111.03.6.3	113.06.3.2
9	V251-UKE112764	110.33.1.1
10	V251-UKE97723	110.02.4.4
11	V290-UKESN1004013	120.30.1.4
12	WA8802UK	100.18.5.4	100.18.6.4	100.18.7.4
13	V290-UKESN1003739	112.10.4.4
14	V290-UKEDN1010190	110.07.1.3
15	V241-UKATC188963	202.02.5.6	202.04.6.2
16	V004-UKAFFN1010438	112.02.1.2	119.21.1.3	119.21.2.1
17	V290-UKEPN1010584	117.01.3.1	117.01.4.1	117.01.5.1	117.01.6.1
18	V290-UKED172332	108.08.3.2
19	QK-CLIVIA-BK-XH-COA	114.16.3.4
20	V230-UKAXK183795-N8	120.21.3.5	120.21.5.3	120.21.5.5	120.21.6.5	120.23.3.5	120.23.5.1	120.23.5.3	120.23.5.5	120.23.6.1	120.23.6.3	120.23.6.5	120.25.3.1	120.25.5.1	120.25.5.3	120.25.5.5	120.25.6.1	120.25.6.3	120.25.6.5
21	V190-UKAN1009243-N1	117.03.2.1	117.03.2.3	117.03.3.1	117.03.3.3	117.03.4.1	117.03.4.3	117.03.5.1	117.03.5.3	117.03.6.1	117.03.6.3
22	V261-UKATCN1003954	203.19.5.1
23	V230-UKAMR110349-N6	120.05.5.3	120.05.5.5	120.05.6.1	120.05.6.3	120.05.6.5	120.06.5.2	120.06.5.4	120.06.5.6	120.06.6.2	120.06.6.4	120.06.6.6	120.08.5.2	120.08.5.4	120.08.6.2	120.08.6.4	120.08.6.6
24	V251-UKATCN1004343	106.16.1.2	106.16.4.2	106.16.5.2
25	USELB10	003.14.4.1	003.15.2.1
26	USELA20	003.14.2.1	201.42.2.2
27	V230-UKAXK400812-N2	118.21.2.1	118.21.2.3	118.21.3.1	118.23.2.3	118.23.3.1	118.23.3.3	118.23.3.5	118.23.4.1	118.23.4.3	118.23.4.5
28	V290-UKESN1002325	111.30.3.2	111.30.3.4	111.30.3.6	111.30.4.2	111.30.4.4	111.30.5.2	111.32.2.2	111.32.3.2	111.32.4.2	111.32.5.2
29	V290-UKESN1003574	117.07.5.5
30	V260-UKAFF177004	111.23.1.1
Sheet1

Cell Formulas
Range	Formula
H2:L2	H2	=TRANSPOSE(MID(UNIQUE(FILTER(Table3[Location],Table3[SKU code]=$A3)),8,1))
A3:A368	A3	=FILTER(Summary!D:D,Summary!Q:Q="A")
B3	B3	=(TRANSPOSE(SORT(UNIQUE(FILTER(FILTER(Table3[Location],Table3[SKU code]=$A3),MID(Table3[Location],8,1)=1)))))
B4,B9:B11,B13:B14,B18:B19,B22,B29:B30,B27:K28,B25:C26,B24:D24,B23:Q23,B21:K21,B20:S20,B17:E17,B16:D16,B15:C15,B12:D12,B8:F8,B7:C7,B6:S6,B5:AF5	B4	=IF($A4<>"",IFERROR(TRANSPOSE(SORT(UNIQUE(FILTER(Table3[Location],Table3[SKU code]=$A4)))),"== No available =="),"")
Dynamic array formulas.

 

[Fluff]

Maybe
=TRANSPOSE(SORT(UNIQUE(FILTER(Table3[Location],(Table3[SKU code]=$A3)*(MID(Table3[Location],8,1)=1)))))

 

[nahaku]

=TRANSPOSE(SORT(UNIQUE(FILTER(Table3[Location],(Table3[SKU code]=$A3)*(MID(Table3[Location],8,1)=1)))))

Thank you for quick reply but i got there #Calc! error my guess is that it cannot understand what to do with
I am thinking about something in logic like this.
If SKU Code = Table3[SKU code] return its location from Table3[Location] if MID(Location,8,1) = 1.
But when i check the part of (MID(Table3[Location],8,1)=1)) it always shows one result, so i guess it takes in to consideration only first value in column.
Perhaps I would know to do it with Vlookup or index, but it returns only 1 value, and there is multiple values.

 

[Fluff]

In that case can you post the data in table3?

 

[nahaku]

In that case can you post the data in table3?

can i send that file to you in massage? but tomorrow

 

[Fluff]

If you want to share the file, then you will need to upload it to a share site & post the link in this thread. It is against board policy to share files privately.
That said, all you need to do is post the data using the XL2BB add-in.

 

[nahaku]

If you want to share the file, then you will need to upload it to a share site & post the link in this thread. It is against board policy to share files privately.
That said, all you need to do is post the data using the XL2BB add-in.

There are ofcourse other columns as well but i posted only 2 of them from where i am looking for the data. If you trying it in excel you will maybe need to get unique values from SKU code first, as the main post data are not probably including those SKUs, there is over 100k rows :P

ABC with OutGoodsStats.xlsx
	A	B
2	SKU code	Location
3	TWDE141W	001.43.3.1
4	TWLV056JMW	002.47.2.2
5	TWDE085DW	002.36.3.2
6	DKTAG007C1	002.62.2.1
7	TWACAL14AW	002.44.2.1
8	TWDE060W	002.43.3.1
9	TWDE113W	002.52.3.2
10	TWHPRA04BW	002.36.3.2
11	DKTBS031C1B	002.36.2.1
12	DKTMK006C1	003.11.2.1
13	TWAC021W	002.44.2.1
14	TWHP053W	002.47.3.2
15	TWHP075W	002.36.2.2
16	DKMA1527	002.35.2.1
17	TWAAX540W	002.56.3.1
18	TWDE089AW	002.33.3.1
19	DKTDW182C4	002.34.3.2
20	DKTDW184C5	002.40.3.1
21	DKDE159	002.46.3.2
22	DKLVT440B	002.33.2.1
23	TWLV057W	001.22.3.1
24	DKDE155	003.11.2.1
25	DKMA1713	002.35.2.1
26	TWTB095W	001.43.3.1
27	TJDEE7440	001.08.2.1
28	TWAAX441W	002.52.3.2
29	TWAAX541W	002.37.3.2
30	TWLVT440W	002.52.3.2
31	CBS18V3A	002.40.3.1
32	CBS12V3A	002.40.3.1
33	TWHP071W	001.27.3.1
34	TWHPNP03W	002.35.2.1
35	TWHPVR03W	002.44.2.1
36	DKAC14B3K	001.28.2.1
37	MA1375	002.51.3.2
38	MA1417	002.44.3.2
39	MA1437	002.39.2.1
40	MA1618	002.39.3.1
41	DKTBD050C1	002.52.3.2
42	SCDHP053	002.35.2.1
43	3084294	105.28.1.2
44	3385368	108.10.2.6
45	2592719	001.21.3.1
46	2680837	001.17.3.1
47	2207137	001.20.2.1
48	2438149	001.19.2.1
49	3488930	002.52.3.2
50	3654737	002.52.3.2
51	3655766	002.52.3.2
52	3651349	002.52.3.2
53	YDK-17	113.39.1.5
54	DH161102-22	113.22.2.6
55	GF08-G	002.63.3.2
56	GF01-B-06	002.43.3.1
57	141802	113.47.1.1
58	18NPK7011Q	113.47.1.3
59	18TY6005M	113.45.1.3
60	LUCY-M-04	113.32.1.4
61	1613-0603-02B	113.13.3.1
62	20NPK1638-3	113.22.1.2
63	20NPK1638-4	002.63.4.2
64	20NPK1637201706	113.37.1.3
65	20NPK1637201707	113.26.1.4
66	22NPK90311701	113.45.1.5
67	DH161002-22	113.03.1.5
68	DH70306-22	002.63.4.2
69	DH70306-22	113.25.1.5
70	LUCYB	113.14.1.2
71	DH080101-61CM	113.32.1.6
72	1507-16-04-45CM	113.23.1.1
73	DH70311-22	113.27.1.3
74	16NPK163401	113.42.1.6
75	DHR001-22	113.28.1.4
76	17NPK16191702	002.65.4.1
77	DH70308-22	113.11.2.5
78	DH70308-22	113.40.1.2
79	TY007-FF-2LB	113.06.4.6
80	TY007-FF-2LB	113.42.1.4
81	16NPK163509	002.45.4.1
82	16NPK163501	002.51.4.2
83	0603-1	002.52.4.2
84	0603-1	113.07.4.5
85	17NPK0071708	113.41.2.1
86	16NPK16191726	113.30.1.2
87	17NPK0081801	113.15.2.5
88	DH20160412-22	113.25.4.5
89	CFW-04W	113.41.1.3
90	GF07-B	002.63.2.2
91	0603-S-02-1	113.25.4.3
92	17NPK0071704	002.55.4.2
93	DH70311-18	112.43.1.5
94	AA002P-45CM	113.06.4.4
95	AA002P-45CM	113.38.1.6
96	23NPK1507B402	002.54.4.2
97	22NPKAD0101800	002.54.4.2
98	17NPK17741801	113.08.1.6
99	1630-16-02B-45CM	113.31.1.5
100	17NPK17741802	113.31.1.3
101	20NPK16381801	113.48.1.2
102	23NPK15071823	113.03.1.1
103	22NPK06031802	113.07.3.1
104	0603-S-02	113.48.1.4
105	24NPK177201	113.33.1.1
106	DH080101-18	002.60.4.1
107	DH70308-18	113.43.1.3
108	1800-1	113.28.1.6
109	DH161102-18	113.13.2.1
110	DH161102-18	113.44.1.4
111	20NPK1637-1901B	113.41.1.1
112	DH70306-18	113.09.2.1
113	DH70306-18	113.38.1.2
114	20NPK1637-1902G	113.21.1.3
115	24NPK1772-1904	113.19.1.5
116	24NPK1772-1901	113.41.1.5
117	24NPK1772-1911	113.45.1.1
118	28NPK15181701	113.26.1.2
119	17NPK1619-1901	113.25.1.3
120	AD010FF	002.64.4.1
121	18NPK1412	113.40.1.6
122	22NPK14121802	113.39.1.3
123	9042-2	113.13.1.3
124	MONKEY-1	113.24.2.6
125	MONKEY-2	112.39.1.5
126	22NPKN1613724	113.43.1.5
127	NPK1772	113.29.1.5
128	22NPK0602-1937	113.25.3.5
129	22NPK0602-1938	113.34.1.4
130	6098S-12	001.40.4.1
131	6098S-12	113.32.2.6
132	6098S-12	113.44.1.6
133	10NPK705610	002.47.4.1
134	19NPK15201701	113.28.1.2
135	DDAC-M-06-BELLY	113.20.2.2
136	DDAC-M-06-BELLY	113.46.1.6
137	24NPK1772-1913	113.37.1.1
138	20NPK9030PBOY-NOHAIR	113.39.1.1
139	AD010-16-01	113.25.2.3
140	MONKEY-3	113.06.3.4
141	MONKEY-3	113.40.1.4
142	MONKEY-5	113.22.1.4
143	MONKEY-5	113.24.2.4
144	MONKEY-6	002.46.4.2
145	1613-0603-01G	003.04.2.1
146	CFW-04P	113.43.1.1
147	18NPK0602-1917	113.23.1.3
148	24NPK1772-1903	113.33.1.3
149	ALIEN	002.56.4.2
150	18NPK14121801	002.63.4.2
151	22NPK0602-1906	113.07.4.1
152	22NPK0602-1906	113.38.1.4
153	22NPK0602-1907	113.42.1.2
154	24NPK1772-1935	113.34.1.2
155	22NPK0602-1904	113.27.1.1
156	18TY6005B	113.25.4.1
157	22NPK1931-2009T	113.26.1.6
158	WJ005BLA00S	001.37.2.1
159	WJ005BLA00M	001.18.3.1
160	WJ005BLA1XL	002.44.2.2
161	WJ005BLA00L	001.36.2.1
162	WJ005KHA1XL	002.43.3.2
163	WJ005BRO00M	001.08.2.1
164	WH001DGRA00M	002.39.2.2
165	WJ206BLA00M	002.39.2.2
166	WH022GRA00M	002.38.2.1
167	WH022RED00M	002.38.3.1
168	WH022GWB00M	002.42.2.1
169	WH022RGRA00M	002.50.3.2
170	DR762NARE00L	002.38.2.2
171	WC336GEN00L	002.48.2.1
172	WC336GEN00M	002.44.3.1
173	WH022SKY00M	002.38.2.1
174	DR059WRED00S	002.39.3.2
175	WC335RED00L	002.31.3.2
176	WC335RED00M	002.44.3.1
177	WC335RED00S	002.48.2.1
178	DR328WRED00S	002.36.3.2
179	DR328YEL00S	002.36.3.2
180	OT+YJ+2PC+LP	003.12.1.1
181	OT+TLDB	002.60.3.2
182	OT+BYTUI+1100MM+P4	201.24.1.6
183	SG+BACKPACK+50L+WLC	002.59.2.1
184	OT+HBLLKB+88	002.46.3.1
185	HB+EYETIE+001	002.36.3.1
186	OT+BLACKXLS+30MM	999.99.1.10
187	OT+WIGCAP+00	002.30.3.2
188	OT+HSNZ	002.30.3.2
189	OT+JBQPJ	001.12.1.1
190	OT+BARSTOOL+30CM	103.14.1.2
191	SHUANGMSCXJ88	108.06.3.2
192	BAISSCTCTJ	119.45.1.1
193	OT+PIANOSTOOL	104.27.2.3
194	OT+ABLR	002.58.3.1
195	OT+457MMSD+P20	002.41.3.1
196	OT+JFQ+P30	002.33.2.2
197	YY010	001.21.4.1
198	CZ012	002.32.1.2
199	BZ002	001.06.4.1
200	BZ008	002.41.1.2
201	FC911	109.15.1.5
202	UK-GYMP3014DC12VG4-4W48DWX10	001.09.2.1
203	UK-GYMP3014G4-DC12V48WW5	002.50.2.2
204	UK-GYMP3014G4-DC12V48CW10	002.50.3.1
205	UK-GYMP3014G4-DC12V48WW10	002.50.3.1
206	UK-GYMP3014G9-230V64WW10	002.41.3.2
207	UK-GYMP2835G9-230V33CW10	001.39.2.1
208	UK-GYMP2835G9-230V51WW10	002.65.3.2
209	UK-GYMP3014G4-AC12V48CW10	002.50.3.1
210	UK-PJJKQD18-25W	002.41.3.1
211	UK-SJD-FXA01CWNEW	002.54.2.1
212	UK-BD-6102WW	002.37.3.1
213	QJJQWJXCZJHB001A12	002.47.3.2
214	ITCRM_58	002.40.2.2
215	I7-PC-GD	003.08.2.1
216	XC-84GRAMR-21	002.55.3.1
217	XC-84GRAMR-12	002.54.2.1
218	XC-84GRAMR-13	002.54.2.1
219	XC-84GRAMR-HG-23	001.33.2.1
220	XC-84GRAMR-HG-13	001.42.1.1
221	XC-84GRAMR-11	002.54.2.1
222	UK16601W	001.30.3.1
223	C8-9525	108.05.1.3
224	C8-9524	108.08.1.2
225	C8-9523	112.13.1.3
226	C8-9521	110.18.1.2
227	C8-9521	111.16.1.2
228	OP21A001	001.41.1.1
229	OS18A001	002.29.1.2
230	171012602	002.52.3.2
231	188770501	002.50.4.2
232	UK-100-DYS-006	003.07.3.1
233	4LON-EHC01-R	002.42.3.2
234	4LON-ZP291-R	002.38.2.2
235	4LA-TWQ-MIX-R	002.40.3.1
236	WQ086-SHENLAN3XL	001.22.3.1
237	GMLHJ161BOHEI-4XL	002.33.1.2
238	GMLHJ161BOHUI-L	001.22.3.1
239	AUR3054	002.54.2.2
240	EAY1513	002.64.3.1
241	AUR2088	002.48.2.1
242	AUR3058	002.42.3.1
243	AUR2088R	002.48.2.1
244	AUR2098	001.41.3.1
245	AUR2101	002.34.2.1
246	AUR2105R	002.48.2.1
247	AUR8003U	001.41.3.1
248	AUR2107B	002.64.3.1
249	AUR2107BL	002.39.3.1
250	AUR2111G	002.42.3.1
251	AUR2109B	002.45.3.1
252	AUR2108G	002.38.2.1
253	AUR2108B	002.54.2.1
254	AUR2097B	002.34.2.1
255	CROSS1-UKWH-002	002.33.3.1
256	220VUK5050RG24-025	002.59.1.1
257	UK220VNEON24K100	002.43.1.2
258	LL155WH12W-WH	002.63.3.1
259	LL183AC-B22WW100W	002.49.3.1
260	M00192B	002.32.2.2
261	N1006-41A	001.40.3.1
262	3DL-4012	100.22.6.2
263	3DL-4015	108.04.2.4
264	3DL-4016	111.02.1.4
265	3DL-4019	002.38.3.1
266	3DL-4023	110.37.1.5
267	3DL-4026	001.28.3.1
268	3DL-4022	002.60.3.1
269	3DL-4024	112.10.2.6
270	3DL-4031	002.30.3.1
271	3DL-4025	112.19.3.5
272	3DL-4035	002.63.2.1
273	B168-A11	103.03.1.1
274	B188	103.03.1.3
275	3DL-4036	106.07.4.1
276	BY1903	109.05.3.1
277	CT3099	003.12.3.1
278	CE3984	002.29.2.1
279	PT4566	002.31.2.1
280	MBY173	002.47.3.1
281	WHB916	002.59.2.2
282	WHB917	002.58.2.2
283	CE5060	002.50.3.2
284	LBM6NM	002.45.3.1
285	66166	002.39.3.2
286	627162	002.57.1.2
287	532853	002.63.1.2
288	39739	002.45.4.1
289	91791	002.63.1.1
290	662228	100.12.8.2
291	414841	002.38.3.2
292	914991	002.44.4.1
293	407940	002.44.4.2
294	662229	100.04.6.4
295	662229	100.04.7.4
296	66246-DF	001.38.3.1
297	493094	001.44.3.1
298	353153	002.62.4.2
299	993099	002.59.4.2
300	573075	002.59.4.2
301	473074	002.44.4.2
302	351253	002.44.4.1
303	YB-BMW-6M-Y	002.44.3.2
304	B297	001.40.3.1
305	FXP	002.54.3.1
306	W-SLT-04	002.53.3.1
307	W-XYQ-51MM	001.19.3.1
308	W-LYQ	002.49.3.2
309	B70Y	001.18.2.1
310	K191	002.32.2.2
311	K165C	002.45.3.1
312	K193D	002.44.2.1
313	W001-ZDYZ-BLUE	001.18.2.1
314	UW-S2ZP-C	002.41.1.1
315	K165C-CR	002.40.1.1
316	K165D	111.25.1.3
317	B324--CR	002.40.1.2
318	K154B-CR	002.42.4.1
319	X000TT49BL	999.95.1.1
320	X000TTBDU1	001.37.3.1
321	X000TT5U3H	001.37.3.1
322	X000TT5TUB	001.37.3.1
323	X000TT49BV	001.37.3.1
324	X000TSZUPV	001.37.3.1
325	X000TTB2NT	999.95.1.1
326	X000W5JT21	001.37.3.1
327	X000XU8Y5D	001.37.3.1
328	X0010APF89	001.32.2.1
329	X000XRL8ZJ	001.37.3.1
330	X000ZAR42F	001.37.3.1
331	X000XKVHNT	001.37.3.1
332	X000ZISXEP	001.37.3.1
333	X000UNHCW9	001.37.3.1
334	X000XU94DJ	001.37.3.1
335	X000ZF1GAV	001.37.3.1
336	X0011Y4L39	001.37.3.1
337	X0010JX3NT	001.37.3.1
338	MY-EU-CM-L001	002.50.3.2
339	MY-EU-WB-002	001.34.3.1
340	YS-EB-BX-001	999.95.1.1
341	YS-EB-TP-001-1	002.41.3.2
342	010883	001.39.2.1
343	011349	001.41.2.1
344	011350	002.45.3.2
345	011342	002.45.3.2
346	010867	002.48.2.1
347	004919	001.39.2.1
348	004922	002.46.2.2
349	002610	002.41.3.1
350	004984	001.39.2.1
351	006846S1	002.56.2.2
352	013171	002.56.2.2
353	010268	002.49.3.2
354	010213	002.49.3.2
355	004554	002.49.3.2
356	006846	002.56.2.2
357	006851	002.48.3.2
358	010675	002.48.2.2
359	6940447101325	100.02.4.2
360	32001A-Y4	002.35.2.1
361	HL482UK	002.65.4.1
362	HYJUK	002.63.1.2
363	DC29VUK	001.10.1.1
364	DC42VUK	002.34.3.2
365	LH42VUK	002.36.2.2
366	LH54VUK	002.34.3.2
367	KL29VUK	002.53.2.2
368	KL42VUK	002.47.2.2
369	KL54VUK	002.47.3.2
370	DB001_UK	003.10.2.1
371	ZJ007WAUK	002.33.3.1
372	ZJ011_BUK	001.11.2.1
373	YY2XKL36UK	002.60.4.1
374	HL362UK-C	002.58.4.1
375	HL362UK-D	001.30.4.1
376	HYJL48V20UK-D	002.37.4.1
377	SH24BUK-D	003.12.4.1
378	SH24SUK-C	002.61.4.1
379	YY2XKL36UK-C	002.32.3.1
380	YYX24UK-D	002.55.4.1
381	YYXKL48UK-C	002.55.4.1
382	GM336V13AHUK-C	002.40.2.2
383	YY2XKL48UK-C	002.49.4.1
384	YYX36UK-C	002.52.4.2
385	LJSXTC920E-UK	002.44.3.1
386	KZPT003-UK	002.42.3.2
387	AR002_6-UK	001.27.3.1
388	PPQG120UK-X	002.47.2.1
389	AR012_1413	002.40.3.2
390	FT118-RETURN	002.60.2.1
391	VLZ-GD-8	002.62.4.2
392	LC-PM-018	002.50.3.2
393	V009-UKAPL108786-N12	117.02.5.6
394	V009-UKAPL183211-N6	107.38.1.6
395	V009-UKAPL88883-N8	002.47.1.2
396	K078-UKAXKN1001086-N4	105.13.2.1
397	V230-UKAXK183829-N2	116.07.3.5
398	V230-UKAXK183829-N2	116.07.4.5
399	V230-UKAXK183829-N2	116.07.5.3
400	V230-UKAXK183829-N2	116.07.5.5
401	V230-UKAXK183829-N2	116.07.6.5
402	V230-UKAXK183829-N2	116.28.1.6
403	V230-UKAXK183829-N2	116.28.2.6
404	V230-UKAXK183829-N2	116.28.3.6
405	V230-UKAXK183829-N2	116.28.4.6
406	V230-UKAXK183829-N2	116.30.1.2
407	V230-UKAXK183829-N2	116.30.1.6
408	V230-UKAXK183829-N2	116.30.2.2
409	V230-UKAXK183829-N2	116.30.2.4
410	V230-UKAXK183829-N2	116.30.2.6
411	V230-UKAXK183829-N2	116.30.3.2
412	V230-UKAXK183829-N2	116.30.3.4
413	V230-UKAXK183829-N2	116.30.3.6
414	V230-UKAXK183829-N2	116.30.4.2
415	V230-UKAXK183829-N2	116.30.4.4
416	V230-UKAXK183829-N2	119.05.2.1
417	V230-UKAXK183829-N2	119.05.2.3
418	V230-UKAXK183829-N2	119.05.2.5
419	V230-UKAXK183829-N2	119.05.3.1
420	V230-UKAXK183829-N2	119.05.3.5
421	V230-UKAXK183829-N2	119.07.1.3
422	V230-UKAXK183829-N2	119.07.3.1
423	V230-UKAXK183829-N2	119.07.3.3
424	V230-UKAXK183829-N2	119.07.3.5
425	V230-UKAXK183829-N2	119.09.2.1
426	V230-UKAXK183829-N2	119.09.3.1
427	V230-UKAXK183829-N2	119.09.3.3
428	V009-UKAPLN1002205-N6	112.13.2.1
429	V009-UKAPLN1002205-N6	112.13.3.1
430	V256-UKAMR138237-N6	118.24.1.6
431	V256-UKAMR138237-N6	118.24.2.6
432	V256-UKAMR138237-N6	118.24.3.6
433	V256-UKAMR138237-N6	118.24.4.6
434	V256-UKAMR138237-N6	118.24.5.6
435	V256-UKAMR138237-N6	118.24.6.6
436	V256-UKAMR138237-N6	118.26.1.2
437	V256-UKAMR138237-N6	118.26.1.4
438	V256-UKAMR138237-N6	118.26.1.6
439	V256-UKAMR138237-N6	118.26.2.2
440	V256-UKAMR138237-N6	118.26.2.4
441	V256-UKAMR138237-N6	118.26.2.6
442	V256-UKAMR138237-N6	118.26.3.2
443	V256-UKAMR138237-N6	118.26.3.4
444	V256-UKAMR138237-N6	118.26.3.6
445	V256-UKAMR138237-N6	118.26.4.2
446	V256-UKAMR138237-N6	118.26.4.4
447	V256-UKAMR138237-N6	118.26.4.6
448	V256-UKAMR138237-N6	118.26.5.2
449	V256-UKAMR138237-N6	118.26.5.4
450	V256-UKAMR138237-N6	118.26.5.6
451	V256-UKAMR138237-N6	118.26.6.2
452	V256-UKAMR138237-N6	118.26.6.4
453	V256-UKAMR138237-N6	118.26.6.6
454	V256-UKAMR138237-N6	118.28.1.2
455	V256-UKAMR138237-N6	118.28.1.4
456	V256-UKAMR138237-N6	118.28.2.2
457	V256-UKAMR138237-N6	118.28.2.4
458	V256-UKAMR138237-N6	118.28.2.6
459	V256-UKAMR138237-N6	118.28.3.2
460	V256-UKAMR138237-N6	118.28.3.4
461	V256-UKAMR138237-N6	118.28.3.6
462	V256-UKAMR138237-N6	118.28.4.2
463	V256-UKAMR138237-N6	118.28.4.4
464	V256-UKAMR138237-N6	118.28.4.6
465	V256-UKAMR138237-N6	118.28.5.2
466	V256-UKAMR138237-N6	118.28.5.4
467	V256-UKAMR138237-N6	118.28.5.6
468	V256-UKAMR138237-N6	118.28.6.2
469	V256-UKAMR138237-N6	118.28.6.4
470	V256-UKAMR138237-N6	118.28.6.6
471	V256-UKAMR138237-N6	118.30.1.2
472	V256-UKAMR138237-N6	118.30.1.4
473	V256-UKAMR138237-N6	118.30.1.6
474	V256-UKAMR138237-N6	118.30.2.2
475	V256-UKAMR138237-N6	118.30.2.4
476	V256-UKAMR138237-N6	118.30.2.6
477	V256-UKAMR138237-N6	118.30.3.2
478	V256-UKAMR138237-N6	118.30.3.4
479	V256-UKAMR138237-N6	118.30.3.6
480	V256-UKAMR138237-N6	118.30.4.2
481	V256-UKAMR138237-N6	118.30.4.4
482	V256-UKAMR138237-N6	118.30.4.6
483	V256-UKAMR138237-N6	118.30.5.2
484	V256-UKAMR138237-N6	118.30.5.4
485	V256-UKAMR138237-N6	118.30.5.6
486	V256-UKAMR138237-N6	118.30.6.2
487	V256-UKAMR138237-N6	118.30.6.4
488	V256-UKAMR138237-N6	118.30.6.6
489	V256-UKAMR138237-N6	118.32.1.2
490	V256-UKAMR138237-N6	118.32.1.4
491	V256-UKAMR138237-N6	118.32.1.6
492	V256-UKAMR138237-N6	118.32.2.2
493	V256-UKAMR138237-N6	118.32.2.4
494	V256-UKAMR138237-N6	118.32.2.6
495	V256-UKAMR138237-N6	118.32.3.2
496	V256-UKAMR138237-N6	118.32.3.4
497	V256-UKAMR138237-N6	118.32.3.6
498	V256-UKAMR138237-N6	118.32.4.2
499	V256-UKAMR138237-N6	118.32.4.4
InventoryData

 

[Fluff]

Ok, thanks for that, how about
=TRANSPOSE(SORT(UNIQUE(FILTER(Table3[Location],(Table3[SKU code]=$A3)*(MID(Table3[Location],8,1)="1")))))

 

[nahaku]

=TRANSPOSE(SORT(UNIQUE(FILTER(Table3[Location],(Table3[SKU code]=$A3)*(MID(Table3[Location],8,1)="1")))))

Thats it, Thank you!!! I was just looking on this and the previous solution you posted and i could not say what is different :D then i noticed the "" :P Good Job.

 

[Fluff]

Yup I missed that initially, but Mid always returns a text string so it needs to be "1", rather than 1