To remove full duplicates: Data -> More filters -> Advanced filter -> Options -> No dublications. Then you need to select an array (for example, column A) and you're done. The selected array looks like this: $ Sheet1. $ A $ 1: $ A $ 5

And how to select records with partial matches automatically? For example from the list:

  • a01
  • b01
  • c01
  • d02
  • e03

How to automatically select records ending in "01"? If this is not possible, then how to select all the records with the number of matches 2,3,4,5, etc.?

I use Libre, but if there is no way - Excel as an option

    1 answer 1

    Although Libre Calc and Excel are similar, they are different programs and what Excel can do, the sister (or Calc - brother can not always? :)).

    Everything below works in Excel .

    -----------------------

    Option 1. Built-in AutoFilter tool.

    Set AutoFilter: Data-Filter tab.

    Next, select filtering by the required column, Text_ Filters -Ends_on-01

     -----------------------

    Option2. Autofilter + sheet functions.

    In the free column write the formula:

     =ПРАВСИМВ(A2;2)="01"

    If we are looking not only at the end of the text, but any partial coincidence:

     =ЕЧИСЛО(ПОИСК("01";A2)) =ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;"01";))<>0

    Filtering in this case in the column with the formula by the value TRUE

      -----------------------

    Option3. Sheet functions (without filtering).

     =ИНДЕКС($A$2:$A$6;НАИМЕНЬШИЙ(ЕСЛИ(ПРАВБ($A$2:$A$6;2)="01";СТРОКА(2:6)-1);СТРОКА(A1)))

    In the free range enter in the first cell. Enter as an array formula with three keys Ctrl + Shift + Enter . Stretch in the lines below.

    To get rid of errors in the range below the values ​​obtained, you can apply

     =ЕСЛИОШИБКА(формула;"все кончилось")

    For more flexibility in formulas, you can write a reference to the cell in which the desired is specified.

      -----------------------

    Option4. Using VBA.

    Alt + F11 - log in to the VBA editor

    Insert-Module tab - creating a common module.

    Expand the module and place the code there:

     Option Explicit ' поиск данных по частичному совпадению Sub FindData() Dim ArrData() Dim sStr As String Dim lRws As Long Dim i As Long, k As Long With ActiveSheet ' можно подставить имя любого листа- Worksheets("имя") sStr = .Range("C1").Value ' искомый текст ' последняя заполененная строка столбца А lRws = .Cells(.Rows.Count, "A").End(xlUp).Row ' считаем, что в строке 1 шапка таблицы If lRws < 2 Then Exit Sub ' данных нет ArrData = .Range("A1:A" & lRws).Value ' данные заносим в массив ' работа с объектами листа медленная, лучше обрабатывать в памяти k = 1: ArrData(1, 1) = "Найденное" For i = 2 To lRws ' цикл по данным If ArrData(i, 1) Like "*" & sStr Then ' если искомое в конце значениия ' If ArrData(i, 1) Like "*" & sStr & "*" Then ' если искомое содержится в значении k = k + 1 ' счетчик найденных значений ArrData(k, 1) = ArrData(i, 1) ' записываем значение End If Next i If k > 1 Then .Columns(4).ClearContents ' удаляем данные из столбца D .Range("D1").Resize(k, 1).Value = ArrData ' выгружаем найденное в столбец D End If End With End Sub

    On the sheet, you can create a button (for example, Insert-Shapes ), which assign the launch of the macro ( RMB-Assign_Macros ).

    After launch, the data in column A , in which the search is found, recorded in cell C1 , will be displayed in column D