Macro for selecting data from a table. Request for data selection (formulas) in MS EXCEL. Random sampling in Excel

How to get a list of unique (non-repeating) values?

Let's imagine a large list of different names, full names, personnel numbers, etc. And from this list it is necessary to leave a list of all the same names, but so that they do not repeat - i.e. remove all duplicate entries from this list. As it is otherwise called: create a list of unique elements, a list of non-repeating, no duplicates. There are several ways to do this: built-in Excel tools, built-in formulas and, finally, using code Visual Basic for Application(VBA) and pivot tables. In this article we will look at each of the options.

Using the built-in capabilities of Excel 2007 and higher
In Excel 2007 and 2010 this is easy to do - there is a special command called - . It is located on the tab Data subsection Working with data (Data tools)

How to use this command. Select a column (or several) with the data in which duplicate records need to be removed. Go to the tab Data -Remove Duplicates.

If you select one column, but next to it there are other columns with data (or at least one column), then Excel will offer a choice: expand the selection range with this column or leave the selection as is and delete data only in the selected range. It is important to remember that if you do not expand the range, the data will be changed in only one column, and the data in the adjacent column will remain without the slightest change.

A window with options for removing duplicates will appear.

Check the boxes next to the columns in which duplicates need to be removed and click OK. If the selected range also contains data headers, then it is better to set the flag My data contains headers to avoid accidentally deleting data in the table (if they suddenly completely coincide with the meaning in the title).

Method 1: Advanced filter
In the case of Excel 2003, everything is more complicated. There is no such tool as Remove duplicates. But there is such a wonderful tool as Advanced filter. In 2003 this tool can be found in Data -Filter -Advanced filter. The beauty of this method is that with its help you can not spoil the original data, but create a list in a different range. In 2007-2010 Excel, it is also there, but a little hidden. Located on the tab Data, group Sort & Filter - Additionally (Advanced)
How to use it: run the specified tool - a dialog box appears:

• Treatment: Choose Copy to another location.
• List range: Selecting a range with data (in our case this is A1:A51).
• Criteria range: in this case we leave it blank.
• Place the result in a range (Copy to): indicate the first cell for data output - any empty one (in the picture - E2).
• Put a tick Unique records only.
• Click OK.

Note: if you want to place the result on another sheet, then simply specifying another sheet will not work. You will be able to specify a cell on another sheet, but...Alas and ah...Excel will display a message that it cannot copy the data to other sheets. But this can also be circumvented, and quite simply. You just need to launch Advanced filter from the sheet on which we want to place the result. And as initial data we select data from any sheet - this is allowed.

You can also not put the result in other cells, but filter the data on the spot. The data will not be affected in any way - it will be normal data filtering.

To do this, you just need to select in the Processing item Filter the list, in-place.

Method 2: Formulas
This method is more difficult to understand for inexperienced users, but it creates a list of unique values ​​without changing the original data. Well, it is more dynamic: if you change the data in the source table, the result will change. Sometimes this is useful. I’ll try to explain with my fingers what’s what: let’s say your list of data is located in column A (A1:A51, where A1 is the heading). We will display the list in column C, starting from cell C2. The formula in C2 will be as follows:
(=INDEX($A$2:$A$51,SMALL(IF(COUNTIF($C$1:C1, $A$2:$A$51)=0,ROW($A$1:$A$50)),1)) )
(=INDEX($A$2:$A$51 ;SMALL(IF(COUNTIF($C$1:C1 ; $A$2:$A$51)=0;ROW($A$1:$A$50));1)) )
A detailed analysis of how this formula works is given in the article:
It should be noted that this formula is an array formula. This can be indicated by the curly brackets in which this formula is enclosed. And this formula is entered into a cell using the keyboard shortcut - Ctrl+Shift+Enter. Once we have entered this formula into C2, we must copy and paste it across several lines so that all unique elements are accurately displayed. Once the formula in the lower cells returns #NUMBER!- this means all the elements are displayed and there is no point in extending the formula below. To avoid errors and make the formula more universal (without dragging it out every time until an error appears), you can use a simple check:
for Excel 2007 and higher:
(=IFERROR(INDEX($A$2:$A$51 ;SMALL(IF(COUNT($C$1:C1 ; $A$2:$A$51)=0;ROW($A$1:$A$50));1 ));""))
(=IFERROR(INDEX($A$2:$A$51 ;SMALL(IF(COUNTIF($C$1:C1 ; $A$2:$A$51)=0;ROW($A$1:$A$50));1 ));""))
for Excel 2003:
(=IF(EOSH(SMALL(IF(COUNTIF($C$1:C1, $A$2:$A$51)=0,ROW($A$1:$A$50)),1));"";INDEX( $A$2:$A$51 ;SMALL(IF(COUNTIF($C$1:C1, $A$2:$A$51)=0,ROW($A$1:$A$50)),1))))
(=IF(ISERR(SMALL(IF(COUNTIF($C$1:C1 ; $A$2:$A$51)=0;ROW($A$1:$A$50));1));"";INDEX( $A$2:$A$51 ;SMALL(IF(COUNTIF($C$1:C1 ; $A$2:$A$51)=0;ROW($A$1:$A$50));1))))
Then instead of an error #NUMBER! (#NUM!) you will have empty cells (not completely empty, of course - with formulas :-)).

