Extracting rows based on fixed criteria

I have a workbook that includes 4 sheets based on quarters of the year and a summary page.

In A1:A500 is an alpha listing of company names - there may be duplicates.
In B1:B500 are the products supplied by the companies.

In H1:h500 yes/No is selected if there are quality concerns.
J1:J500 details the concern if Yes is selected in H1:H500.

I would like to be able to produce a (in the summary page) a summary of the the companies and products that have quality concerns. The same company may appear more than once, the product is unique.

Is it possible to achieve this without VB coding? I seem to remember a similar question sometime ago but have been unable to locate it.

Any assistance gratefully received

EdMac

On the second sheet, select all of your target cells for the company name
(probably A2:A500, A1 for the heading)and add this to the formula bar

=IF(ISERROR(SMALL(IF(Sheet1!$H$1:$H$500="Yes",ROW($A1:$A$500),""),ROW($A1:$A
$500))),"",
INDEX(Sheet1!A$1:A$500,SMALL(IF(Sheet1!$H$1:$H$500="Yes",ROW($A1:$A$500),"")
,ROW($A1:$A$500))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Copy the block formula acroos to column B to get the product.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"EdMac" <[email protected]> wrote in
message news:[email protected]...
>
> I have a workbook that includes 4 sheets based on quarters of the year
> and a summary page.
>
> In A1:A500 is an alpha listing of company names - there may be
> duplicates.
> In B1:B500 are the products supplied by the companies.
>
> In H1:h500 yes/No is selected if there are quality concerns.
> J1:J500 details the concern if Yes is selected in H1:H500.
>
> I would like to be able to produce a (in the summary page) a summary of
> the the companies and products that have quality concerns. The same
> company may appear more than once, the product is unique.
>
> Is it possible to achieve this without VB coding? I seem to remember a
> similar question sometime ago but have been unable to locate it.
>
> Any assistance gratefully received
>
> EdMac
>
>
> --
> EdMac
> ------------------------------------------------------------------------
> EdMac's Profile:
http://www.excelforum.com/member.php...o&userid=30736
> View this thread: http://www.excelforum.com/showthread...hreadid=558845
>

Take a look at Data==>Filter==>Autofilter.

Select columns, check Autofilter.

Filter column H for "yes" and copy/paste required data to your summary sheet.

HTH

"EdMac" wrote:

>
> I have a workbook that includes 4 sheets based on quarters of the year
> and a summary page.
>
> In A1:A500 is an alpha listing of company names - there may be
> duplicates.
> In B1:B500 are the products supplied by the companies.
>
> In H1:h500 yes/No is selected if there are quality concerns.
> J1:J500 details the concern if Yes is selected in H1:H500.
>
> I would like to be able to produce a (in the summary page) a summary of
> the the companies and products that have quality concerns. The same
> company may appear more than once, the product is unique.
>
> Is it possible to achieve this without VB coding? I seem to remember a
> similar question sometime ago but have been unable to locate it.
>
> Any assistance gratefully received
>
> EdMac
>
>
> --
> EdMac
> ------------------------------------------------------------------------
> EdMac's Profile: http://www.excelforum.com/member.php...o&userid=30736
> View this thread: http://www.excelforum.com/showthread...hreadid=558845
>
>

Thanks for the responses.

Bob - your's certainly cuts the mustard and is ideal - I will be using it, it's a great solution.

Toppers, thanks for your's, but I don't want to mess about with my source table and cut and paste out of it as it will be used by other users.

Edmac

Bob,
I tried your formula (as part of my ongoing education programme)
but I don't get the expected results so what (if anything is wrong). The data
below shows the results (left hand column) and the right hand side shows the
data on Sheet1. So for example, I would have expected to see Customer 14 in
the results list.

(Usual alignment problems but hopefully you can see how the data is arranged).

Results customer 10 YES
customer 10 customer 14 YES
customer 16 customer 16 No
customer 26 customer 23 YES
customer 30 customer 24 YES
customer 39 customer 26 No
customer 50 customer 3 No
customer 9 customer 30 No
customer 31 No
customer 32 No
customer 33 YES
customer 34 No
customer 35 No
customer 38 YES
customer 39 YES
customer 45 YES
customer 48 No
customer 49 No
customer 50 YES
customer 8 YES
customer 9 YES


"Bob Phillips" wrote:

> On the second sheet, select all of your target cells for the company name
> (probably A2:A500, A1 for the heading)and add this to the formula bar
>
> =IF(ISERROR(SMALL(IF(Sheet1!$H$1:$H$500="Yes",ROW($A1:$A$500),""),ROW($A1:$A
> $500))),"",
> INDEX(Sheet1!A$1:A$500,SMALL(IF(Sheet1!$H$1:$H$500="Yes",ROW($A1:$A$500),"")
> ,ROW($A1:$A$500))))
>
> which is an array formula, it should be committed with Ctrl-Shift-Enter, not
> just Enter.
>
> Copy the block formula acroos to column B to get the product.
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "EdMac" <[email protected]> wrote in
> message news:[email protected]...
> >
> > I have a workbook that includes 4 sheets based on quarters of the year
> > and a summary page.
> >
> > In A1:A500 is an alpha listing of company names - there may be
> > duplicates.
> > In B1:B500 are the products supplied by the companies.
> >
> > In H1:h500 yes/No is selected if there are quality concerns.
> > J1:J500 details the concern if Yes is selected in H1:H500.
> >
> > I would like to be able to produce a (in the summary page) a summary of
> > the the companies and products that have quality concerns. The same
> > company may appear more than once, the product is unique.
> >
> > Is it possible to achieve this without VB coding? I seem to remember a
> > similar question sometime ago but have been unable to locate it.
> >
> > Any assistance gratefully received
> >
> > EdMac
> >
> >
> > --
> > EdMac
> > ------------------------------------------------------------------------
> > EdMac's Profile:
> http://www.excelforum.com/member.php...o&userid=30736
> > View this thread: http://www.excelforum.com/showthread...hreadid=558845
> >
>
>
>

John,

You have entered it wrongly.

It is not your standard array formula, whereby you enter the formula in a
cell, CSE it, and then copy down, but rather a block array formula (as I
like to call it). Here you select all of the target range, type the formula
into the formula bar, and the CSE it. This then creates an array of the
results, as well as using source arrays. When you edit the formula, you have
to select the complete array and commit en-masse.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Toppers" <[email protected]> wrote in message
news:[email protected]...
> Bob,
> I tried your formula (as part of my ongoing education
programme)
> but I don't get the expected results so what (if anything is wrong). The
data
> below shows the results (left hand column) and the right hand side shows
the
> data on Sheet1. So for example, I would have expected to see Customer 14
in
> the results list.
>
> (Usual alignment problems but hopefully you can see how the data is
arranged).
>
> Results customer 10 YES
> customer 10 customer 14 YES
> customer 16 customer 16 No
> customer 26 customer 23 YES
> customer 30 customer 24 YES
> customer 39 customer 26 No
> customer 50 customer 3 No
> customer 9 customer 30 No
> customer 31 No
> customer 32 No
> customer 33 YES
> customer 34 No
> customer 35 No
> customer 38 YES
> customer 39 YES
> customer 45 YES
> customer 48 No
> customer 49 No
> customer 50 YES
> customer 8 YES
> customer 9 YES
>
>
> "Bob Phillips" wrote:
>
> > On the second sheet, select all of your target cells for the company
name
> > (probably A2:A500, A1 for the heading)and add this to the formula bar
> >
> >
=IF(ISERROR(SMALL(IF(Sheet1!$H$1:$H$500="Yes",ROW($A1:$A$500),""),ROW($A1:$A
> > $500))),"",
> >
INDEX(Sheet1!A$1:A$500,SMALL(IF(Sheet1!$H$1:$H$500="Yes",ROW($A1:$A$500),"")
> > ,ROW($A1:$A$500))))
> >
> > which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
> > just Enter.
> >
> > Copy the block formula acroos to column B to get the product.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "EdMac" <[email protected]> wrote in
> > message news:[email protected]...
> > >
> > > I have a workbook that includes 4 sheets based on quarters of the year
> > > and a summary page.
> > >
> > > In A1:A500 is an alpha listing of company names - there may be
> > > duplicates.
> > > In B1:B500 are the products supplied by the companies.
> > >
> > > In H1:h500 yes/No is selected if there are quality concerns.
> > > J1:J500 details the concern if Yes is selected in H1:H500.
> > >
> > > I would like to be able to produce a (in the summary page) a summary
of
> > > the the companies and products that have quality concerns. The same
> > > company may appear more than once, the product is unique.
> > >
> > > Is it possible to achieve this without VB coding? I seem to remember
a
> > > similar question sometime ago but have been unable to locate it.
> > >
> > > Any assistance gratefully received
> > >
> > > EdMac
> > >
> > >
> > > --
> > > EdMac
> >
> ------------------------------------------------------------------------
> > > EdMac's Profile:
> > http://www.excelforum.com/member.php...o&userid=30736
> > > View this thread:
http://www.excelforum.com/showthread...hreadid=558845
> > >
> >
> >
> >

Bob,
Thanks. If I had bothered to read your first posting more
closely, I wouldn't asked the question! Anyway, it's OK now so thanks again
for your patience!

"Bob Phillips" wrote:

> John,
>
> You have entered it wrongly.
>
> It is not your standard array formula, whereby you enter the formula in a
> cell, CSE it, and then copy down, but rather a block array formula (as I
> like to call it). Here you select all of the target range, type the formula
> into the formula bar, and the CSE it. This then creates an array of the
> results, as well as using source arrays. When you edit the formula, you have
> to select the complete array and commit en-masse.
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Toppers" <[email protected]> wrote in message
> news:[email protected]...
> > Bob,
> > I tried your formula (as part of my ongoing education
> programme)
> > but I don't get the expected results so what (if anything is wrong). The
> data
> > below shows the results (left hand column) and the right hand side shows
> the
> > data on Sheet1. So for example, I would have expected to see Customer 14
> in
> > the results list.
> >
> > (Usual alignment problems but hopefully you can see how the data is
> arranged).
> >
> > Results customer 10 YES
> > customer 10 customer 14 YES
> > customer 16 customer 16 No
> > customer 26 customer 23 YES
> > customer 30 customer 24 YES
> > customer 39 customer 26 No
> > customer 50 customer 3 No
> > customer 9 customer 30 No
> > customer 31 No
> > customer 32 No
> > customer 33 YES
> > customer 34 No
> > customer 35 No
> > customer 38 YES
> > customer 39 YES
> > customer 45 YES
> > customer 48 No
> > customer 49 No
> > customer 50 YES
> > customer 8 YES
> > customer 9 YES
> >
> >
> > "Bob Phillips" wrote:
> >
> > > On the second sheet, select all of your target cells for the company
> name
> > > (probably A2:A500, A1 for the heading)and add this to the formula bar
> > >
> > >
> =IF(ISERROR(SMALL(IF(Sheet1!$H$1:$H$500="Yes",ROW($A1:$A$500),""),ROW($A1:$A
> > > $500))),"",
> > >
> INDEX(Sheet1!A$1:A$500,SMALL(IF(Sheet1!$H$1:$H$500="Yes",ROW($A1:$A$500),"")
> > > ,ROW($A1:$A$500))))
> > >
> > > which is an array formula, it should be committed with Ctrl-Shift-Enter,
> not
> > > just Enter.
> > >
> > > Copy the block formula acroos to column B to get the product.
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace somewhere in email address with gmail if mailing direct)
> > >
> > > "EdMac" <[email protected]> wrote in
> > > message news:[email protected]...
> > > >
> > > > I have a workbook that includes 4 sheets based on quarters of the year
> > > > and a summary page.
> > > >
> > > > In A1:A500 is an alpha listing of company names - there may be
> > > > duplicates.
> > > > In B1:B500 are the products supplied by the companies.
> > > >
> > > > In H1:h500 yes/No is selected if there are quality concerns.
> > > > J1:J500 details the concern if Yes is selected in H1:H500.
> > > >
> > > > I would like to be able to produce a (in the summary page) a summary
> of
> > > > the the companies and products that have quality concerns. The same
> > > > company may appear more than once, the product is unique.
> > > >
> > > > Is it possible to achieve this without VB coding? I seem to remember
> a
> > > > similar question sometime ago but have been unable to locate it.
> > > >
> > > > Any assistance gratefully received
> > > >
> > > > EdMac
> > > >
> > > >
> > > > --
> > > > EdMac
> > >
> > ------------------------------------------------------------------------
> > > > EdMac's Profile:
> > > http://www.excelforum.com/member.php...o&userid=30736
> > > > View this thread:
> http://www.excelforum.com/showthread...hreadid=558845
> > > >
> > >
> > >
> > >
>
>
>