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


--
EdMac
------------------------------------------------------------------------
EdMac's Profile: http://www.excelforum.com/member.php...o&userid=30736
View this thread: http://www.excelforum.com/showthread...hreadid=558845

Extracting rows based on fixed criteria
 

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" wrote in
message ...

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

Extracting rows based on fixed criteria
 

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

Extracting rows based on fixed criteria
 

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


--
EdMac
------------------------------------------------------------------------
EdMac's Profile: http://www.excelforum.com/member.php...o&userid=30736
View this thread: http://www.excelforum.com/showthread...hreadid=558845

Extracting rows based on fixed criteria
 

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" wrote in
message ...

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

Extracting rows based on fixed criteria
 

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" wrote in message
...
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" wrote in
message ...

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

Extracting rows based on fixed criteria
 

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" wrote in message
...
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" wrote in
message ...

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