Lookup multiple values and return a single string

0
votes

I have found how to lookup multiple values and return them as a list (one value in one cell). But I need to display the found list in a single cell (as a string with break lines); preferably with a semicolon after each value.

For example:

Source list (one value - one cell):

A
A
B
B
B
C

Result (displayed in a single cell):

A;
B;
C;

Thank you!

2
arraysexcellookup
As far as I can see you'll need VBA for this. – Scott Holtzman
Are there any way to concatenate all array formula elements into one string? E. g. as far as I know we can sum all array elements. – Serg

2 Answers

2
votes

You'll need to extract unique values and then concatenate those values.

When a2:a7 = {a, a, b, b, b, c}, type this at b2 and hit ctrl + shift + enter. 

=IFERROR(INDEX($A$2:$A$7,MATCH(SUM(COUNTIF(B$1:B1,$A$2:$A$7)),COUNTIF($A$2:$A$7,"<"&$A$2:$A$7),0)),"")

Then copy b2 and paste it onto b3:b7. Now you have a list of unique values. This method is from this answer.

Now that you have the list, you only have to join them. type this at c2.

=IF(B2="", "", B2&";"&CHAR(10))
&IF(B3="", "", B3&";"&CHAR(10))
&IF(B4="", "", B4&";"&CHAR(10))
&IF(B5="", "", B5&";"&CHAR(10))
&IF(B6="", "", B6&";"&CHAR(10))
&IF(B7="", "", B7&";"&CHAR(10))

See the picture.

enter image description here

I know this is ulgy. But there's no built-in formula in Excel. Other workarounds without VBA are here and there, just in case.

By the way, Google Spreadsheet provides all of these as built-in functions. Just one line.

=join(";"&char(10), filter(unique(A2:A7), not(isblank(unique(A2:A7)))))&";"
0
votes

Use a static dictionary object in a user defined functiuon (aka UDF) and overwrite any duplicates.

Option Explicit

Function udfUniqueList(str As String, _
                       delim As String, _
                       Optional cs As Boolean = False)
    Dim a As Long, arr As Variant
    Static dict As Object
                      
    If dict Is Nothing Then
        Set dict = CreateObject("Scripting.Dictionary")
    End If
    dict.RemoveAll
    dict.CompareMode = IIf(cs, vbBinaryCompare, vbTextCompare)
    
    arr = Split(str, Chr(10))
    For a = LBound(arr) To UBound(arr)
        dict.Item(arr(a)) = a
    Next a
    
    udfUniqueList = Join(dict.keys, delim) & delim

End Function

A static object is best for functions that will be copied down a long column as the object does not have to be recreated for repetitious use.

enter image description here

Remember to turn 'wrap text' on in the destination cell.