You can read a little more about the differences and nuances of the IFERROR and IF(EOSH) formulas in this article: How to show 0 instead of an error in a cell with a formula

Method 3: VBA code
This approach will require macro permissions and basic knowledge of working with them. If you are not confident in your knowledge, I recommend reading these articles first:

• What is a macro and where to find it? a video tutorial is attached to the article
• What is a module? What types of modules are there? will be needed to understand where to insert the codes below

Both codes below should be placed in standard module. Macros must be allowed.

We will leave the original data in the same order - the list with the data is located in column "A" (A1:A51, where A1 is the title). Only we will display the list not in column C, but in column E, starting from cell E2:

Sub Extract_Unique() Dim vItem, avArr, li As Long ReDim avArr(1 To Rows.Count, 1 To 1) With New Collection On Error Resume Next For Each vItem In Range("A2", Cells(Rows.Count, 1) .End(xlUp)).Value "Cells(Rows.Count, 1).End(xlUp) – determines the last filled cell in column A. Add vItem, CStr(vItem) If Err = 0 Then li = li + 1: avArr(li, 1) = vItem Else: Err.Clear End If Next End With If li Then .Resize(li).Value = avArr End Sub

Using this code, you can extract unique ones not only from one column, but also from any range of columns and rows. If instead of a line
Range("A2", Cells(Rows.Count, 1).End(xlUp)).Value !}
specify Selection.Value , then the result of the code will be a list of unique elements from the range selected on the active sheet. Only then would it be nice to change the value output cell - instead of put the one in which there is no data.
You can also specify a specific range:

Range("C2", Cells(Rows.Count, 3).End(xlUp)).Value

Universal code for selecting unique values
The code below can be used for any ranges. All you need to do is run it, specify a range with values ​​to select only non-repeating ones (selecting more than one column is allowed) and a cell to display the result. The specified cells will be scanned, only unique values ​​will be selected from them (empty cells are skipped) and the resulting list will be written starting from the specified cell.

Sub Extract_Unique() Dim x, avArr, li As Long Dim avVals Dim rVals As Range, rResultCell As Range On Error Resume Next "request cell addresses to select unique values ​​Set rVals = Application.InputBox("Specify the range of cells to select unique values" , "Request for data", "A2:A51", Type:=8) If rVals Is Nothing Then "if the Cancel button is pressed Exit Sub End If "if only one cell is specified, there is no point in selecting If rVals.Count = 1 Then MsgBox " To select unique values, you need to specify more than one cell", vbInformation, "www.site" Exit Sub End If "cut off empty rows and columns outside the working range Set rVals = Intersect(rVals, rVals.Parent.UsedRange) "if only empty cells are specified out of working range If rVals Is Nothing Then MsgBox "Insufficient data to select values", vbInformation, "www..Value"requesting a cell to display the result Set rResultCell = Application.InputBox("Укажите ячейку для вставки отобранных уникальных значений", "Запрос данных", "E2", Type:=8) If rResultCell Is Nothing Then "если нажата кнопка Отмена Exit Sub End If "определяем максимально возможную размерность массива для результата ReDim avArr(1 To Rows.Count, 1 To 1) "при помощи объекта Коллекции(Collection) "отбираем только уникальные записи, "т.к. Коллекции не могут содержать повторяющиеся значения With New Collection On Error Resume Next For Each x In avVals If Len(CStr(x)) Then "пропускаем пустые ячейки.Add x, CStr(x) "если добавляемый элемент уже есть в Коллекции - возникнет ошибка "если же ошибки нет - такое значение еще не внесено, "добавляем в результирующий массив If Err = 0 Then li = li + 1 avArr(li, 1) = x Else "обязательно очищаем объект Ошибки Err.Clear End If End If Next End With "записываем результат на лист, начиная с указанной ячейки If li Then rResultCell.Cells(1, 1).Resize(li).Value = avArr End Sub!}

