STEP-BY-STEP OPERATION
- Please start with clicking on "ADA..." - "Source File"; and "Set Source File."...You will get the menu below, please click "CALIFORNIA" and "Open"...-
Screenshot 8:
Please click "ADA..." - "Source File..." - "Set File Layout Type..." and "Accept"... You have now selected the pre-defined Record Definition File "QryTxt.INI"... - Please click "ADA..." - "Build Query" and the "Parameter Lookup Menu" appears: Please select "CONTAINS" from the "Operator" Drop-Down Menu Please enter "san" in the "Value" input box and click "Add"... as shown below - Screenshot 9:
Your entry has been copied into the "Query-List-Box" as shown below... That means: Open the file "CALIFORNIA.txt" on my hard disk, scan all entries in column A for matches according to the criteria "san" and import all records found into my Microsoft Office Excel worksheet "RESULTS", starting at cell A1… Please click "Accept" and the menu closes - select "ADA..." on the Toolbar again and then "Get Data..." Screenshot 10:
Here are the results found... - Screenshot 11:
All records having the lookup criteria "san" anywhere in column A will be filtered from the database and imported... You may now either export these results to a Microsoft Office Word document file or write selected data to a Text-File... In order to export the results into Microsoft Office Word please press "Ctrl + W" and Microsoft Office Word opens up with the data shown… You may now format your document any way you like and print it out... If you decide to generate a text file, please select your text before you press "Ctrl +T"... You will then be asked to enter the full Path, Filename and Extension as shown below... - Screenshot 12:
In order to delete your filtered data, please press "Ctrl + D"... This step should be done prior to any filtering session...
Please observe the following rule: THE FILTERED DATA WILL ALWAYS BE IMPORTED INTO THE RANGE WHICH IS DESIGNATED AS THE "ACTIVE CELL", that means, if your active cell happens to be "F6" then all results will be written to an area starting with that position...
Example With A Combination Of The Logicals AND - OR
We will now show you where ADA outperforms Microsoft Office Excel's AutoFilter...
With Microsoft Office Excel's AutoFilter you may only filter on one column at the same time with ADA, there are no such restrictions...
ADA treats each row as one logical record which allows for more than one AND/OR combination
As an example, we use the following combined criteria for filtering:
DISTRICT LIKE '%san%' AND TOTAL-EXPENDITURES-II LIKE '%236%' OR TOTAL-REVENUE-II LIKE '%417%'
This means: Search column DISTRICT for records containing san AND search the same records additionally for entries containing 236 in column TOTAL-EXPENDITURES-II, OR search column TOTAL-REVENUE-II for entries containing 417 and import the data found into my worksheet RESULTS... - Screenshot 13:
...And Here Is How To Do It... - Please select from the Dropdown Box "Operator" the "CONTAINS" function
- Please enter "san" into the "Value" filed and click the Button "Add"
- The Query-List-Box shows DISTRICT LIKE '%san%'
- Please just click the Button "Add" next to the Dropdown-Box "Logical", for the logical "AND" is already shown
- Please select from the Dropdown Box "Column" the entry "TOTAL-EXPENDITURES-II"
- Please select from the Dropdown Box "Operator" the "CONTAINS" function
- Please enter "236" into the "Value" filed and click the Button "Add"
- Now the Query-List-Box shows: DISTRICT LIKE '%san%' AND TOTAL-EXPENDITURES-II LIKE '%236%'
- Please click "OR" in the Dropdown-Box "Logical" and click the Button "Add" next to it
- Please select from the Dropdown Box "Column" the entry "TOTAL-REVENU-II"
- Please select from the Dropdown Box "Operator" the "CONTAINS" function
- Please enter "417" into the "Value" filed and click the Button "Add"
- Now the Query-List-Box shows: DISTRICT LIKE '%san%' AND TOTAL-EXPENDITURES-II LIKE '%236%' OR TOTAL-REVENUE-II LIKE '%417%'
- Please click the Button "Accept" and your Menu closes
- As the last step, please click "ADA…" and "Get Data..."
...And here are your results:
Screenshot 14:
...Just Another Example With Four Criteria Including Three ANDs...
TOTAL-EXPENDITURES LIKE '%249%' AND TOTAL-REVENUE-II LIKE '%251%' AND NET-OTHER-I-O-II LIKE '%-17%' AND REVENUE-LOW LIKE '%251%' Screenshot15: The Filter Results:
...One More Example With Ten Criteria Including Nine ANDs...
RESULTS-FOUND LIKE '%custom%' AND RESULTS-FOUND LIKE '%assign%' AND RESULTS-FOUND LIKE '%tool%' AND RESULTS-FOUND LIKE '%eliminate%' AND RESULTS-FOUND LIKE '%print%' AND RESULTS-FOUND LIKE '%use%' AND RESULTS-FOUND LIKE '%delete%' AND RESULTS-FOUND LIKE '%layout%' AND RESULTS-FOUND LIKE '%option%' AND RESULTS-FOUND LIKE '%including%'
...Will Extract The Following Results:
Saving time with Custom Views Custom Views is a tool in Excel that lets you assign a name to a particular sheet layout so you can recall it for later viewing. You can access the feature by selecting View | Custom Views. In the Custom Views dialog box, you'll see a listing of saved views, and you can add or delete views. When adding a view, you have the option of including print settings, hidden rows and columns, and filter settings. The new view even remembers where the active cell was when the view was saved, so when you open that view, it will "jump" to the spot in your sheet that corresponds to that view. Making good use of custom views can save a great deal of time. For example, you can eliminate repetitive hiding, resizing, filtering, and other changes for producing various printed reports. Each set of options can be saved as a view. Then, all you need to do is apply the view before you print.
You may now try your own lookup combinations if you please...just remember to delete your previous results with "Ctrl + D" in order to get correct results...