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.

 

[nahaku]

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

Can I bother you once again, its about adding another filter in the filter you adviced to me.
The thing is, i did not considered it before, that the "1" represents ground floor, but there are certain locations what are already on ground flor and they are starting like this: 001,002,003,096,097,098,099,999
I was thinking about using Wildcards but Filter/search/find does not work with wildcards. Also I tried add to your function with another *()*()*() but I think that it just represents and()and()and() so I ended up with no results. Then i tried:

=TRANSPOSE(SORT(UNIQUE(FILTER(Table3[Location],(Table3[SKU code]=$A16) *OR((MID(Table3[Location],8,1)="1",LEFT(Table3[Location],3)="097",LEFT(Table3[Location],3)="096",LEFT(Table3[Location],3)="099",LEFT(Table3[Location],3)="999"))))))

but that returned everything like I even did not used a filter.
When I do it one by one *LEFT(Table3[Location],3)="097" itworks, but because it is returning Arrays I would have to use all Width of Excel sheet to return results. It would be really hard to look at, or find what we need.
Or if it helps there could be some Wildcards as follow: 1??.??.1.?, 2??.??.1.?, 999.??.?.?, 0??.??.?.? , But so far I could not make them work as it needs to be in "" and filter things that it is all Text

=TRANSPOSE(SORT(UNIQUE(FILTER(Table3[Location],(Table3[SKU code]=$A16)*(COUNTIF(Table3[SKU code],"1??.??.1.?")+COUNTIF(Table3[SKU code],"2??.??.1.?")+COUNTIF(Table3[SKU code],"0??.??.?.?")+COUNTIF(Table3[SKU code],"999.??.?.?"))))))

But this returns #CALC! error

 

[Fluff]

It needs to be like
=TRANSPOSE(SORT(UNIQUE(FILTER(Table3[Location],(Table3[SKU code]=$A5)*((MID(Table3[Location],8,1)="1")+(LEFT(Table3[Location],3)="097")+(LEFT(Table3[Location],3)="096")+(LEFT(Table3[Location],3)="099")+(LEFT(Table3[Location],3)="999"))))))

 

[nahaku]

It needs to be like
=TRANSPOSE(SORT(UNIQUE(FILTER(Table3[Location],(Table3[SKU code]=$A5)*((MID(Table3[Location],8,1)="1")+(LEFT(Table3[Location],3)="097")+(LEFT(Table3[Location],3)="096")+(LEFT(Table3[Location],3)="099")+(LEFT(Table3[Location],3)="999"))))))

Great job, did not know i can use + in filter :P everyday some new knowledge. Thank you.

 

[Fluff]

You're welcome & thanks for the feedback.