Query/Import Data
from Multiple External Data/Text/ASCII Files
This
feature is used to import data from multiple external text files. The external files can
be stored under the same main directory but different subdirectories. Once the import
criteria are setup, useful data can be imported directly into Excel table from hundreds of
files. Of course, all the external files to be imported should have similar data
arrangement, like output files from the same program.
The
import criteria can be setup using absolute line/column numbers within the files, or
relative line/column number comparing to a specific searchable keyword.
This
feature is very useful when user work on a sensitivity study and want to create a summary
table. Because all the output files are create from same program and thus have similar
data arrangement.
Query/Import Data
from Multiple Closed Excel Workbook Files
This
feature is used to extract useful information from multiple close Excel workbook files.
These workbook files can be stored under the same main directory but different
subdirectories. Once the import criteria are setup, desired data can be directly imported
into Excel table without opening those workbook files.
The
import criteria can be setup using absolute row/column numbers within a specific
worksheet.
This
feature is very useful when user have many workbook files saved/received from same
template/pre-formatted table and want to create a summary table.
Query/Import Data
from Remote Internet/Websites
This
feature is used to extract real-time valuable information from internet, such as daily
stock prices, product inventory and prices, etc.
The
import criteria can be setup using webpage URL address, keyword to search and the relative
position of the useful data to the keyword.
Query/Consolidate
Data from Multiple Worksheets
This
feature is used to consolidate information from multiple worksheets into one single
summary table.
The
consolidate criteria can be setup using source data range, keyword to search and the
relative position of the useful data to the keyword.
This
feature is very useful when user have many sheets with similar data arrangement/format.
Query/Conditional
Statistics on a Specific Range
This
feature is used to perform statistical analysis (Max, Min, Sum, Count, Average, etc.)
under multiple conditions and thus achieve MaxIF, AverageIF, etc. functionalities.
This
feature is a simplified database query and is very useful for query data table under
multiple conditions.
Operation/Conditional
Highlight on a Selected Range
This
feature is used to do operation on a selected range, such as insert/remove text,
insert/remove spaces, change cases, math calculation, add unit name, unit conversion,
replace strings, replace error with specified strings, replace formula with value, replace
name with address, etc.
This
feature can also used to highlight cells based on user specified criteria: highlight the
maximum value, minimum value, values with a range, difference between rows/columns, every
N rows/columns, cell with formula, cell with link to other worksheet/workbook, etc.
Lookup Value Based
on Select Range and Keyword
This
feature is used to lookup values based on specified keyword and the relative position of
the returning value to the keyword.
The lookup
criteria can be setup by specifying the range to search, keyword to search, and relative
position of the returning value to the keyword ¨C rows below/above the keyword and
columns to the right/left of keyword. The keyword can be a formula, like the maximum value
of the range, etc.
Extract and
Re-Arrange Record from a Table
This
feature is used to re-arrange the records in a table, such as extract one record at a time
for plotting chart or other process, re-organize the table as one-record-one row,
different record different row, etc. This is a very powerful feature for re-arranging
data.
Compare Same Range
of Two Different Sheets
This
feature is used to find the difference between two worksheets. The comparison can be
either on values or on formula.
Chart Operation
This
feature includes two handy chart operations: dynamically add label to a chart series (link
the label to a range) and link chart title to a range.
Assign Shortcut Keys
This
feature is used to assign shortcut keys to several most frequently used actions: paste
special/value, paste special/formula, paste special/format, paste special/transpose. For
example, user can assign ctrl+shift+v for paste special /value.
User-Defined
Functions
Function XLDSSearch
Search
a value in a range and returns a value based on the specified relative position.
Function
XLDSXtrValues
Parse
a text string based on the specified delimiters/separators which dividing the string into
an array of substrings, and then extract a substring from it.
Function XLDSRef
Create
a range reference based on provided top-left corner cells row and column number, row
height, column height and sheet's name.