Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert rows based on specific value | Excel Worksheet Functions | |||
resetting last cell | Excel Discussion (Misc queries) | |||
copy rows meeting criteria to another worksheet | Excel Worksheet Functions | |||
Hiding rows based on choice in a listbox | Excel Discussion (Misc queries) | |||
Counting rows based on criteria in multiple cells | Excel Discussion (Misc queries) |