Prev: Calculating percent of times a value is in a column and total #
Next: Using VLOOKUP to find colours
From: LiAD on 26 Mar 2010 10:14
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
From: LiAD on 26 Mar 2010 12:26
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 <L...(a)discussions.microsoft.com> 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
>
> .
>
From: Pete_UK on 26 Mar 2010 21:09
You're welcome - thanks for feeding back.

Pete

On Mar 26, 4:26 pm, LiAD <L...(a)discussions.microsoft.com> 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 <L...(a)discussions.microsoft.com> 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 -

From: Ashish Mathur on 26 Mar 2010 21:58
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" <LiAD(a)discussions.microsoft.com> wrote in message
news:FEEE2CB8-4B55-4459-8497-259695EB766F(a)microsoft.com...
> 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

First  |  Prev  | 
Pages: 1 2
Prev: Calculating percent of times a value is in a column and total #
Next: Using VLOOKUP to find colours