joetwa
asked on
excel vba lookup function with multiple data elements in a single cell
looking for someone to help me write a quick and dirty function to look at ID numbers in a cell and to return a corresponding name (or names) associated with the ID number(s) via a lookup pointing to a separate array
IDs would be numeric in this format, seperated by "; " where there are multiples - eg
100 (for only one ID in a cell)
100; 101; 108 (for 3 IDs in a cell)
etc.
see attached file for more detail
thanks in advance
sample.xls
IDs would be numeric in this format, seperated by "; " where there are multiples - eg
100 (for only one ID in a cell)
100; 101; 108 (for 3 IDs in a cell)
etc.
see attached file for more detail
thanks in advance
sample.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
joetwa,
The code below is in the atached file. Press the button to get the results.
Patrick
The code below is in the atached file. Press the button to get the results.
Patrick
Sub specialmacro()
Dim rng As Range
Dim celle As Range
Dim IDs() As String
Dim Owners() As String
Dim i As Long
Dim coll1 As New Collection
Dim coll2 As New Collection
With Sheets("Sheet1")
If .Cells(2, "D") <> "" Then
.Range(.Cells(2, "D"), .Cells(.Rows.Count, "D").End(xlUp).Offset(0, 1)).ClearContents
End If
Set rng = Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))
For Each celle In rng
IDs = Split(celle, ";")
Owners = Split(celle.Offset(0, 1), ";")
For i = 0 To UBound(IDs)
On Error Resume Next
coll1.Add CStr(IDs(i)), CStr(IDs(i))
On Error Resume Next
coll2.Add CStr(Owners(i)), CStr(Owners(i))
Next i
For i = 0 To UBound(IDs)
IDs(i) = ""
Owners(i) = ""
Next i
Next celle
For i = 1 To coll1.Count
.Cells(i + 1, "D") = coll1(i)
.Cells(i + 1, "E") = Trim(coll2(i))
Next i
End With
End Sub
splitter-01.xls
ASKER
perfect - function works great - thanks for your help
Open in new window