Filter using not FILTER but formula

M

makinmomb

Active Member
Joined
Dec 23, 2013
Messages
401
I have the following data on sheet1

COLOURART NO.BRANDSIZESURFACEPACKINGM2 CTNOUR CODEINCSTKBOOKFREE
Grey GCM04CEMENTUM60 X 60Matt31.08239.000.022,450.00 0
White GCM01CEMENTUM60 X 60Matt31.08239.000.012,450.00 0
CremaGGS12GRANTITE STONE30 X 60Rock81.44239.000.033,505.00 0
CremaGGS12GRANTITE STONE30 X 60Rock31.08239.000.782,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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
makinmomb said:
I have the following data on sheet1

COLOURART NO.BRANDSIZESURFACEPACKINGM2 CTNOUR CODEINCSTKBOOKFREE
Grey GCM04CEMENTUM60 X 60Matt31.08239.000.022,450.000
White GCM01CEMENTUM60 X 60Matt31.08239.000.012,450.000
CremaGGS12GRANTITE STONE30 X 60Rock81.44239.000.033,505.000
CremaGGS12GRANTITE STONE30 X 60Rock31.08239.000.782,590.000

<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
Click to expand...

Use Index+SMALL+IF Pattern:
=INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$A$2:$A$99=$A$2,ROW($2:$99),4^8),ROW(A1)))&""
Using Ctrl+Shift+Enter to input the formula. and drag left and down to complite the input.
 
Upvote 0
CheryBTL said:
Use Index+SMALL+IF Pattern:
=INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$A$2:$A$99=$A$2,ROW($2:$99),4^8),ROW(A1)))&""
Using Ctrl+Shift+Enter to input the formula. and drag left and down to complite the input.
Click to expand...

XXX


Since I am a junior user , wow the formula, if my result sheet 2 looks as follows, should I copy paste the formula on cell A5 ?

20 X 90SIZE
GripSURFACE
COLOURART NO.
<colgroup><col width="317" style="width: 238pt; mso-width-source: userset; mso-width-alt: 11593;"> <col width="65" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2377;"> <tbody> </tbody>
 
Upvote 0
Try this:

Layout

COLOUR
ART NO.
BRAND
SIZE
SURFACE
PACKING
M2 CTN
OUR CODE
INC
STK
BOOK
FREE
Sheet1
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
Grey
GCM04
CEMENTUM
20 X 90
Grip
3
1.08
239.000.02
2,450.00
White
GCM01
CEMENTUM
20 X 90
Grip
3
1.08
239.000.01
2,450.00
*******
********
***************
*******
********
********
*******
*********
*******
****
******
*****
*******

<tbody>
</tbody>

20 X 90
SIZE
Sheet2
Grip
SURFACE
*
COLOUR
ART NO.
Grey
GCM04
White
GCM01
*
*******
********
*******

<tbody>
</tbody>

Name

Data - Refers To: =Sheet1!$A$2:ÍNDICE(Sheet1!$L:$L;CORRESP("ZZZZZ";Sheet1!$A:$A);0)

Formula

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

=IFERROR(INDEX(Data,SMALL(IF(INDEX(Data,,MATCH($B$1,Sheet1!$A$1:$L$1,0))=$A$1,
IF(INDEX(Data,,MATCH($B$2,Sheet1!$A$1:$L$1,0))=$A$2,
ROW(Data)-ROW(INDEX(Data,1,1))+1)),ROWS($A$5:$A5)),COLUMNS($A$5:A$5)),"")

And copy to the right and down.

Markmzz
 
Last edited:
Upvote 0
markmzz said:
Try this:

Layout

COLOURART NO.BRANDSIZESURFACEPACKINGM2 CTNOUR CODEINCSTKBOOKFREESheet1
Grey GCM04CEMENTUM60 X 60Matt31.08239.000.022,450.000
White GCM01CEMENTUM60 X 60Matt31.08239.000.012,450.000
CremaGGS12GRANTITE STONE30 X 60Rock81.44239.000.033,505.000
CremaGGS12GRANTITE STONE30 X 60Rock31.08239.000.782,590.000
Grey GCM04CEMENTUM20 X 90Grip31.08239.000.022,450.00
White GCM01CEMENTUM20 X 90Grip31.08239.000.012,450.00
**************************************************************************************************

<tbody>
</tbody>

20 X 90SIZESheet2
GripSURFACE
*
COLOURART NO.
Grey GCM04
White GCM01
*
**********************

