Excel: объединить строки на основе дубликатов ключей

Question

У меня есть два столбца Ключ и Значение (стандартная карта)

Есть дублированные ключи. Удалить дублированные ключи тривиально (а именно кнопку «Удалить дубликаты»). Но это приведет к удалению некоторых полезных данных в столбце значений (или, наоборот, к нарушению пар).

Что действительно нужно сделать, так это консолидировать данные, чтобы значения объединялись на основе дубликатов ключей. В идеале это должно быть сделано с особым форматированием, но это менее важно.

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

Например, http://www.extendoffice.com/documents/excel/1268-excel-combine-duplicate-rows-and-sum.html

Я надеялся, что суммирование будет эквивалентно объединению; но, увы, похоже, что это не так.

Answer 1

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

Вот окончательный результат этого вопроса:

И вот код:

Option Explicit
Function LookupCSVResults(lookupValue As Variant, lookupRange As Range, resultsRange As Range) As String

    Dim s As String 'Results placeholder
    Dim sTmp As String  'Cell value placeholder
    Dim r As Long   'Row
    Dim c As Long   'Column
    Const strDelimiter = "|||"  'Makes InStr more robust

    s = strDelimiter
    For r = 1 To lookupRange.Rows.Count
        For c = 1 To lookupRange.Columns.Count
            If lookupRange.Cells(r, c).Value = lookupValue Then
                'I know it's weird to use offset but it works even if the two ranges
                'are of different sizes and it's the same way that SUMIF works
                sTmp = resultsRange.Offset(r - 1, c - 1).Cells(1, 1).Value
                If InStr(1, s, strDelimiter & sTmp & strDelimiter) = 0 Then
                    s = s & sTmp & strDelimiter
                End If
            End If
        Next
    Next

    'Now make it look like CSV
    s = Replace(s, strDelimiter, ",")
    If Left(s, 1) = "," Then s = Mid(s, 2)
    If Right(s, 1) = "," Then s = Left(s, Len(s) - 1)

    LookupCSVResults = s 'Return the function

End Function

---

Чтобы показать пример, вот формула, показанная в ячейке E1:

=LookupCSVResults(D1,B1:B7,A1:A7)

А вот CSV-версия данных в диапазоне A1:D7:

Адам, красный ,, Красный
Адам, зеленый ,, зеленый
Адам, голубой ,, синий
Боб, красный ,, желтый
Боб, желтый ,,
Боб, зеленый ,,
Карл, красный ,,

Answer 2

Я искал весь интернет для этого. Я изменил эту строку:

If lookupRange.Cells(r, c).Value = lookupValue Then

к этому, так что частичное совпадение строк работает:

If InStr(1, lookupRange.Cells(r, c).Value, lookupValue) Then