Игнорировать дубликаты и создать новый список уникальных значений в Excel

Question

У меня есть столбец значений, которые часто отображаются как дубликаты. Мне нужно создать новый столбец с уникальными значениями, основанными на первом столбце, следующим образом:

Column A   Column B  
a          a
a          b
b          c
c
c

Этот столбец B фактически должен появиться на другом листе, в той же книге, поэтому я предполагаю, что он должен работать с sheet2!A1 формат стиля.

Мне не повезло с опциями меню Data/Filter, так как кажется, что это работает только по команде. Мне нужно, чтобы столбец B автоматически обновлялся при вводе нового значения в столбец A.

Answer 1

Тотеро ответ правильный. Ссылка также очень полезна.

В основном формула, которая вам нужна:

B2=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0))

Затем нажмите Ctrl+Shift+Enter (или это не будет работать, используя формулу массива).

Здесь нужно помнить две важные вещи: полный список находится в ячейках A2:A20, тогда эта формула должна быть вставлена ​​в ячейку B2 (Не B1 как это даст вам круговую ссылку). Во-вторых, это формула массива, поэтому вам нужно нажать Ctrl+Shift+Enter, иначе она не будет работать правильно.

Answer 2

Здесь есть хорошее руководство, как это сделать.

В основном что-то похожее на:

=INDEX(Sheet1!$A$1:$A$20, MATCH(0, COUNTIF($B$1:B1,Sheet!$A$1:$A$20), 0))

Answer 3

В моем случае Excel был заморожен при использовании формулы

B2= ИНДЕКС ($A$2:$A$20, МАТЧ (0, COUNTIF($B$1:B1, $A$2:$A$20), 0))

потому что было много строк (10000). Таким образом, я сделал по-другому, что я покажу ниже.

Я скопировал свой исходный список во второй столбец, а затем с помощью функции Excel "удалить дубликаты" я смог найти список уникальных значений.

Скопировано с веб-сайта Microsoft Office:

Select all the rows, including the column headers, in the list 

Вы хотите фильтровать.

Нажмите верхнюю левую ячейку диапазона, а затем перетащите в нижнюю правую ячейку.

On the Data menu, point to Filter, and then click Advanced Filter.
In the Advanced Filter dialog box, click Filter the list, in place.
Select the Unique records only check box, and then click OK.

Отфильтрованный список отображается, а повторяющиеся строки скрыты.

On the Edit menu, click Office Clipboard.

Отобразится область задач буфера обмена.

Make sure the filtered list is still selected, and then click Copy Copy button.

Отфильтрованный список выделяется ограничивающими контурами, а выделение отображается в виде элемента> > в верхней части буфера обмена.

On the Data menu, point to Filter, and then click Show All.

Исходный список отображается повторно.

Press the DELETE key.

Исходный список удален.

In the Clipboard, click on the filtered list item.

Отфильтрованный список появляется в том же месте, что и исходный список.

Источник: веб-сайт Microsoft Office (ссылка удалена, причина смерти)

Answer 4

Удалить дубликаты из столбца

1. Сортировать значения в столбце A A->Z
2. Выберите столбец B

3. Пока столбец B все еще выбран, в поле ввода формулы введите

   =IF(TRIM(A1)=TRIM(A2),"",TRIM(A1))
   

4. Пока столбец B все еще выбран, выберите "Правка" -> "Заливка" -> "Вниз" (в более новых версиях просто выберите ячейку B1 и потяните вниз внешнее поле, чтобы развернуть его до конца в столбце).

Примечание: если столбец B находится на другом листе, вы можете использовать Sheet1!A1 и Sheet1!A2.

Answer 5

В отсортированном столбце вы также можете попробовать эту идею:

B2=A2
B3=IFERROR(INDEX(A:A,MATCH(B2,A:A,1)+1),"")

B3 можно приклеить. Это приведет к 0, после последнего уникального матча. Если это нежелательно, поместите какое-нибудь утверждение IF, чтобы исключить это.

Редактировать:

Проще, чем оператор IF, по крайней мере для текстовых значений:

B3=IFERROR(T(INDEX(A:A,MATCH(B2,A:A,1)+1)),"")

Answer 6

=SORT(UNIQUE(A:A))

Приведенная выше формула работает лучше всего, если вы хотите перечислить уникальные значения в столбце.

Answer 7

У меня есть список имен цветов в диапазоне A2:A8, в столбце B I нужно извлечь отдельный список имен цветов.

Выполните следующие шаги:

• Выберите ячейку B2; написать формулу, чтобы получить уникальные значения из списка.
• =IF(COUNTIF(A$2:A2,A2)=1,A2,””)
• Нажмите Enter на клавиатуре.
• Функция вернет имя первого цвета.
• Чтобы вернуть значение для остальных ячеек, скопируйте ту же формулу вниз. Чтобы скопировать формулу в диапазоне B3:B8, скопируйте формулу в ячейку B2, нажав клавишу CTRL+C на клавиатуре, и вставьте в диапазон B3:B8, нажав клавишу CTRL + V.
• Здесь вы можете увидеть вывод, где у нас есть уникальный список названий цветов.

