Как объединить несколько столбцов в Excel, чтобы уникальные значения были сохранены в новом столбце?

Question

Скажем, данные в столбцах Col1 и Col2 ниже. Как мы можем создать Col3 путем слияния Col1 и Col2?

Col1  Col2   Col3
------------------
  a      b      a
  b      c      b
  c      d      c
  g      e      d
         f      e
                f
                g

Answer 1

Вот способ использования скрытых столбцов, который не требует VBA. Однако это не сортирует последний столбец.

После ввода этих формул вы можете скрыть три столбца в середине, щелкнув правой кнопкой мыши их заголовки и выбрав Скрыть:

И это оставит вас с этим:

Формулы в четырех столбцах после Col1 и Col2 должны быть скопированы, по крайней мере, вдвое больше максимальной длины Col1 и Col2. Я предлагаю выбрать произвольное большое число, которое, по вашему мнению, никогда не превысит число строк Col1 и Col2, а затем просто забыть его.

Это предполагает, что ваши Col1 и Col2 находятся в столбцах A и B и что строка заголовка включена, а строка заголовка находится в строке 1. Вам придется немного их изменить, если строка заголовка не включена.

Формула комбинированного столбца:

=OFFSET($A$2, FLOOR((ROW() - ROW($2:$2)) / 2, 1), MOD(ROW() - ROW($2:$2), 2))

Формула отфильтрованного столбца:

=IF(C2 = 0, "", IF(ISERROR(MATCH(C2, C$1:C1, 0)), ROW(), ""))

Формула отсортированного столбца:

=SMALL(D:D, ROW() - ROW($1:$1))

Формула столбца Col3:

=IFERROR(INDEX(C:C, E2), "")

Способ, которым это работает, состоит в том, чтобы сначала объединить Col1 и Col2, начиная с первого ряда и далее вниз (ячейка A2, ячейка B2, ячейка A3, ячейка B3 и т.д.). Это достигается с помощью комбинации функции OFFSET сочетании с функциями FLOOR, MOD и ROW .

Затем мы отфильтровываем дубликаты, проверяя, существует ли значение в текущей строке во всех предыдущих строках. Для этого мы используем функцию MATCH в сочетании с умным использованием абсолютных и относительных ссылок. Вместо значения возвращается номер строки, чтобы его можно было использовать в следующих двух столбцах.

Затем мы выводим все перечисленные номера строк в верхнюю часть с помощью комбинации функций SMALL и ROW .

И, наконец, нам просто нужно вернуть INDEX столбца Combined, соответствующего строке столбца Sorted.

Answer 2

Сделайте это на бумажном носителе, на случай, если что-нибудь взорвется или растает, потому что «Отменить» не всегда работает.  Скопируйте данные из двух столбцов, последовательно, в другой рабочий столбец; назовем это V. Если у ваших существующих данных есть заголовки, скопируйте заголовок из столбца A; в противном случае поместите фиктивное значение в ячейку V1.

Щелкните где-нибудь в столбце V. Теперь перейдите на вкладку «Данные», группу «Сортировка и фильтр» и нажмите «Дополнительно»:

         

Это вызовет диалоговое окно «Расширенный фильтр»:

        

Убедитесь, что «Список диапазонов» отображает ваши данные в столбце V. Выберите «Копировать в другое место» и «Только уникальные записи».  Введите «W1» в поле «Копировать в» - или щелкните в поле, а затем щелкните в W1 (есть несколько методов, которые получат тот же результат).  Нажмите «ОК». Вы должны получить что-то вроде этого:

           

Теперь скопируйте данные из столбца W в столбец C. При необходимости выполните сортировку.  Затем очистите столбцы V и W.

^{Раскрытие: приведенный выше ответ был частично скопирован из моего ответа на Группирование столбцов данных по общим значениям.}

Answer 3

Этот VBA должен сделать это. Я уверен, что есть формульный путь, но я нахожу VBA намного легче читать в этих ситуациях

Sub FindUniques()
'Define and set variables
Dim rangeIn As Range, rangeOut As Range
Set rangeIn = Application.Selection
Set rangeIn = Application.InputBox("Input Range", "Input Range", rangeIn.Address, Type:=8)
Set rangeOut = Application.InputBox("Select the first cell to output to", "Output Range Start", Type:=8)

Dim colLoop, rowLoop

Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")

Dim curVal


'Loop through range columns
For colLoop = 1 To rangeIn.Columns.Count
    'Loop through range rows
    For rowLoop = 1 To rangeIn.Rows.Count
        'Get the value of cell in current column, current row
        curVal = rangeIn.Cells(rowLoop, colLoop).Value
        'Check if we've already added this to the dictionary. If we have, it's not unique, skip it
        If Not dic.Exists(curVal) And curVal <> "" Then
            'Write the value in place of our output
            rangeOut.Value = curVal
            'Add the value to our dictionary so it'll be skipped if it comes up again
            dic(curVal) = ""
            'Go to the next cell down ready to write the next one
            Set rangeOut = rangeOut.Offset(1, 0)
        End If
    Next
Next
End Sub

Answer 4

VBA решение

Sub MergeColumnsUnique()    
  Dim MyLookupCols As Variant: MyLookupCols = Array(1, 2)
  Dim MyOutputCol As Integer: MyOutputCol = 3
  Dim MySheet As Variant: Set MySheet = ThisWorkbook.Sheets(1)

  For Each col In MyLookupCols
    curCol = MySheet.UsedRange.Columns(col)

    For Each cell In curCol
      If Not IsEmpty(cell) And MySheet.Columns(MyOutputCol).Find(cell) Is Nothing Then
        Count = Count + 1
        MySheet.Cells(Count, MyOutputCol) = cell
      End If
    Next cell

  Next col        
End Sub