Объединить строки Excel в таблицу

Question

Можно ли в Excel сделать такую ​​таблицу:

И сделайте что-то вроде этого:

Поэтому, если в таблице есть два или более человека с одинаковым адресом, я хочу объединить строки, записав все имена в одну ячейку, а общий адрес — в другую.

Answer 1

Псевдокод для выполнения этого в VBA: (Вы можете разобраться с настоящим кодом). Обратите внимание, что это было написано только в ответе, поэтому вам придется заполнить пробелы, но, надеюсь, вы уловили общую идею циклического перебора каждого адреса и сбора любого имени, у которого есть столбец, соответствующий этому адресу.

dim address_on as string 'current address
dim names as string 'concatenate list of names
dim in_list_already as boolean

    For address_row = 1 to range().end(xldown).row
    'loop through addresses
        address_on = Range("Column" & address_row).value
        names = ""

        'First check if address_on is already in destination list?
        in_list_already = false
        for check_row = 1 to range("Destination").end(xldown).row
            If range("Destination Col" & check_row).value = address_on then
                in_list_already = true
                Exit for
            End if
        next

        if in_list_already = false then
            'Find all names that have this address
            for name_row = 1 to range().end(xldown).row
                If range("Address Column" & name_row).value = address_on then
                    names = names  & Range("Name Column" & name_row).value & ","
                End if
            next

            'remove last comma
            names = names.remove(Len(names)-1,1)

            'add to list
            Range("Column to insert to 1" & next_slot).value = names
            Range("Column to insert to 2" & next_slot).value = address_on 

        End if 

next

Как видите, names = names & Range("Name Column" & name_row).value & ","при наличии совпадений просто добавляется в список.

Метод, описанный выше, таков:

1. Просмотрите ваши данные
2. Есть ли элемент в выводе? Если нет, то не отображать его (чтобы не было дубликатов).
3. Соберите все имена, которые содержат адрес, по которому вы находитесь.
4. Вывод результатов

Answer 2

Это легко сделать с помощью формул, если записи агрегированы, как в вопросе (это сложнее, если совпадающие адреса не находятся вместе):

Отображение столбцов для отображения метода:

Я создал два вспомогательных столбца, один для имен и один для фильтрации. Чтобы соответствовать желаемой последовательности в вопросе, я вставил вспомогательный столбец имен слева. Формула в A2:

=IF(C2=C1,A1& ", " &B2,B2)

Столбец C в моем — это столбец Адрес. Он проверяет, совпадает ли адрес в текущей строке с адресом в предыдущей строке. Если нет, это означает, что это новый адрес, и он вставляет связанное имя. Если это тот же адрес, что и предыдущий, он объединяет запятую и имя строки с результатом в предыдущей строке (так что у вас может быть любое количество совпадающих адресов).

Вспомогательный столбец D проверяет, является ли адрес строки последним для этого адреса (т.е. отличается ли следующая строка). Формула в D2:

=C2<>C3

После заполнения формулами столбцов, щелкните D1 и Автофильтр в меню. В раскрывающемся списке D1 снимите флажок ЛОЖЬ. Это скроет все строки, которые не являются последней строкой для каждого адреса.

Если вам нужен постоянный, "чистый" список, скопируйте нужные отфильтрованные столбцы и вставьте в новое место. Будут скопированы только видимые, как в моих столбцах F и G ниже:

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

Answer 3

Я хотел бы предложить одну UDF (пользовательскую функцию), которая решит эту проблему.

Как это работает:

• Я предполагаю, что исходные данные находятся в диапазоне A2:B8.

• Введите эту формулу массива (CSE) в E2, закончите с помощьюCtrl+Shift+Enterи залейте.

  {=INDEX($B$2:$B$8, MATCH(SMALL(IF(COUNTIF($E$1:E1, $B$2:$B$8)=0, COUNTIF($B$2:$B$8, "<"&$B$2:$B$8), " "), 1), COUNTIF($B$2:$B$8, "<"&$B$2:$B$8), 0))}
  

• КопироватьиВставитьниже показан код как Модуль.

  Function ExtractinOneCell(LookupValue As String, LookupRange As Range, ColumnNumber As Integer, Char As String)
  
  Dim I As Long
  
  Dim xRet As String
  For I = 1 To LookupRange.Columns(2).Cells.Count
  
      If LookupRange.Cells(I, 2) = LookupValue Then
          If xRet = "" Then
              xRet = LookupRange.Cells(I, ColumnNumber) & Char
          Else
              xRet = xRet & "" & LookupRange.Cells(I, ColumnNumber) & Char
          End If
      End If
  
  Next
  
  ExtractinOneCell = Left(xRet, Len(xRet) - 2)
  
  End Function
  

• Введите эту формулу D2и заполните ее.

  =ExtractinOneCell(E2,$A$2:$B$8,1,", ")