Filter formula
 

Hi,

I am looking for a formula that can act as a filter.

In sheet AA I have a table with headings products, date, diameter etc
starting from col A.

I would like in sheet BB to be able just to see data corresponding to a
certain product, call it Cars. The user enters Cars in B2 sheet BB and the
formula fills in the table in sheet BB with only the data for Cars from sheet
AA.

Is this possible without code?

Thanks

Filter formula
 

Hi,
I assume sheet BB has the same structure than sheet AA so in B2 enter

=index(AA!$B$1:$B$10000,match(A2,AA!$A$1:$A$10000, 0))

"LiAD" wrote:

Hi,

I am looking for a formula that can act as a filter.

In sheet AA I have a table with headings products, date, diameter etc
starting from col A.

I would like in sheet BB to be able just to see data corresponding to a
certain product, call it Cars. The user enters Cars in B2 sheet BB and the
formula fills in the table in sheet BB with only the data for Cars from sheet
AA.

Is this possible without code?

Thanks

Filter formula
 

Yes, it is possible.

In a spare column in sheet AA (eg column M) put this formula on row 2:

=IF(A2="","",A2&"_"&COUNTIF(A$2:A2,A2))

and copy this down as far as you like (even beyond your data). I'm
assuming that your products are listed in column A, so adjust if
necessary.

Then in M3 of sheet BB you can have this formula:

=IF(B$2="","",IF(ISNA(MATCH(B$2&"_"&ROW(A1),'AA'!M :M,0)),"",MATCH(B
$2&"_"&ROW(A1),'AA'!M:M,0)))

and again copy this down as far as you might need it (it will show
blanks beyond where you have matching data, so it doesn't matter how
far you take it).

Then in A3 of sheet BB you can have this formula:

=IF($M3="","",INDEX('BB'!A:A,$M3))

Then you can copy this across for as many columns as you have, and
then format the cells containing dates appropriately. Then copy these
formulae from row 3 down as far as you need (or beyond).

Now, just change the entry in B2 and the display will change just like
a filter.

Hope this helps.

Pete


On Mar 26, 1:27*pm, LiAD wrote:
Hi,

I am looking for a formula that can act as a filter.

In sheet AA I have a table with headings products, date, diameter etc
starting from col A.

I would like in sheet BB to be able just to see data corresponding to a
certain product, call it Cars. *The user enters Cars in B2 sheet BB and the
formula fills in the table in sheet BB with only the data for Cars from sheet
AA.

Is this possible without code?

Thanks

Filter formula
 

LiAD wrote:
Hi,

I am looking for a formula that can act as a filter.

In sheet AA I have a table with headings products, date, diameter etc
starting from col A.

I would like in sheet BB to be able just to see data corresponding to a
certain product, call it Cars. The user enters Cars in B2 sheet BB and the
formula fills in the table in sheet BB with only the data for Cars from sheet
AA.

Is this possible without code?

Thanks


Assuming that you have headings on sheet BB from B3 across to match the headings
on sheet AA, put the following array formula (commit with CTRL+SHIFT+ENTER) in
A4 on sheet BB:

=SMALL(IF(ISNUMBER(SEARCH($B$2,AA!$A$2:$A$1000)),
ROW(AA!$A$2:$A$1000),""),ROW(1:1))

Now put this "normal" formula in B4 and copy down and across as needed:

=IF(ISNUMBER($A4),INDEX(AA!A$1:A$8,$A4),"")

You can hide column A in sheet BB.

Filter formula
 

Glenn wrote:
LiAD wrote:
Hi,

I am looking for a formula that can act as a filter.

In sheet AA I have a table with headings products, date, diameter etc
starting from col A.

I would like in sheet BB to be able just to see data corresponding to
a certain product, call it Cars. The user enters Cars in B2 sheet BB
and the formula fills in the table in sheet BB with only the data for
Cars from sheet AA.

Is this possible without code?

Thanks


Assuming that you have headings on sheet BB from B3 across to match the
headings on sheet AA, put the following array formula (commit with
CTRL+SHIFT+ENTER) in A4 on sheet BB:

=SMALL(IF(ISNUMBER(SEARCH($B$2,AA!$A$2:$A$1000)),
ROW(AA!$A$2:$A$1000),""),ROW(1:1))

Now put this "normal" formula in B4 and copy down and across as needed:

=IF(ISNUMBER($A4),INDEX(AA!A$1:A$8,$A4),"")

You can hide column A in sheet BB.


Forgot to mention that you need to fill A4 down as well.

Filter formula
 

Hi,

Thanks but his will only return one big list of the small result that it
finds.

