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

nahaku

Board Regular
Joined
Mar 19, 2020
Messages
106
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTU
2namelocationsB3=12345
3X00127M9TN#VALUE!
4V263-UKAKKOA171851104.28.1.4
5V230-UKAXK183829-N2116.07.3.5116.07.4.5116.07.5.3116.07.5.5116.07.6.5116.28.1.6116.28.2.6116.28.3.6116.28.4.6116.30.1.2116.30.1.6116.30.2.2116.30.2.4116.30.2.6116.30.3.2116.30.3.4116.30.3.6116.30.4.2116.30.4.4119.05.2.1
6V088-UKATUN1003322-N1116.13.1.3116.33.2.1116.33.2.3116.33.2.5116.33.3.1116.33.3.3116.33.3.5116.33.4.1116.33.4.3116.33.4.5116.33.5.3116.33.5.5116.33.6.3116.33.6.5116.41.2.5116.42.1.2116.42.2.2116.45.5.1
7V290-UKE117555114.04.1.4114.12.2.4
8V007-UKED162675111.03.2.3111.03.3.3111.03.5.3111.03.6.3113.06.3.2
9V251-UKE112764110.33.1.1
10V251-UKE97723110.02.4.4
11V290-UKESN1004013120.30.1.4
12WA8802UK100.18.5.4100.18.6.4100.18.7.4
13V290-UKESN1003739112.10.4.4
14V290-UKEDN1010190110.07.1.3
15V241-UKATC188963202.02.5.6202.04.6.2
16V004-UKAFFN1010438112.02.1.2119.21.1.3119.21.2.1
17V290-UKEPN1010584117.01.3.1117.01.4.1117.01.5.1117.01.6.1
18V290-UKED172332108.08.3.2
19QK-CLIVIA-BK-XH-COA114.16.3.4
20V230-UKAXK183795-N8120.21.3.5120.21.5.3120.21.5.5120.21.6.5120.23.3.5120.23.5.1120.23.5.3120.23.5.5120.23.6.1120.23.6.3120.23.6.5120.25.3.1120.25.5.1120.25.5.3120.25.5.5120.25.6.1120.25.6.3120.25.6.5
21V190-UKAN1009243-N1117.03.2.1117.03.2.3117.03.3.1117.03.3.3117.03.4.1117.03.4.3117.03.5.1117.03.5.3117.03.6.1117.03.6.3
22V261-UKATCN1003954203.19.5.1
23V230-UKAMR110349-N6120.05.5.3120.05.5.5120.05.6.1120.05.6.3120.05.6.5120.06.5.2120.06.5.4120.06.5.6120.06.6.2120.06.6.4120.06.6.6120.08.5.2120.08.5.4120.08.6.2120.08.6.4120.08.6.6
24V251-UKATCN1004343106.16.1.2106.16.4.2106.16.5.2
25USELB10003.14.4.1003.15.2.1
26USELA20003.14.2.1201.42.2.2
27V230-UKAXK400812-N2118.21.2.1118.21.2.3118.21.3.1118.23.2.3118.23.3.1118.23.3.3118.23.3.5118.23.4.1118.23.4.3118.23.4.5
28V290-UKESN1002325111.30.3.2111.30.3.4111.30.3.6111.30.4.2111.30.4.4111.30.5.2111.32.2.2111.32.3.2111.32.4.2111.32.5.2
29V290-UKESN1003574117.07.5.5
30V260-UKAFF177004111.23.1.1
Sheet1
Cell Formulas
RangeFormula
H2:L2H2=TRANSPOSE(MID(UNIQUE(FILTER(Table3[Location],Table3[SKU code]=$A3)),8,1))
A3:A368A3=FILTER(Summary!D:D,Summary!Q:Q="A")
B3B3=(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:AF5B4=IF($A4<>"",IFERROR(TRANSPOSE(SORT(UNIQUE(FILTER(Table3[Location],Table3[SKU code]=$A4)))),"== No available =="),"")
Dynamic array formulas.
 

nahaku

Board Regular
Joined
Mar 19, 2020
Messages
106
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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:
VBA Code:
=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
VBA Code:
=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
 
Last edited:

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Mar 19, 2020
Messages
106
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,390
Messages
5,636,035
Members
416,892
Latest member
Bensch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top