<tbody>
</tbody>

Name

Data - Refers To: =Sheet1!$A$2:ÍNDICE(Sheet1!$L:$L;CORRESP("ZZZZZ";Sheet1!$A:$A);0)

Formula

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

=IFERROR(INDEX(Data,SMALL(IF(INDEX(Data,,MATCH($B$1,Sheet1!$A$1:$L$1,0))=$A$1,
IF(INDEX(Data,,MATCH($B$2,Sheet1!$A$1:$L$1,0))=$A$2,
ROW(Data)-ROW(INDEX(Data,1,1))+1)),ROWS($A$5:$A5)),COLUMNS($A$5:A$5)),"")

And copy to the right and down.

Markmzz
Click to expand...

XXX

Yes I am sorted , wow I am very happy indeed to the solution, thanks again
 
Upvote 0
makinmomb said:
XXX

Yes I am sorted , wow I am very happy indeed to the solution, thanks again
Click to expand...

What is the trick to further shape the program if I want to display tiles of all kinds of surface since the drop down only gives selection of particular surface
 
Upvote 0
makinmomb said:
What is the trick to further shape the program if I want to display tiles of all kinds of surface since the drop down only gives selection of particular surface
Click to expand...

If I understand correctly what you want, maybe this can helps:

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

=IFERROR(INDEX(Data,SMALL(IF(IF($A$1="",$A$1,INDEX(Data,,MATCH($B$1,Sheet1!$1:$1,0)))=$A$1,
IF(IF($A$2="",$A$2,INDEX(Data,,MATCH($B$2,Sheet1!$1:$1,0)))=$A$2,
ROW(Data)-ROW(INDEX(Data,1,1))+1)),ROWS($A$5:$A5)),COLUMNS($A$5:A$5)),"")

And copy to the right and down.

Then to display data for all kinds of surface or size leave A1 or A2 or both empty.

Markmzz
 
Upvote 0
=INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$A$2:$A$99=$A$2,ROW($2:$99),4^8),ROW(A1)))&""

you just need to change $A$2 as the reall range. such as :$C$4

BTY:
I do not have permission to access this page. so post here.
 
Upvote 0
makinmomb said:
XXX

Yes I am sorted , wow I am very happy indeed to the solution, thanks again
Click to expand...

The program is working perfectly , I re checked and I found at once place it is not picking correctly, the
colour of the result on line one is correct except is picking wrongly In place of CREMA on the database it
is OLIVE and the art GGS12 needs to on the database reads gmr 94, what should be the cause of this small
error, I wish I could send the work file to someone on the team since solving it here would be difficult as the
whole program works I think except this recent notice

30 X 60SIZE
PolishedSURFACE
COLOURART NO.BRANDSIZESURFACEPACKINGM2 CTNOUR CODEINCSTKBOOKFREE
CremaGGS12REGAL30 X 60Polished61.08239.000.73252536.7037
BlackGMR97REGAL30 X 60Polished61.08239.000.7425255780578
NoceGMR85REGAL30 X 60Polished61.08239.001.1225250.8301
AlmondGMR90REGAL30 X 60Polished61.08239.001.13252517.3017
<colgroup><col width="110" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4022;"> <col width="65" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2377;"> <col width="107" style="width: 80pt; mso-width-source: userset; mso-width-alt: 3913;"> <col width="56" style="width: 42pt; mso-width-source: userset; mso-width-alt: 2048;"> <col width="58" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2121;"> <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="76" style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;"> <col width="35" style="width: 26pt; mso-width-source: userset; mso-width-alt: 1280;"> <col width="33" style="width: 25pt; mso-width-source: userset; mso-width-alt: 1206;" span="3"> <tbody> </tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Conditional formating where there are 2 values in a cell
Replies
3
Views
911
Godders199
G
S
  • Question
Conditional Formating Required for changing Dates based on 2 different cell dates
Replies
1
Views
682
Toadstool
Toadstool
N
  • Question
excel macro to split data into multiple worksheets based on conditions worksheet
Replies
4
Views
4K
Fluff
Fluff
B
  • Question
Matching Number To Number In text
2 3
Replies
23
Views
2K
Twollaston
T
F
  • Question
Excel data matrix into a pivot
Replies
5
Views
3K
alansidman
A

Forum statistics

Threads
1,214,646
Messages
6,120,715
Members
448,985
Latest member
chocbudda

Share this page

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
Back
Top