Filter using not FILTER but formula

[makinmomb]

I have the following data on sheet1

COLOUR	ART NO.	BRAND	SIZE	SURFACE	PACKING	M2 CTN	OUR CODE	INC	STK	BOOK	FREE
Grey	GCM04	CEMENTUM	60 X 60	Matt	3	1.08	239.000.02	2,450.00			0
White	GCM01	CEMENTUM	60 X 60	Matt	3	1.08	239.000.01	2,450.00			0
Crema	GGS12	GRANTITE STONE	30 X 60	Rock	8	1.44	239.000.03	3,505.00			0
Crema	GGS12	GRANTITE STONE	30 X 60	Rock	3	1.08	239.000.78	2,590.00			0

<colgroup><col width="52" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1901;"> <col width="54" style="width: 41pt; mso-width-source: userset; mso-width-alt: 1974;"> <col width="95" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3474;"> <col width="44" style="width: 33pt; mso-width-source: userset; mso-width-alt: 1609;"> <col width="55" style="width: 41pt; mso-width-source: userset; mso-width-alt: 2011;"> <col width="52" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1901;"> <col width="44" style="width: 33pt; mso-width-source: userset; mso-width-alt: 1609;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;"> <col width="49" style="width: 37pt; mso-width-source: userset; mso-width-alt: 1792;"> <col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 987;"> <col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;"> <col width="32" style="width: 24pt; mso-width-source: userset; mso-width-alt: 1170;"> <tbody> </tbody>

Can a drop box on sheet 2 specifying tile size pull data or without drop box , I know the normal filter is the best option but trying to spoon feed the sales team since some are weak users, they would hardly know how to use the FILTER option

 

[markmzz]

The cell took the formula and the result is blank cell , I have requested to send the file to your mail , I wish I can take it too work 2moro

Did you used Ctrl+Shift+Enter to enter the formula?

Maybe I'm wrong, but I think that this is the problem here.

Markmzz

 

[markmzz]

Here is the result of sheet2 (with my last formula):

20 X 90	SIZE	Sheet2
Grip	SURFACE
500 to 750	QTY
COLOUR	ART NO.
Grey	GCM04	*
Color23	GCM14	*
Color27	GCM23	*
Color36	GCM29	*
Color56	GCM41	*
Color64	GCM44	*
Color66	GGS12	*
**************	********	*

<tbody>
</tbody>

Markmzz

 

[alvin-chung]

Hi, making use of your existing names (i.e. data) try this array formula (ctrl+shift+enter) in A6 and copy across.

A6{=IFERROR(INDEX(data,SMALL(IF(((INDEX(data,,MATCH($B$1,Sheet1!$A$1:$L$1,0))=$A$1)*(INDEX(data,,MATCH($B$2,Sheet1!$A$1:$L$1,0))=$A$2)*(INDEX(data,,MATCH($B$3,Sheet1!$A$1:$L$1,0))>=VALUE(LEFT($A$3,SEARCH(" ",$A$3)-1)))*(INDEX(data,,MATCH($B$3,Sheet1!$A$1:$L$1,0))<=IFERROR(VALUE(MID($A$3,SEARCH("to ",$A$3)+3,LEN($A$3)-SEARCH("to ",$A$3)+3)),9.99999999999999E+307)))>0,(INDEX(data,,MATCH($B$1,Sheet1!$A$1:$L$1,0))=$A$1)*(INDEX(data,,MATCH($B$2,Sheet1!$A$1:$L$1,0))=$A$2)*(INDEX(data,,MATCH($B$3,Sheet1!$A$1:$L$1,0))>=VALUE(LEFT($A$3,SEARCH(" ",$A$3)-1)))*(INDEX(data,,MATCH($B$3,Sheet1!$A$1:$L$1,0))<=IFERROR(VALUE(MID($A$3,SEARCH("to ",$A$3)+3,LEN($A$3)-SEARCH("to ",$A$3)+3)),9.99999999999999E+307))*(ROW(data)-MIN(ROW(data))+1)),ROWS(A$6:A6)),COLUMN()),"")}

ps: this formula is extensible if you have more criteria in future!

 

[makinmomb]

Hi, making use of your existing names (i.e. data) try this array formula (ctrl+shift+enter) in A6 and copy across.

A6{=IFERROR(INDEX(data,SMALL(IF(((INDEX(data,,MATCH($B$1,Sheet1!$A$1:$L$1,0))=$A$1)*(INDEX(data,,MATCH($B$2,Sheet1!$A$1:$L$1,0))=$A$2)*(INDEX(data,,MATCH($B$3,Sheet1!$A$1:$L$1,0))>=VALUE(LEFT($A$3,SEARCH(" ",$A$3)-1)))*(INDEX(data,,MATCH($B$3,Sheet1!$A$1:$L$1,0))<=IFERROR(VALUE(MID($A$3,SEARCH("to ",$A$3)+3,LEN($A$3)-SEARCH("to ",$A$3)+3)),9.99999999999999E+307)))>0,(INDEX(data,,MATCH($B$1,Sheet1!$A$1:$L$1,0))=$A$1)*(INDEX(data,,MATCH($B$2,Sheet1!$A$1:$L$1,0))=$A$2)*(INDEX(data,,MATCH($B$3,Sheet1!$A$1:$L$1,0))>=VALUE(LEFT($A$3,SEARCH(" ",$A$3)-1)))*(INDEX(data,,MATCH($B$3,Sheet1!$A$1:$L$1,0))<=IFERROR(VALUE(MID($A$3,SEARCH("to ",$A$3)+3,LEN($A$3)-SEARCH("to ",$A$3)+3)),9.99999999999999E+307))*(ROW(data)-MIN(ROW(data))+1)),ROWS(A$6:A6)),COLUMN()),"")}

ps: this formula is extensible if you have more criteria in future!

WOW the formula pics all the 3 criteria , except its programmed for column J the stock while it needs to be programmed for column L the free stock after booked stock , test on the file I sent , 60 60 matt 250 to 500 , gih 06 is 294 but booked stock is 296 thus free is almost nil , it should thus not display

Please check and advice changes , wow I can now take it to work , how do further program for tiles less than 250 , validation 0 to 250

 

[alvin-chung]

Hi, the formula was designed to be flexible in that you just need to change the third rules by editing B3 from "STK" to "FREE" and it'll look into column L
Try it

 

[markmzz]

how do further program for tiles less than 250 , validation 0 to 250

Try this:

In A5 - use Ctrl+Shift+Enter and not only Enter to enter the formula

=IFERROR(INDEX(Data,SMALL(IF((QTY>=IFERROR(--LEFT(SUBSTITUTE($A$3,"to","  "),4),0))*(QTY < IFERROR(--RIGHT(SUBSTITUTE($A$3,"to","  "),4),99^99))*
(COUNTIF($A$1,SIZE)+($A$1=""))*(COUNTIF($A$2,SURFACE)+($A$2="")),ROW(Data)-ROW(INDEX(Data,1,1))+1),ROWS(A$5:A5)),COLUMNS($A5:A5)),"")

Markmzz