Sleeper: Remove duplications

[ilyaskazi]

cell value = ;ZONE 1;ZONE 2;ZONE 1;ZONE 3;ZONE 3;ZONE 6;ZONE 6;ZONE 8;ZONE 9;ZONE 10;

OUTPUT REQUIRED = ;ZONE 1;ZONE 2;ZONE 3;ZONE 6;ZONE 8;ZONE 9;ZONE 10;

[OBP]

ilyaskazi, can you sort the data in it's current position?
If not have you a suitable place to "Paste" the data to so that it can be sorted as that makes the code much faster. If you are limited to just the 10 values you have shown it will not make much difference, but if you will need to re-arrange a large number of values it will.

[ilyaskazi]

cell value shown above contains in a single cell only.

it is same as exactly shown above.

output also required in single cell only.

Data may differ from shown.

[OBP]

ilyaskazi, is VBA on a command button OK?

[ilyaskazi]

what?
i hv created toolbar button, upon clicking which should execute my macro...

some one hv given solution like this given below but not working perfect...


Sub rmvDupes()
'''CAUTION - THIS CODE MAY OVERWRITE ORIGINAL DATA
Dim sOrig As String, sConv As String
Dim aOrig()
Dim i As Integer, j As Integer
On Error GoTo ErrorHandler
sOrig = Selection.value
ReDim aOrig(1 To Len(sOrig) / 4)
For i = 2 To Len(sOrig) Step 4
j = j + 1
aOrig(j) = Mid(sOrig, i, 4)
Next i
For i = UBound(aOrig) To 1 Step -1
For j = 1 To i - 1
If aOrig(i) = aOrig(j) Then aOrig(i) = ""
Next j
Next i
sConv = ";"
For i = 1 To UBound(aOrig)
sConv = sConv + aOrig(i)
Next i
Selection = sConv
Exit Sub
ErrorHandler:
MsgBox "Selection does not contain a useful value", vbInformation, "AutoMax - Selection not valid"
ActiveCell.value = ""
Sheets("FARES").Select
End Sub


try my example of cells given and figure out the solution.... thankyou

[OBP]

ilyaskazi, that code is not flexible and is for use with the "values" between ; ; of 4 characters long. Your Values are 7 Characters long so the code needs to be -


'''CAUTION - THIS CODE MAY OVERWRITE ORIGINAL DATA
Dim sOrig As String, sConv As String
Dim aOrig()
Dim i As Integer, j As Integer
On Error GoTo ErrorHandler
sOrig = Selection.Value
ReDim aOrig(1 To (Len(sOrig) - 2) / 7)
For i = 2 To (Len(sOrig) - 2) Step 7
j = j + 1
aOrig(j) = Mid(sOrig, i, 7)
Next i
For i = UBound(aOrig) To 1 Step -1
For j = 1 To i - 1
If aOrig(i) = aOrig(j) Then aOrig(i) = ""
Next j
Next i
sConv = ";"
For i = 1 To UBound(aOrig)
sConv = sConv + aOrig(i)
Next i
Selection = sConv
Exit Sub
ErrorHandler:
MsgBox "Selection does not contain a useful value", vbInformation, "AutoMax - Selection not valid"
ActiveCell.Value = ""
Sheets("FARES").Select

[Zack Barresse]

OBP - here is some info I edited your post to take advantage of this; hope you don't mind. :)

[OBP]

firefytr, thanks for that, is there a "help" somewhere on posting replies. Up till now I have only used the qucik reply and then edited the text.

[Zack Barresse]

Sure, right here (http://www.vbaexpress.com/forum/faq.php?faq=vb_faq#faq_vb_read_and_post). There are VBA tags present in the Quick Reply as well, directly above the text box you should see the B, I, U, quote box button and VBA tags button.

To use the VBA tags, select your code that you typed or copied into the reply box, then click on the VBA button, it will wrap the selection (this does work well with FireFox also, unlike some other forums).