1

I have two columns Key and Value (standard map)

There are duplicated keys. It is trivial to delete the duplicated keys (namely the delete duplicates button). But that would delete some useful data in the value column (or alternatively mess up the pairings).

What really should be done is for the data to be consolidated such that the values are concatenated based on duplicate keys. Ideally this would be done with a particular formatting in mind, but that is less important.

This seems relatively straightforward for numerical values, but less so for text based requirements.

E.g. http://www.extendoffice.com/documents/excel/1268-excel-combine-duplicate-rows-and-sum.html

I was hoping that summation would be equivalent to concatenation; but alas this does not seem to be the case.

Stumbler
  • 278

2 Answers2

3

I think this sounds like a similar question I just answered with VBA. For your solution, you would copy the Key column, remove the duplicates, and then use a formula to call a UDF that returns a string of values separated by commas.

Here's the final result from that question:

Screenshot 1

And here's the code:

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

To help show the example, here's the formula shown in cell E1:

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

And here's the CSV version of the data in the range A1:D7:

Adam,Red,,Red
Adam,Green,,Green
Adam,Blue,,Blue
Bob,Red,,Yellow
Bob,Yellow,,
Bob,Green,,
Carl,Red,,

Community
  • 1
Engineer Toast
  • 5,218
0

I've been searching the entire internet for this. I modified this line:

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

to this, so that a partial string match works:

If InStr(1, lookupRange.Cells(r, c).Value, lookupValue) Then
Stephen Rauch
  • 3,296
littlemo
  • 1