Remove Duplicate strings in a cell

[beijing0305]

Cell A1: 001-001, 004-006, 007-090, 004-006, 100-300, 040-001, 090-001, 500-555

suppose we have the content in cell A1.

I have a working function from this forum that works (i.e. remove duplicates)

Now, I have need to modify the IF statement to NOT add the "040-001" and the the likes (090-001), but I need to keep the "001-001". I know 001-001 is one variant of ###-001. Would be great if someone with more brain power to help me out with this logic. Thanks!

...
...
For Each x In Split(txt, delim)
if Trim(x) <> "" AND Not .exists(Trim(x)) AND Not (Trim(x) Like "###-001") Then .add Trim(x), Nothing 'This works, but it also don't add the 001-001 record
Next
...
...

Furthermore, I would like to develop a logic where the not to pick up any string that the first 3#s is greater than the 3#s after the dash. e.g. 100-101 => add, 100-001 => do not add to final list
I know this will involve converting the string to number with 3 digits and compare the first 3-digit number to the 2nd 3-digit number.

Thanks in advance.

[XLent]

so, perhaps you could try:

Please Login or Register  to view this content.

[beijing0305]

so, perhaps you could try:

Please Login or Register  to view this content.

Got a #VALUE!, but if I put parenthesis between your suggested conditions, I got a null in return. Could you tell me what Evaluate here are supposed to have returned? Trim(x) = "001-001" would return True. Since "001-001" is part of "###-001", Not (Trim(x) Like "###-001") would be False.

[Marc L]

According to your original cell A1 content, what could be the expected result ? Well elaborate each step …

[ByteMarks]

You could use a collection to remove the duplicates an then check for the other parameters.
Input 001-001, 004-006, 007-090, 004-006, 100-300, 040-001, 090-001, 500-555
Output 001-001, 004-006, 007-090, 100-300, 500-555

Please Login or Register  to view this content.

[Marc L]

A VBA array demonstration as a beginner starter alternative :

Sub Demo1() 
  Const D = ", " 
    Dim S$(), U$(), V, W$(), L& 
        S = Split([A1].Text, D) 
        ReDim U(1 To UBound(S) + 1) 
    For Each V In S 
        If IsError(Application.Match(V, U, 0)) Then 
            W = Split(V, "-") 
            If W(0) <= W(1) Then L = L + 1: U(L) = V 
        End If 
    Next 
        If L Then ReDim Preserve U(1 To L) Else U = Split("") 
        Debug.Print Join(U, D) 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[ByteMarks]

Even better!

[Marc L]

Thanks ByteMarks.

And for duplicate strings within the source cell it could be easily achieved
with an Excel basics like the MATCH worksheet function just using another array variable …

[Marc L]

Post #6 edited in order to remove any duplicate string …

[Rick Rothstein]

   If V = "001-001" Or Not V Like "*-001" Then  

I am not sure you can count on "-001" being the only value that needs to be removed. The OP said "I would like to develop a logic where the not to pick up any string that the first 3#s is greater than the 3#s after the dash". I can imagine lots of scenarios where this condition would be met and the last 3 digits are not "001". Here is my interpretation of the solution I think the OP is looking for...

Please Login or Register  to view this content.

[Marc L]

Hi Rick,

you are right, when it seems too easy the glitch may come, thanks ! Post #6 code updated.

Your demonstration working with a String variable is good enough for short string.
I thought about the same but as the initial post is not well elaborated, as it seems the OP uses something like a Collection
like ByteMarks did in his post #5 so I wanna show the array alternative …

[XLent]

Obviously there have been subsequent follow ups / suggestions, but in reference to post#3

Got a #VALUE!,

without the full code it's hard to debug, but the suggested code works ok for me using a basic Dictionary setup of:

Please Login or Register  to view this content.

the revised line has an AND/OR logical approach for establishing valid values, more specifically:

AND -- not blank, unique, not like ###-001, and first value <= second value
OR -- value is 001-001

Could you tell me what Evaluate here are supposed to have returned?

Evaluate will satisfy below criteria:

...not to pick up any string that the first 3#s is greater than the 3#s after the dash

i.e. Evaluate will execute the Trim(x) as a formula, e.g. 100-101 -> -1

so, where A1 holds 001-001, 004-006, 007-090, 004-006, 100-300, 040-001, 090-001, 500-555

B1: =UniqueItems(A1) --> 001-001, 004-006, 007-090, 100-300, 500-555

[kvsrinivasamurthy]

Try this

Please Login or Register  to view this content.

[beijing0305]

You could use a collection to remove the duplicates an then check for the other parameters.
Input 001-001, 004-006, 007-090, 004-006, 100-300, 040-001, 090-001, 500-555
Output 001-001, 004-006, 007-090, 100-300, 500-555

Please Login or Register  to view this content.

Thank you very much for the response. This is exactly the output I am expecting to see. Didn't realize I could convert string to integer using Int function. I can also follow the code for the most part. A couple of questions. Could you please help me understand your code better?

