My first hedge is, I've been tasked with a reporting job and I haven't used Excel for years. I looked up a thread from 2004 that mimics what I need and copying the code, tried to replace the code to match my sheet ranges, I'm not sure what went wrong, but I get a compiler and syntax error. so I replaced all the : ;_ - (operators) in the Names with letters.
Would anyone be able to help me adapt the following to my attached sample xls? Forum sample.xlsx
I need to remove dupes from columns N (TRN), O (BRO), P (APP), Q (BUS)
the text in each multi-value cell is separated with commas.
Currently looks like: GBTXFA110Z Ariba Indirect Procurement for Requestors,GBTXFA110Z Ariba Indirect Procurement for Requestors,GBTXFA112Z Creating and Approving Business Role Access (eLearning)
Needs to look like: GBTXFA110Z Ariba Indirect Procurement for Requestors,GBTXFA112Z Creating and Approving Business Role Access (eLearning)
Forum sample.xlsx
VB
[Option Explicit
Sub Remove_DupesInString()
' this puts the final value in column m
Dim starval As String
Dim finval As String
Dim strarray() As String
Dim x As Long
Dim TRN As Long
' step through each cell in range
For Each cell(, ", ") In Sheets(1).Range("TRN")
Erase strarray ' erase array
finval = "" ' erase final value"
starval = cell.Value
strarray = Split(starval, ",")
'Step through length of string and look for duplicate
For rw = 0 To UBound(strarray)
For n = rw + 1 To UBound(strarray)
If Trim(strarray(n)) = Trim(strarray(rw)) Then
strarray(n) = "" 'if duplicate clear array value
End If
Next n
Next rw
' combine all value in string less duplicate
For x = 0 To UBound(strarray)
If strarray(x) <> "" Then
finval = finval & Trim(strarray(x)) & ", "
End If
Next x
' remove last space and comma
finval = Trim(finval)
finval = Left(finval, Len(finval) - 1)
' output value to Column m
cell.Offset(0, 9).Value = finval
Next cell
End Sub]
Thanks,
Trishnalynn
Bookmarks