What I am looking for is a formula that returns the first, then the second,
then the third etc etc item that matches the product. So exactly the same
result I would have by using the standard filters.

Do you know how to do this?

Thansk for your help

"Eduardo" wrote:

Hi,
I assume sheet BB has the same structure than sheet AA so in B2 enter

=index(AA!$B$1:$B$10000,match(A2,AA!$A$1:$A$10000, 0))

"LiAD" wrote:

Hi,

I am looking for a formula that can act as a filter.

In sheet AA I have a table with headings products, date, diameter etc
starting from col A.

I would like in sheet BB to be able just to see data corresponding to a
certain product, call it Cars. The user enters Cars in B2 sheet BB and the
formula fills in the table in sheet BB with only the data for Cars from sheet
AA.

Is this possible without code?

Thanks

Filter formula
 

Spot on

Thanks a lot

"Pete_UK" wrote:

Yes, it is possible.

In a spare column in sheet AA (eg column M) put this formula on row 2:

=IF(A2="","",A2&"_"&COUNTIF(A$2:A2,A2))

and copy this down as far as you like (even beyond your data). I'm
assuming that your products are listed in column A, so adjust if
necessary.

Then in M3 of sheet BB you can have this formula:

=IF(B$2="","",IF(ISNA(MATCH(B$2&"_"&ROW(A1),'AA'!M :M,0)),"",MATCH(B
$2&"_"&ROW(A1),'AA'!M:M,0)))

and again copy this down as far as you might need it (it will show
blanks beyond where you have matching data, so it doesn't matter how
far you take it).

Then in A3 of sheet BB you can have this formula:

=IF($M3="","",INDEX('BB'!A:A,$M3))

Then you can copy this across for as many columns as you have, and
then format the cells containing dates appropriately. Then copy these
formulae from row 3 down as far as you need (or beyond).

Now, just change the entry in B2 and the display will change just like
a filter.

Hope this helps.

Pete


On Mar 26, 1:27 pm, LiAD wrote:
Hi,

I am looking for a formula that can act as a filter.

In sheet AA I have a table with headings products, date, diameter etc
starting from col A.

I would like in sheet BB to be able just to see data corresponding to a
certain product, call it Cars. The user enters Cars in B2 sheet BB and the
formula fills in the table in sheet BB with only the data for Cars from sheet
AA.

Is this possible without code?

Thanks

.

Filter formula
 

You're welcome - thanks for feeding back.

Pete

On Mar 26, 4:26*pm, LiAD wrote:
Spot on

Thanks a lot



"Pete_UK" wrote:
Yes, it is possible.

In a spare column in sheet AA (eg column M) put this formula on row 2:

=IF(A2="","",A2&"_"&COUNTIF(A$2:A2,A2))

and copy this down as far as you like (even beyond your data). I'm
assuming that your products are listed in column A, so adjust if
necessary.

Then in M3 of sheet BB you can have this formula:

=IF(B$2="","",IF(ISNA(MATCH(B$2&"_"&ROW(A1),'AA'!M :M,0)),"",MATCH(B
$2&"_"&ROW(A1),'AA'!M:M,0)))

and again copy this down as far as you might need it (it will show
blanks beyond where you have matching data, so it doesn't matter how
far you take it).

Then in A3 of sheet BB you can have this formula:

=IF($M3="","",INDEX('BB'!A:A,$M3))

Then you can copy this across for as many columns as you have, and
then format the cells containing dates appropriately. Then copy these
formulae from row 3 down as far as you need (or beyond).

Now, just change the entry in B2 and the display will change just like
a filter.

Hope this helps.

Pete

On Mar 26, 1:27 pm, LiAD wrote:
Hi,

I am looking for a formula that can act as a filter.

In sheet AA I have a table with headings products, date, diameter etc
starting from col A.

I would like in sheet BB to be able just to see data corresponding to a
certain product, call it Cars. *The user enters Cars in B2 sheet BB and the
formula fills in the table in sheet BB with only the data for Cars from sheet
AA.

Is this possible without code?

Thanks

.- Hide quoted text -

- Show quoted text -

Filter formula
 

Hi,

You may want to refer to question 7 on the following link -
http://ashishmathur.com/knowledgebaseII.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"LiAD" wrote in message
...
Hi,

I am looking for a formula that can act as a filter.

In sheet AA I have a table with headings products, date, diameter etc
starting from col A.

I would like in sheet BB to be able just to see data corresponding to a
certain product, call it Cars. The user enters Cars in B2 sheet BB and
the
formula fills in the table in sheet BB with only the data for Cars from
sheet
AA.

Is this possible without code?

Thanks