advanced filter in Excel

[Anne Nasser]

I'm wondering if anyone may have some suggestions on the best way to do
the following filter of specific lines from a very large excel dataset.

If I have a table with several field headings, for example:

year data type subgroup value
1981 labour force age 1 to 5 203
1982 census age 1 to 5 545
1981 population age 1 to 5 676
1983 labour force age 6 to 8 339
1983 census age 6 to 8 432
1983 population age 6 to 8 532
1985 labour force age 10 to 15 666

How could I arrange to filter out the lines which correspond to all of
the following criteria (not only one of the criteria):

1) year is 1981,1982 or 1985; and
2) data type is labour force or census; and
3) subgroup is: age 1 to 5, age 3 to 5, age 7 to 10, or age 10 to 15

Is it possible to to set up the criteria along the following lines and
do an advanced filter on it? (or is there an easier way or another way
to do this which is more advisable ie. VB macro):

year data type subgroup
1981 labour force age 1 to 5
1982 census age 3 to 5
1985 age 7 to 10
age 10 to 15

How could it be indicated that the data line must meet the criterion for
each of the fields (year, data type, subgroup) and not that it need meet
the criterion for only one of the fields. An example of datalines which
meets all the criterion is:

year data type subgroup value
1981 labour force age 1 to 5 203
1985 labour force age 10 to 15 666


Thank you, and I look forward to hearing from you.

Best regards,

Anne Nasser




*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Tom Ogilvy]

Think you would have to write all you combinations between the three columns
as separate rows in your criteria.

an alternative would be to use a calculated criteria where you use