(1) cln.Add y, y: This adds the y to the collection cln, what's the second parameter "y" do?
(2) ReDim result(myCol.Count - 1): I assume this shortens the array by 1, but why is always going to be one less?

Thanks again for the solution. Now, I need to learn/understand this for future application.

[beijing0305]

A VBA array demonstration as a beginner starter alternative :

Sub Demo1() 
  Const D = ", " 
    Dim S$(), U$(), V, W$(), L& 
        S = Split([A1].Text, D) 
        ReDim U(1 To UBound(S) + 1) 
    For Each V In S 
        If IsError(Application.Match(V, U, 0)) Then 
            W = Split(V, "-") 
            If W(0) <= W(1) Then L = L + 1: U(L) = V 
        End If 
    Next 
        If L Then ReDim Preserve U(1 To L) Else U = Split("") 
        Debug.Print Join(U, D) 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

This works as well, but I don't know why. ::lack brain power:: Appreciate if you could please put some comment/logic to help me follow your code. I will study up the Dim *$() *& suppose to be, and ReDim Preserve. Looks like working with arrays...Thanks!

[Rick Rothstein]

I know the code I posted in Message #10 is short, but I am almost certain that it works correctly. Maybe try it and see?

[XLent]

Rick, I agree -- #6, #10, and #12 will all generate the same results (bar a space or two), but I prefer your approach, given stated requirement.

[ByteMarks]

Thank you very much for the response. This is exactly the output I am expecting to see. Didn't realize I could convert string to integer using Int function. I can also follow the code for the most part. A couple of questions. Could you please help me understand your code better?

(1) cln.Add y, y: This adds the y to the collection cln, what's the second parameter "y" do?
(2) ReDim result(myCol.Count - 1): I assume this shortens the array by 1, but why is always going to be one less?

Thanks again for the solution. Now, I need to learn/understand this for future application.

Hi

cln.Add(y,y) is adding y to both the Item and Key properties. The Key property has to be unique which is why it works for removing duplicates. It's also why it's preceded by "On Error Resume Next" so that it doesn't throw an error if an attempt is made to add a duplicate key.


ReDim result(myCol.Count - 1)
The reason is that arrays are usually zero based, i.e. start at 0. Collections on the other hand start at 1
So if the collection had 10 items it'd be indexed 1-10, but the array would be 0-9. myCol.count would be 10, but the upper bound of the array would be 9, hence the -1


ReDim stands for ReDimension, so it's sizing the array to the collection. The reason for that ColToArray function is to convert the collection to an array so we can use Join to put the string back together.

[ByteMarks]

I know the code I posted in Message #10 is short, but I am almost certain that it works correctly. Maybe try it and see?

Nice compact solution. The only thing is it doesn't seem to put the original delimiter back in and if you put it in after the Then statement you end up with a leading comma?

[Rick Rothstein]

Nice compact solution. The only thing is it doesn't seem to put the original delimiter back in and if you put it in after the Then statement you end up with a leading comma?

Whoops! I forgot about that. Change the last line of code to this...

Please Login or Register  to view this content.

[beijing0305]

Try this

Please Login or Register  to view this content.

Worked perfectly! Thank you for sharing your brain power with me. Trying to break down the logic, please correct me if I miss stating...Thanks for such an elegant solution.

If Trim(x) <> "" AND _ ' if not empty
Not .exists(Trim(x)) AND _ ' if exists
(Not (Trim(x) Like "###-001") Or _ 'first pattern
Trim(x) Like "001-001") Or _ '2nd pattern
Val(Left(Trim(x), 3)) < Val(Right(Trim(x), 3)) _ '3 condition when 1st set of ### is larger than 2nd set of ###...If I have Val(Left(Trim(x), 3)) <= Val(Right(Trim(x), 3), I could eliminate the first 2 patterns above?
) Then, .Add Trim(x), Nothing

[beijing0305]

Hi

cln.Add(y,y) is adding y to both the Item and Key properties. The Key property has to be unique which is why it works for removing duplicates. It's also why it's preceded by "On Error Resume Next" so that it doesn't throw an error if an attempt is made to add a duplicate key.


ReDim result(myCol.Count - 1)
The reason is that arrays are usually zero based, i.e. start at 0. Collections on the other hand start at 1
So if the collection had 10 items it'd be indexed 1-10, but the array would be 0-9. myCol.count would be 10, but the upper bound of the array would be 9, hence the -1


ReDim stands for ReDimension, so it's sizing the array to the collection. The reason for that ColToArray function is to convert the collection to an array so we can use Join to put the string back together.

Thank you very much for the detailed explanation.

[beijing0305]

I did get an extra space in the result. Besides that, the solution is identity. Thanks.

[beijing0305]

I know the code I posted in Message #10 is short, but I am almost certain that it works correctly. Maybe try it and see?

All worked!

[kvsrinivasamurthy]

Thanks for feed back and rep.