I have a data table that is sent to me from a 3rd party vendor and in the
cell the vendor uses letters to refer to what they mean for example:

c = Rock-Eval analysis checked and confirmed
lc = Leco TOC analysis checked and confirmed

I need to create a macro that will look at this letter and put in what it
correlates to. To make it even harder, sometimes there are multiple letters
that refer to a sentence. Such as "c,lc". The combinations in letters vary
from the data I recieve. If both letters appears I would like to see
"Rock-Eval and Leco TOC analysis checked and confirmed"

Thanks for any help

range name a table where the first column is the letters and the second
column the description...
so row 1 column 1 is LC
and row 1 column 2 is Leco Toc
i my code, i used TASKLIST as teh range name.
I create a user defined function (UDF) that tales the cell vale, eg C or
C,LS and returns the concatenated list

Option Explicit
' NOTE datatabel is ranged named TASKLIST
Public Function decode(text As String) As String
Dim item As Variant
For Each item In Split(text, ",")
decode = decode & ", " & safelookup(item)
Next
decode = Mid(decode, 3)
End Function
Function safelookup(item)
On Error Resume Next
safelookup = Application.WorksheetFunction.VLookup(CStr(item), _
Range("TASKLIST"), 2, False)
On Error GoTo 0
End Function


"James" wrote:

I have a data table that is sent to me from a 3rd party vendor and in the
cell the vendor uses letters to refer to what they mean for example:

c = Rock-Eval analysis checked and confirmed
lc = Leco TOC analysis checked and confirmed

I need to create a macro that will look at this letter and put in what it
correlates to. To make it even harder, sometimes there are multiple letters
that refer to a sentence. Such as "c,lc". The combinations in letters vary
from the data I recieve. If both letters appears I would like to see
"Rock-Eval and Leco TOC analysis checked and confirmed"

Thanks for any help

i would say, first insert a couple of columns & break out that column,
using a comma as a delimiter, using Text To Columns.
then it's a simply search-&-replace operation from there (you could
record it all with the macro recorder & see what you come up with -
not the most efficient coding, but a place to start).
hope this helps
susan


On Mar 12, 9:50*am, James wrote:
I have a data table that is sent to me from a 3rd party vendor and in the
cell the vendor uses letters to refer to what they mean for example:

c = Rock-Eval *analysis checked and confirmed
lc = Leco TOC analysis checked and confirmed

I need to create a macro that will look at this letter and put in what it
correlates to. *To make it even harder, sometimes there are multiple letters
that refer to a sentence. Such as "c,lc". *The combinations in letters vary
from the data I recieve. If both letters appears I would like to see
"Rock-Eval and Leco TOC analysis checked and confirmed"

Thanks for any help

Took me a bit to get it to work but it is going now, thanks again.

"Patrick Molloy" wrote:

range name a table where the first column is the letters and the second
column the description...
so row 1 column 1 is LC
and row 1 column 2 is Leco Toc
i my code, i used TASKLIST as teh range name.
I create a user defined function (UDF) that tales the cell vale, eg C or
C,LS and returns the concatenated list

Option Explicit
' NOTE datatabel is ranged named TASKLIST
Public Function decode(text As String) As String
Dim item As Variant
For Each item In Split(text, ",")
decode = decode & ", " & safelookup(item)
Next
decode = Mid(decode, 3)
End Function
Function safelookup(item)
On Error Resume Next
safelookup = Application.WorksheetFunction.VLookup(CStr(item), _
Range("TASKLIST"), 2, False)
On Error GoTo 0
End Function


"James" wrote:

I have a data table that is sent to me from a 3rd party vendor and in the
cell the vendor uses letters to refer to what they mean for example:

c = Rock-Eval analysis checked and confirmed
lc = Leco TOC analysis checked and confirmed

I need to create a macro that will look at this letter and put in what it
correlates to. To make it even harder, sometimes there are multiple letters
that refer to a sentence. Such as "c,lc". The combinations in letters vary
from the data I recieve. If both letters appears I would like to see
"Rock-Eval and Leco TOC analysis checked and confirmed"

Thanks for any help