=AND(or(A2=1981,A2=1982,A2=1983),or(B2="labor force",B2=census),or(C3="age 1
to 5", etc

 

[RB Smissaert]

Another option is to use SQL on sheet data.
It may look more complicated, but once you get the hang of it
you may find it much easier for things like this.

In this case it would work with the Sub as below.
You will need a workbook called SQLTester with the sheets:
TableSheet and ResultSheet and this has to be saved in:
C:\ExcelFiles\

You will have to set a reference in the VBE (Tools, References) to
a Microsoft ActiveX Data Objects library

Note that I changed the field name data type to datatype.

Sub runSQL()

Dim rsData As ADODB.Recordset
Dim szConnect As String
Dim szSQL As String

'Where "HDR=Yes" means that there is a header row in the cell range
'(or named range), so the provider will not include the first row of the
'selection into the recordset. If "HDR=No", then the provider will
include
'the first row of the cell range (or named ranged) into the recordset.

'--------------------------------------------------------------------------

' Create the connection string.
' note the single quotes here around Excel 8.0;HDR=Yes
'----------------------------------------------------
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\ExcelFiles\SQLTester.xls;" & _
"Extended Properties='Excel 8.0;HDR=Yes'"

' Query examples
'---------------
' Query based on the worksheet name.
' szSQL = "SELECT * FROM [Sales$]"
' Query based on a sheet-level range name.
'szSQL = "SELECT * FROM [Sales$SheetLevelName]"
' Query based on a specific range address.
'szSQL = "SELECT * FROM [Sales$A1:E89]"
' Query based on a book-level range name.
' szSQL = "SELECT * FROM BookLevelName"

szSQL = "SELECT * FROM [TableSheet$] " & _
"WHERE " & _
"year IN ('1981', '1982', '1985') AND " & _
"datatype IN ('labour force', 'census') AND " & _
"subgroup IN ('age 1 to 5', 'age 3 to 5', 'age 7 to 10', 'age 10
to 15')"

Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If Not rsData.EOF Then
Sheets("ResultSheet").Cells(1).CopyFromRecordset rsData
Else
MsgBox "No records returned.", vbCritical, ""
End If

' Clean up our Recordset object.
rsData.Close
Set rsData = Nothing

End Sub


Put this sub in the above workbook.
Put your data in the sheet TableSheet.
Run the Sub.
The result will appear in the sheet ResultSheet


RBS

 

[Mark E. Philpot]

I think a vba form would be easier.

you could have what ever you want and have the results put
onto a sheet or on the form or both.

Let me know if you want me to do it.

(e-mail address removed)

Regards
mark

visit:
http://au.geocities.com/excelmarksway

 

[Anne Nasser]

For Tom Ogilvy,

You had made a suggestion to apply a formula to run an advanced filter
in response to a message I posted in June (original message attached).
Thanks for the suggestion, I tried it out and it seems to work.

A similar question, I see how a formula would work if I have one set of
criterion. Is it possible to write a formula that could extract a
number of lines from a dataset, each with a different set of criterion?

For example, if the dataset is as follows:


year data type subgroup value 1981 labour
force age 1 to 5 203
1982 census age 1 to 5 545
1981 population age 1 to 5 676
1983 labour force age 6 to 8 339
1983 census age 6 to 8 432
1983 population age 6 to 8 532
1985 labour force age 10 to 15 666

Is there a way to write a formula that would extract the following lines
only?:

1982 census age 1 to 5 545
1983 census age 6 to 8 432
1985 labour force age 10 to 15 666

Could I write the three components:

=and(or(A2=1982),or(B2="census"),or(c3="age 1 to 5")
=and(or(A2=1983),or(B2="census"),or(c3="age 6 to 8")
=and(or(A2=1985),or(B2="labour force"),or(c3="age 10 to 15")

and combine them (say even about 100 of these components) in one long
formula in order to do a mass extraction of lines? Or do you suggest
another way, is there an easier way to do this?

Thank you again, and I look forward to hearing your suggestions.

Best regards,

Anne Nasser

Re: advanced filter in Excel
From: Tom Ogilvy

Think you would have to write all you combinations between the three
columns
as separate rows in your criteria.

an alternative would be to use a calculated criteria where you use

=AND(or(A2=1981,A2=1982,A2=1983),or(B2="labor
force",B2=census),or(C3="age 1
to 5", etc

--
Regards,
Tom Ogilvy




original post:

 

[Debra Dalgleish]

Set up the criteria area with headings that match the headings in the
dataset.
In the rows below, enter the criteria for the data you want to extract
-- each row in the criteria area represents an OR statement. For example:

year data type subgroup value
1982 census age 1 to 5 545
1983 census age 6 to 8 432
1985 labour force age 10 to 15 666

When you run the Advance Filter, select the criteria area headings, and
all the rows with criteria.

 

[Anne Nasser]

Debra,

Thanks for clarifying that on the advanced filter. It`s much easier
than creating a formula. Can this be done for a large number of
headings/categories across, say up to 10?

I`m also wondering if there is some way to mark data lines (for example,
with a color or some other marker) in the individual analysis
spreadsheet pages generated by doing "show pages" from a pivot table.
And then to be able to filter out/extract these data lines somehow to a
separate spreadsheet page in one shot or filter out the originating data
lines in the source data used to make the pivot table? Any suggestions
as to the best way to do this would be much appreciated.

Thank you, and I look forward to hearing from you.

Best regards,

Anne Nasser

 

[Debra Dalgleish]

You shouldn't have any problem with a large number of headings. The
headings should exactly match the headings in the data table, unless
you're using formulas in the criteria row.

To mark data lines, or rows in the source data, use a new column, and
mark with a character, such as "x". Then you can filter for that
character.

 

[Anne Nasser]

Debra,

By "marking" data lines, I was referring to the possibility of "marking"
data lines in the individual analysis pivot table spreadsheets generated
when you do a "show pages" from the original pivot table. For example,
from the main pivot table I have generated more than a hundred pivot
table analysis spreadsheets for different countries (each spreadsheet
page shows one country's data).

As all the analysis has been done in these individual spreadsheet pages
generated by the "show pages", is there a way to "mark" or identify
specific lines of data in each of the spreadsheets and be able extract
these lines from the whole set of spreadsheets in one shot. Ultimately,
I would like to be able to use this info to extract the original data
lines from the original source data used to make the pivot table in the
first place?

I hope I've been able to explain it clearly. How do you think would be
the best way to do this?

Thanks again, and looking forward to your suggestions.

Best regards,

Anne Nasser