the Essence of the query is to select from the original table the rows that meet certain criteria (like use of standard Filter). will Make the selection of values from the source table using the array formulas. Unlike Filter (CTRL+SHIFT+L or Data/ Sort & filter/ Filter), selected rows will be placed in a separate table.
Lists in MS EXCEL
The list of Microsoft Excel provide a single-column range of cells containing values related to the same topic. Lists you can make a drop-down (drop-down).
this section discusses tools to help you manage and analyze groups of related data (lists) on the Excel worksheet. If you declare a range of cells a list, the data of this list to manage and analyze them independently from data that are not included in the list. For example, using only the data from the list, you can filter rows, add the totals row.
On the worksheet, you can create multiple lists of data that allows the flexibility to divide data into separate well-managed sets. When creating a list in Excel 2007, Excel 2010, the process of recognition and change the contents of a list can be simplified at the expense of the list and the visual elements designed to enhance these funds.
Advantages of lists
- Sorting and filtering lists.
the Lists can be sorted ascending, descending or in a user specified order. You can also filter the lists to display only data that matches specified criteria.
For lists, you can use the built in Excel validation of data. For example, in a list column you can allow to use only numbers or dates.
- Formatting objects in the list.
in the list cells, you can apply Conditional formatting rules, in the same way as for regular worksheet cells.
Compare two tables with almost same structure. Table different values in individual rows, some of the names of the rows found in one table, but another may be missing.
When filling the cell with data, it is often necessary to restrict the option of entering a specific list of values. For example, there is a cell where the user must enter the name of the Department, pointing out where he works. Logical, first create a list of departments in an organization and allow the user only to select values from this list. This approach will help to speed up the entry process and reduce the number of misprints.
Having a list with duplicate values, create the list containing only unique values. When adding new values in the source list, the list of unique values should be automatically updated.