Method 4: Pivot Tables
A somewhat non-standard way to extract unique values.

• Select one or more columns in the table and go to the tab Insert-group Table -PivotTable
• In the dialog box Create PivotTable check that the data range has been allocated correctly (or install a new data source)
• indicate the location of the Pivot Table:
  • To a new sheet (New Worksheet)
  • To an existing sheet (Existing Worksheet)
• confirm creation by pressing the button OK

Because Pivot tables, when processing data that is placed in the area of ​​rows or columns, select only unique values ​​from them for subsequent analysis, then absolutely nothing is required from us except create a pivot table and place the data of the desired column in the area of ​​rows or columns.
Using the file attached to the article as an example:

What is the inconvenience of working with pivots in this case: when there is a change in the source data, the pivot table will have to be updated manually: Select any cell of the pivot table - Right mouse button - Refresh or tab Data -Refresh all -Refresh. And if the source data is updated dynamically, and even worse, you will need to re-specify the range of the source data. And one more minus - the data inside the pivot table cannot be changed. Therefore, if you need to work with the resulting list in the future, then after creating the desired list using the summary list, it must be copied and pasted onto the desired sheet.

To better understand all the actions and learn how to handle pivot tables, I strongly recommend that you read the article General information about pivot tables - it is accompanied by a video tutorial in which I clearly demonstrate the simplicity and ease of working with the basic capabilities of pivot tables.

In the attached example, in addition to the described techniques, a slightly more complex variation of extracting unique elements using a formula and code is written, namely: extracting unique elements by criterion. What we are talking about: if one column contains the surname, and the second (B) contains some data (in the file these are months) and you need to extract the unique values ​​of column B only for the selected surname. Examples of such unique extracts are located on the sheet Retrieval by criterion.

Download example:

(108.0 KiB, 14,152 downloads)

To do this, you need to open the Visual Basic Editor (key combination “Alt+F11” or right-click on the shortcut of any sheet and select “Source Text” or in the “Code” group of the “Developer” menu tab, click on the “Visual Basic” item) ) and insert a standard module into the project. And add two program codes to this module (see Figure 8.) - and .

How to optimize the selection of multiple search values ​​from different tables in Excel

When preparing reports on amounts and payment terms for several individual suppliers, it is often necessary to sift through a significant amount of data (tables) before finding the information you are looking for.

You can optimize the selection of several desired values ​​(payment amounts for different suppliers) according to one parameter (for example, by date) from a small number of tables using almost the same scheme as with the only difference that the “Book Table of Contents” sheet will display a list of the names of the tables participating in the selection and its results, and in addition, a slightly different table search formula will be used:

“=VLOOKUP($C$1,INDIRECT(A2),2,FALSE)”, where:

• cell C1 (quarter number) – sets the parameter value;
• “INDIRECT(A2)” – defines a text link to a named range, the name of which is in cell A2;
• “2” is the column number of the source tables of suppliers, which contain the payment amounts we need;
• “FALSE” (can be replaced by 0) – indicates to the VLOOKUP function that an exact match is required.

Method one: Using an advanced autofilter

On an Excel sheet, you need to select an area among the data that you want to select. In the “Home” tab, click “Sorting and Filter” (located in the “Editing” settings block). Next, click on the filter.

You can do it differently: after selecting the area, go to the “Data” tab and click on “Filter”, located in the “Sorting and Filter” group.

