It's a filter but not a filter

[matthew.clegg]

Over a year ago Harlan Grove solved a huge issue for me (see link for
details
http://groups.google.com.au/group/m...lnk=gst&q=clegg&rnum=5&hl=en#93577d2811f18bbc)

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$10000,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

 

[Guest]

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/datamenu/dataexamples/externaldataexamples.htm

This is basically an Excel filter on steroids.

 

[Guest]

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,Sheet1!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