Multiple lookups for string in once cell and return comma separated values

[MurugaSatish]

Lookup Table
Jon Doe --- jon.doe!email.com
Dell Man --- jon.doe!lala.com
App Guy-- aguy!app.com



Current String form in once cell = John Doe,Dell Man,App Guy,John Doe
Required output without duplicates in one cell = john.doe!email.com,dell.man!lala.com,aguy!app.com


Any custom formula or vba macro code for this?

Thanks in advance for your help!

[mana]

Table
　-----A-----　 　　------B-----
1 Jon Doe　　　 jon.doe!email.com
2 Dell Man　　　 jon.doe!lala.com
3 App Guy　　　 aguy!app.com






Option Explicit

Sub test()
Dim s As String
Dim dicT As Object, dicO As Object
Dim v, c As Range, i As Long
Dim tmp As String

s = "John Doe,Dell Man,App Guy,John Doe"

v = Split(s, ",")

Set dicT = CreateObject("scripting.dictionary")
Set dicO = CreateObject("scripting.dictionary")

For Each c In Range("a1").CurrentRegion.Resize(, 1)
dicT(c.Value) = c.Offset(, 1).Value
Next

For i = 0 To UBound(v)
tmp = v(i)
If Not dicO.exists(tmp) Then
If dicT.exists(tmp) Then
dicO(tmp) = dicT(tmp)
Else
dicO(tmp) = tmp
End If
End If
Next

MsgBox Join(dicO.items, ",")

End Sub

[MurugaSatish]

Thank you so much! This code worked after little tweaks. You are a life saver.

[MurugaSatish]

Can you please help me with a custom formula to get this string output for multiple lookups?

Current Input string in one cell: jane.doe!noemail.com,mary.jane!noemail.com
Current Output string required in one cell: Jane Doe(jane.doe!noemail.com-AType),Mary Jane(mary.jane!noemail.com-BType)


Current database in a different sheet



Full Name
Email Address
First Name
Last Name
Product
City
Type


Jane Doe
jane.doe!noemail.com
Jane
Doe
Qlikview
Gotham
A Type


Mary Jane
mary.jane!noemail.com
Mary
Jane
Matlab
Asgard
B Type

[mana]

Option Explicit

Sub test()
Dim s As String
Dim ws As Worksheet
Dim v, i As Long
Dim m
Dim tmp As String

s = "jane.doe!noemail.com,mary.jane!noemail.com"

Set ws = Worksheets("Database")

v = Split(s, ",")

For i = 0 To UBound(v)
m = Application.Match(v(i), ws.Columns("B"), 0)
If IsNumeric(m) Then
tmp = tmp & "," & ws.Cells(m, "d").Value & "(" & v(i) & "-" & ws.Cells(m, "g").Value & ")"
End If
Next

MsgBox Mid(tmp, 2)

End Sub

[MurugaSatish]

Thank you again!

How do I convert this to a custom function?

Like Outputstring = customfunction(reference to input string)

[mana]

Option Explicit


Function test(s As String, tbl As Range) As String
Dim v, i As Long
Dim m
Dim tmp As String

v = Split(s, ",")

For i = 0 To UBound(v)
m = Application.Match(v(i), tbl.Columns("B"), 0)
If IsNumeric(m) Then
tmp = tmp & "," & tbl.Cells(m, "d").Value & "(" & v(i) & "-" & tbl.Cells(m, "g").Value & ")"
End If
Next

test = Mid(tmp, 2)

End Function