Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
It's a filter but not a filter
Over a year ago Harlan Grove solved a huge issue for me (see link for
details http://groups.google.com.au/group/mi... 7d2811f18bbc) well it's served me very well but I can't adapt it to achieve my current problem. Best explained by example: here's my data Column A Column B Values Column D Aus Bob 50 Include Aus Dave 23 Include Aus Dave 3345 US Eric 43 Include US Simon 525 Include US Simon 66 Include US Simon 73 US Simon 82 Spain Jim 6 Include Sing Bob 5 H Kong Tom 91 Include and I need to create a summary page that returns just the "Includes" for the countries I'm interested in. This would result in the following: Column A Column B Values Column D Aus Bob 50 Include Aus Dave 23 Include US Eric 43 Include US Simon 525 Include US Simon 66 Include H Kong Tom 91 Include note Spains exclusion because there's another table which shows my required countries: Column A Aus US Hong Kong Now obviously this is a simplified e.g. & in the real version the number of includes changes and the number of entrants for each country changes etc. I can't use filters as the user needs to be able to just paste data in one tab, and then my calculations present everything sumarised in another tab. Harlan's formula last time (obviously won't make complete sense in this context but at least it shows his method) is a follows: Enter the following formulas in Sheet2. A1: =Sheet1!A1 A2 [array formula]: =IF(SUMPRODUCT(COUNTIF(A$1:A1,Sheet1!A$1:A$10000)) <COUNTA(Sheet1!A$1:A$10000),INDEX(Sheet1!A$1:A$100 00,MATCH(0, COUNTIF(A$1:A1,Sheet1!A$1:A$10000),0)),"") Select A2 and fill down as needed, worst case into Sheet2!A3:A10000. Any help would be much appreciated. Thanks Matt |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
It's a filter but not a filter
Your needs are best met if you treat your source data as a database and
performa query against it. Nick Hodges has an excellent article at this site explaining how to pull data from an Access database, but you can just as easily substitute your Excel data as the source database. Follow his steps to provide parameters to the query (i.e., the countries you want analyzed) and then pull the data into another sheet for analysis. http://www.nickhodge.co.uk/gui/datam...taexamples.htm This is basically an Excel filter on steroids. " wrote: Over a year ago Harlan Grove solved a huge issue for me (see link for details http://groups.google.com.au/group/mi... 7d2811f18bbc) well it's served me very well but I can't adapt it to achieve my current problem. Best explained by example: here's my data Column A Column B Values Column D Aus Bob 50 Include Aus Dave 23 Include Aus Dave 3345 US Eric 43 Include US Simon 525 Include US Simon 66 Include US Simon 73 US Simon 82 Spain Jim 6 Include Sing Bob 5 H Kong Tom 91 Include and I need to create a summary page that returns just the "Includes" for the countries I'm interested in. This would result in the following: Column A Column B Values Column D Aus Bob 50 Include Aus Dave 23 Include US Eric 43 Include US Simon 525 Include US Simon 66 Include H Kong Tom 91 Include note Spains exclusion because there's another table which shows my required countries: Column A Aus US Hong Kong Now obviously this is a simplified e.g. & in the real version the number of includes changes and the number of entrants for each country changes etc. I can't use filters as the user needs to be able to just paste data in one tab, and then my calculations present everything sumarised in another tab. Harlan's formula last time (obviously won't make complete sense in this context but at least it shows his method) is a follows: Enter the following formulas in Sheet2. A1: =Sheet1!A1 A2 [array formula]: =IF(SUMPRODUCT(COUNTIF(A$1:A1,Sheet1!A$1:A$10000)) <COUNTA(Sheet1!A$1:A$10000),INDEX(Sheet1!A$1:A$100 00,MATCH(0, COUNTIF(A$1:A1,Sheet1!A$1:A$10000),0)),"") Select A2 and fill down as needed, worst case into Sheet2!A3:A10000. Any help would be much appreciated. Thanks Matt |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
It's a filter but not a filter
one way to do this is to use a hidden helper column A
in A1 on your summary sheet =if(and(Sheet1!D1="include",countif(Country!A;A,Sh eet1!a1)0),row(),"") in summary sheet B1 enter =if(count($A:$A)Row(),index(Sheet1!A:A,index(A:A, small($A:$A,Row()),"") copy B1 paste in B1 to E(as far down as you want) and hide column A If you are going to have headers for your date in the index section subtract the number of header rows from the Row() in the index function " wrote: Over a year ago Harlan Grove solved a huge issue for me (see link for details http://groups.google.com.au/group/mi... 7d2811f18bbc) well it's served me very well but I can't adapt it to achieve my current problem. Best explained by example: here's my data Column A Column B Values Column D Aus Bob 50 Include Aus Dave 23 Include Aus Dave 3345 US Eric 43 Include US Simon 525 Include US Simon 66 Include US Simon 73 US Simon 82 Spain Jim 6 Include Sing Bob 5 H Kong Tom 91 Include and I need to create a summary page that returns just the "Includes" for the countries I'm interested in. This would result in the following: Column A Column B Values Column D Aus Bob 50 Include Aus Dave 23 Include US Eric 43 Include US Simon 525 Include US Simon 66 Include H Kong Tom 91 Include note Spains exclusion because there's another table which shows my required countries: Column A Aus US Hong Kong Now obviously this is a simplified e.g. & in the real version the number of includes changes and the number of entrants for each country changes etc. I can't use filters as the user needs to be able to just paste data in one tab, and then my calculations present everything sumarised in another tab. Harlan's formula last time (obviously won't make complete sense in this context but at least it shows his method) is a follows: Enter the following formulas in Sheet2. A1: =Sheet1!A1 A2 [array formula]: =IF(SUMPRODUCT(COUNTIF(A$1:A1,Sheet1!A$1:A$10000)) <COUNTA(Sheet1!A$1:A$10000),INDEX(Sheet1!A$1:A$100 00,MATCH(0, COUNTIF(A$1:A1,Sheet1!A$1:A$10000),0)),"") Select A2 and fill down as needed, worst case into Sheet2!A3:A10000. Any help would be much appreciated. Thanks Matt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why won't advanced filter return filter results? | Excel Worksheet Functions | |||
How do I use advanced filter to filter for blank cells? | Excel Discussion (Misc queries) | |||
Excel auto filter doesn't recoginize case - won't filter AA from A | Excel Discussion (Misc queries) | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions | |||
advanced filter won't allow me to filter on bracketed text (-456.2 | Excel Discussion (Misc queries) |