Native worksheet formulas simply do not handle string concatenation well. Even the new string functions that are being introduced such as TEXTJOIN function1 and the updated CONCAT function2 have difficulty with conditional concatenation beyond TEXTJOIN's blank/no-blank parameter.
Here are a pair of User Defined Functions (aka UDF) that will perform the tasks.
Function udfUniqueList(rng As Range, _
Optional delim As String = ",")
Dim str As String, r As Range
'always truncate ranges as parameters to the usedrange
Set rng = Intersect(rng, rng.Parent.UsedRange)
str = rng(1).Value2
For Each r In rng
If Not CBool(InStr(1, delim & str & delim, delim & r.Value2 & delim, vbTextCompare)) Then
str = str & delim & r.Value2
End If
Next r
udfUniqueList = str
End Function
Function udfRogueHeaders(rng As Range, hdr As Range, _
Optional delim As String = ",", _
Optional bBlnks As Boolean = False)
Dim i As Long, bas As String, str As String
'always truncate ranges as parameters to the usedrange
Set rng = Intersect(rng, rng.Parent.UsedRange)
'reshape hdr to be identical to rng
Set hdr = hdr.Resize(rng.Rows.Count, rng.Columns.Count)
bas = rng(1).Value2
For i = 1 To rng.Cells.Count
If (CBool(Len(UCase(rng.Cells(i).Value2))) And Not bBlnks) Or _
bBlnks Then
If UCase(bas) <> UCase(rng.Cells(i).Value2) Then
str = str & IIf(CBool(Len(str)), delim, vbNullString) & _
hdr.Cells(i).Value2
End If
End If
Next i
udfRogueHeaders = str
End Function
In P2:Q2 as,
=udfUniqueList(A2:L2)
=udfRogueHeaders(A2:L2, A$1:L$1)
Results:
1 The TEXTJOIN function is being introduced with Excel 2016 ? Office 365 ? Excel Online. It is not available in earlier versions.
2 The new CONCAT function for Excel 2016 ? Office 365 ? Excel Online is intended to replace the older CONCATENATE function with improved functionality.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…