It appears you are suggesting that we process the individual string from a Excel Cell, and determine if it has duplicate strings.
Assuming by your description:
- 1 = aolsystemmsg; standbac; aolsystemmsg; aolsystemmsg; standbac; standbac; aolsystemmsg
- 2 = aolsystemmsg; richcarter78; aolsystemmsg; aolsystemmsg; richcarter78; aolsystemmsg
Becomes:
- 1 = aolsystemmsg; standbac
- 2 = aolsystemmsg; richcarter78
Youmight even want further that no duplicates should exist in all cells but we live it at this for now.
Primarily, doing this in VB through OLE Automation of Office, I would expect you have some understanding of the Excel.Application, Excel.WorkBook, and Excel.Worksheet classes, and further more, most importantly, the universal Excel object : Excel.Range
So given that we will set up simply:
Imports Microsoft.Office.Interop' This should already be in your project from Office Interop
private _wb as Excel.WorkBook
private _ws as Excel.WorkSheet
'Assuming you already have the Application Created or Acquired
'and you have an active Work Book and Worksheet
' this would analyze the cell for you:
public Sub RemoveDuplicates(Cell as String)
dim Found() as String = nothing
dim Current() as String
dim s as string
if _ws isnot nothing then
if _ws.Range(Cell) isnot nothing then 'this may need a try-catch clause to protect itself
s = _ws.Range(Cell).Value
Current = s.Split(";", StringSplitOptions.RemoveEmptyEntries)
for each s in Current
if (Found is NOthing) orelse (Array.IndexOf(Found, s) = -1) then
if found is nothing then Found = new String() {}
redim Found(Found.Length)
Found(Found.Length - 1) = s
end if
next
if Found isnot nothing andalso Found.Length > 0 then
_ws.Range(Cell).Value = String.Join(";", Found)
end if
end if
end if
end Sub
You could also use collections if you wish, as opposed to arrays, and I utilize extensions to simplify a lot of that code.
Another Avenue to try would be to look into the Range.Cell() property, which is also under WorkSheet, but that requires the division of row and column, where as "A1" is parsed by the Range() object directly to a single cell.
to call the above code you'd simply run:
RemoveDuplicates("A1")
RemoveDuplicates("A2")
Hope this helps