Answer 8

В модуле листа для листа, содержащего список:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngDest As Range

    If Not Intersect(Target, Me.Columns(1)) Is Nothing Then

        Set rngDest = ThisWorkbook.Sheets("Sheet2").Range("A1")

        Me.Range(Me.Range("A2"), Me.Cells(Rows.Count, 1).End(xlUp)).AdvancedFilter _
            Action:=xlFilterCopy, CopyToRange:=rngDest, Unique:=True

    End If

End Sub

Answer 9

Все, что вам нужно сделать, это: Перейти на вкладку "Данные". Выберите "Дополнительно" в разделе "Сортировка и фильтрация". В действиях выберите: скопировать в другое место, если хотите создать новый список - Копировать в любое место. В диапазоне списка выберите список, из которого вы хотите получить записи. И самое главное - проверить: только уникальные записи.

Answer 10

Честно говоря, я последовал этим примерам, и они просто не сработали. После того, как я безуспешно пытался заставить Excel работать, я в итоге просто скопировал все содержимое моей колонки в NotePad++, где я смог найти простое решение за считанные минуты. Взгляните на это: удаление повторяющихся строк в Notepad++

Изменить: Вот краткий обзор, как это сделать в TextFX:

Плагины -> Диспетчер плагинов -> Показать диспетчер плагинов -> вкладка Доступно -> TextFX -> Установить

После того, как TextFX установлен в NotePad++, вы выбираете весь текст, из которого хотите удалить дубликаты, и затем проверяете: TextFX -> Инструменты TextFX -> Сортировка выводит только УНИКАЛЬНЫЕ строки

Затем нажмите "Сортировать строки с учетом регистра" или "Сортировать строки с учетом регистра", и он выполнит уникальную сортировку.

Answer 11

Поэтому для этой задачи сначала отсортируйте данные по порядку от А до Я или от Я до А, затем вы можете просто использовать одну простую формулу, как указано ниже:

=IF(A2=A3, "Duplicate", "Not Duplicate")

Приведенная выше формула утверждает, что если данные столбца A2 ( A - это столбец, а 2 - номер строки) аналогичны A3 (A - это столбец, а 3 - номер строки), то будет напечатано Duplicate, иначе будет напечатано Not Duplicate.

Давайте рассмотрим пример. Столбец A состоит из адреса электронной почты, в котором некоторые дубликаты, поэтому в столбце 2 я использовал приведенную выше формулу, которая в результатах отображала мне 2 дубликата ячейки: одна строка 2 и строка 6.

Как только вы получили повторяющиеся данные, просто поместите фильтр на свой лист и сделайте видимыми только дублированные данные и удалите все ненужные данные.

Answer 12

Другой подход, начиная с Excel 2016, - использовать Power Query.

Как:

• выберите данные (включая имя поля),
• использовать меню Data > From a table or a range,
• (Excel превратит ваш лист в таблицу Excel, что очень удобно),
• в редакторе Power Query щелкните правой кнопкой мыши ColumnA (заголовок столбца) и Remove duplicates,
• в меню выберите Close and load, выберите, где вы хотите получить результат, и все готово, вот так.
• Всякий раз, когда вы хотите обновить таблицу результатов, щелкните ее правой кнопкой мыши и выберите Refresh.

Преимущества:

• он использует CPU только при ручном обновлении, что очень удобно для длинных списков,
• если вам интересно, это предлагает много других мощных возможностей.

Недостатки:

• он не обновляется на лету (вам нужно щелкнуть правой кнопкой мыши и обновить таблицу результатов),
• люди со старой версией Excel не смогут обновить таблицу результатов.

Answer 13

Найти здесь упомянутую выше формулу с контролем ошибок

=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"") 

где: (B2:B9 - данные столбца, из которого вы хотите извлечь уникальные значения, D1 - ячейка выше, где находится ваша формула)

Answer 14

СОВРЕМЕННЫЙ подход заключается в рассмотрении случаев, когда столбец информации поступает от веб-службы, такой как источник OData. Если вам нужно сгенерировать фильтр для выбора полей из массивных данных, которые имеют реплицированные значения для столбца, рассмотрите код ниже:

var CatalogURL = getweb(currenturl)
                 +"/_api/web/lists/getbytitle('Site%20Inventory%20and%20Assets')/items?$select=Expense_x0020_Type&$orderby=Expense_x0020_Type";

/* the column that is replicated, is ordered by <column_name> */

    OData.read(CatalogURL,
        function(data,request){

            var myhtml ="";
            var myValue ="";

            for(var i = 0; i < data.results.length; i++)
            {
                myValue = data.results[i].Expense_x0020_Type;

                if(i == 0)
                {
                        myhtml += "<option value='"+myValue+"'>"+myValue+"</option>";
                }
                else
                if(myValue != data.results[i-1].Expense_x0020_Type)
                {
                        myhtml += "<option value='"+myValue+"'>"+myValue+"</option>";

                }
                else
                {

                }


            }

            $("#mySelect1").append(myhtml);

        });