When these steps are completed, icons should appear in the table header to start filtering. They will be displayed point down as small triangles on the right edge of the cells. Click on this icon at the beginning of the column for which you are going to make a selection. Launch the menu, go to “Text filters” and select “Custom filter...”.

The Custom Filtering window should now be activated. In it you set the restriction by which the selection will be carried out. You can choose one of five proposed types of conditions: equal, not equal, greater than, greater than, or equal to, less than.

After filtering, only those lines remain in which the amount of revenue exceeds 10,000 (as an example).

In the same column you can add a second condition. You need to return to the custom filtering window again, and set a different selection limit in its lower part. Set the switch to the “Less” position, and enter “15000” in the field on the right.

The table will only contain those rows in which the amount of revenue is not less than 10,000, but not more than 15,000.

In other columns, the selection is configured in a similar way. In the desired column, click on the filter icon, and then sequentially click on the list items “Filter by date” and “Custom filter”.

The Custom AutoFilter window should open. For example, perform a selection of results in the table from May 4 to May 6, 2016, inclusive. Click “After or equal to”, and in the field on the right enter the value “05/04/2016”. In the lower block, put the switch in the “Before or equal” position, and in the right field enter “05/06/2016”. Leave the condition compatibility switch in the default position, that is, “AND”. To apply filtering, click OK.

The list should now be reduced even more, because only rows will remain in which the amount of revenue varies from 10,000 to 15,000, and this is for the period from 05/04 to 05/06/2016 inclusive.

In one of the columns, you can reset the filtering if desired. For example, you can do this for revenue values. Click on the autofilter icon in the corresponding column. Select "Remove filter".

Selection by revenue amount will be disabled and only selection by dates will remain (from 05/04/2016 to 05/06/2016).

There is another column in the table called “Name”. It contains data in text format. Using these values, you can also form a sample. In the column name, click on the filter icon. Go to “Text Filters” and then “Custom Filter...”.

The custom filter window will open again, in which you can make a selection, for example, by the names “Meat” and “Potatoes”. In the first block you need to set the switch to the “Equal” position and enter “Potatoes” in the field to the right of it. Set the switch of the lower block to the “Equal” position, and in the field opposite - “Meat”. Now you should set the condition compatibility switch to the “OR” position. Click OK.

In the new sample, restrictions are set by date (from 05/04/2016 to 05/06/2016) and by name (Meat and Potatoes). There are no restrictions only on the amount of revenue.

You can remove the filter completely and this is done using the same methods that were used to set it. To reset filtering, in the “Data” tab, click on “Filter” in the “Sorting and Filter” group.

In the second option, you can go to the “Home” tab and click on “Sorting and Filter” in “Editing”. Next, click on “Filter”.

If you use any of these methods, the table will be deleted and the selection results will be cleared. That is, the table will display all the data previously entered into it.

Method two: Using an array formula

On the same Excel sheet, create an empty table with the same column names in the header that are in the source.

All empty cells must be selected in the first column of the new table. Place the cursor in the formula bar to enter the formula - =INDEX(A2:A29,SMALL(IF(15000

To apply the formula, press the Ctrl+Shift+Enter keys.

Select the second column with dates and place the cursor in the formula bar to enter - =INDEX(B2:B29,SMALL(IF(15000

In the same way, enter the following formula in the revenue column - =INDEX(C2:C29,SMALL(IF(15000

A formatting window will open in which you need to select the “Number” tab. In "Number Formats" select "Date". In the right part of the window, if desired, you can select the type of date displayed, and when all the settings have been made, click on OK.

Now everything will be beautiful, and the date will be displayed correctly. If the cells display the value “#NUMBER!”, then you need to apply conditional formatting. All table cells should be selected (except for the header) and, being in the “Home” tab, click on “Conditional Formatting” (in the “Styles” tool block). A list will appear in which you should select “Create a rule...”.

Select the rules “Format only cells that contain”, and in the first field, located under the line “Format only cells for which the following condition is met”, select “Errors” and click “Format...”.

A formatting window will open, in which go to “Font” and select white color. Click on OK.

A ready-made sample according to the specified limitation will appear in front of you, and it will all be in a separate table.

Third method: Sampling by several conditions using a formula

Boundary conditions for the sample should be entered in a separate column.

Select the empty columns of the new table one by one to enter the required three formulas into them. In the first column enter - =INDEX(A2:A29,SMALL(IF(($D$2=C2:C29),ROW(C2:C29);"");ROW(C2:C29)-ROW($C$1) )-ROW($C$1)). Next, enter the same formulas into the columns, only change the coordinates after the name of the INDEX operator to those that are needed and correspond to certain columns. Everything is similar to the previous method. Every time you enter, do not forget to press the key combination Ctrl+Shift+Enter.

If you need to change the sampling boundaries, you can simply change the boundary numbers in the conditions column and then the selection result will be automatically changed.

Fourth method: Random sampling

On the left side of the table you need to skip one column, and in the cell of the next one enter the formula - =RAND() to display a random number. To activate it, press ENTER.

If you need to make a whole column of random numbers, then place the cursor in the lower right corner of the cell containing the formula. A fill marker should appear, which should be dragged down by holding down the left mouse button. This is done parallel to the table with the data and to the end.

The range of cells will contain the RAND formula, but you don't need to work with pure values. Copy to the empty column on the right and select a range of cells with random numbers. In the “Home” tab, click on “Copy”.

Select an empty column and right-click to bring up the context menu. In the “Insert Options” tool group, select “Values” (shown as an icon with numbers).

In the “Home” tab, click on “Sorting and Filter”, and then “Custom Sorting”.

Next to the “My data contains headers” option, check the box. In the “Sort by” line, indicate the name of the column in which the copied random number values ​​are located. In the “Sorting” line, the settings remain at their defaults. In the “Order” line, select the “Ascending” or “Descending” option. Click on OK.

The table values ​​must be arranged in ascending or descending order of random numbers. Any number of first rows from the table can be taken and considered the result of a random sample.

If you work with large tables, you will definitely find duplicate amounts scattered along an entire column. At the same time, you may need to select data from a table with the first smallest numeric value, which has its own duplicates. We need automatic data sampling based on condition. In Excel, you can successfully use an array formula for this purpose.

How to make a selection in Excel by condition

To determine the corresponding value for the first smallest number, a sample from the table is needed according to the condition. Let’s say we want to find out the first cheapest product on the market from a given price list:

Automatic sampling is implemented for us by a formula that will have the following structure:

INDEX(data_range_for_sampling, MIN(IF(range=MIN(range),ROW(range)-ROW(column_header);””)))

In the place “data_range_for_sampling” you should specify the range of values ​​A6:A18 for sampling from a table (for example, text), from which the INDEX function will select one resulting value. The range argument refers to the area of ​​cells with numeric values ​​from which the first smallest number should be selected. In the “column_header” argument for the second ROW function, you should specify a reference to a cell with a column header that contains a range of numeric values.

Naturally, this formula should be executed in an array. Therefore, to confirm its entry, you should press not just the Enter key, but the entire key combination CTRL+SHIFT+Enter. If everything is done correctly, curly braces will appear in the formula bar.

Pay attention to the figure below, where this formula was entered into cell B3 in the array:

Sampling the corresponding value with the smallest number first:

With this formula, we were able to select the minimum value relative to the numbers. Next, we will analyze the principle of operation of the formula and step by step analyze the entire order of all calculations.

How conditional sampling works

The INDEX function plays a key role here. Its nominal task is to select from the source table (indicated in the first argument - A6:A18) values ​​corresponding to certain numbers. INDEX works taking into account the criteria defined in the second (row number within the table) and third (column number in the table) arguments. Since our source table A6:A18 has only 1 column, we do not specify the third argument in the INDEX function.

To calculate the table row number opposite the smallest number in the adjacent range B6:B18 and use it as the value for the second argument, several calculation functions are used.

The IF function allows you to select a value from a list based on a condition. Its first argument specifies where each cell in the range B6:B18 is checked for the smallest numeric value: IFB6:B18=MINB6:B18. In this way, an array of logical values ​​TRUE and FALSE is created in the program memory. In our case, 3 array elements will contain the value TRUE, since the minimum value of 8 contains 2 more duplicates in column B6:B18.

The next step is to determine which rows of the range contain each minimum value. We need this because the first smallest value is determined. This task is implemented using the ROW function; it fills the elements of the array in the program memory with the line numbers of the sheet. But first, from all these numbers, the number on opposite the first row of the table is subtracted - B5, that is, the number 5. This is done because the INDEX function works with numbers inside the table, and not with numbers in the Excel worksheet. At the same time, the ROW function can only return sheet line numbers. To avoid a shift, it is necessary to compare the order of the row numbers of the sheet and table by subtracting the difference. For example, if the table is on the 5th row of the sheet, then each row of the table will be 5 less than the corresponding row of the sheet.

After all the minimum values ​​have been selected and all row numbers in the table have been compared, the MIN function will select the smallest row number. This same row will contain the first smallest number that appears in column B6:B18. Based on this row number, the INDEX function will select the corresponding value from table A6:A18. As a result, the formula returns this value to cell B3 as the result of the calculation.

How to select the value with the largest number in Excel

Having understood the principle of the formula, you can now easily modify it and adjust it to other conditions. For example, the formula can be modified to select the first maximum value in Excel:

If you need to change the formula conditions so that you can select the first maximum in Excel, but less than 70:

How to select the first minimum value other than zero in Excel:

As is easy to see, these formulas differ from each other only in the MIN and MAX functions and their arguments.

Now nothing limits you. Once you understand the principles of operation of formulas in an array, you can easily modify them to suit many conditions and quickly solve many computational problems.

Using Excel tools, you can select certain data from a range in random order, according to one or several conditions. To solve such problems, as a rule, array formulas or macros are used. Let's look at examples.

How to make a selection in Excel by condition

When using array formulas, the selected data is shown in a separate table. This is the advantage of this method compared to a conventional filter.

Source table:

First, let's learn how to make a sample using one numerical criterion. The task is to select from the table products with a price above 200 rubles. One way to solve this is to use filtering. As a result, only those products that satisfy the request will remain in the source table.

Another solution is to use an array formula. The rows corresponding to the query will be placed in a separate table report.

First, we create an empty table next to the original one: we duplicate the headers, the number of rows and columns. The new table occupies the range E1:G10. Now select E2:E10 (the “Date” column) and enter the following formula: ( }.

To get an array formula, press the key combination Ctrl + Shift + Enter. In the adjacent column - “Product” - enter a similar array formula: ( ). Only the first argument of the INDEX function has changed.

In the “Price” column we enter the same array formula, changing the first argument of the INDEX function.

As a result, we get a report on goods with a price of more than 200 rubles.

This selection is dynamic: when the query changes or new products appear in the source table, the report will automatically change.

Task No. 2 – select from the original table products that went on sale on September 20, 2015. That is, the selection criterion is date. For convenience, we enter the desired date in a separate cell, I2.

To solve the problem, a similar array formula is used. Only instead of criterion).

Similar formulas are entered in other columns (see principle above).

Now we use the text criterion. Instead of the date in cell I2, enter the text “Product 1”. Let's change the array formula a little: ( }.

Such a large selection function in Excel.

Selection by multiple conditions in Excel

First, let's take two numerical criteria:

The task is to select products that cost less than 400 and more than 200 rubles. We combine the conditions with the sign “*”. The array formula looks like this: ( }.!}

This is for the first column of the report table. For the second and third, we change the first argument of the INDEX function. Result:

To make a selection based on several dates or numerical criteria, we use similar array formulas.

Random sampling in Excel

When a user is working with a large amount of data, random sampling may be required for subsequent analysis. Each row can be assigned a random number and then sorted for selection.

Original dataset:

First, insert two empty columns on the left. In cell A2 we enter the formula RAND(). Let's multiply it by the entire column:

Now we copy the column with random numbers and paste it into column B. This is necessary so that these numbers do not change when new data is entered into the document.

To insert values ​​rather than a formula, right-click on column B and select the Paste Special tool. In the window that opens, put a checkmark next to the “Values” item:

You can now sort the data in column B in ascending or descending order. The order in which the original values ​​are presented will also change. We select any number of rows from above or below - we get a random sample.