Remove adjacent duplicate numbers from String A and remove characters in the same position from String B.

[alecambo]

Hello,

I have two strings of letters (columns String A and String B) and I want to transform them into strings like the ones in Solution 1 and Solution 2 correspondingly.

String A	String B	Solution A	Solution B
7-6-1-3-4-2-2	DDDDDTT	7-6-1-3-4-2	DDDDDT
3-3-0-2-0-0	DDTDTT	3-0-2-0	DTDT
6-8-8-13-13-11-10-14-14-7-4-7-2-1-0-3-9	DDDDDDDDDDDDDDDDT	6-8-13-11-10-14-7-4-7-2-1-0-3-9	DDDDDDDDDDDDDT

In the case of String A, I need to remove any adjacent duplicate numbers (the numbers are separated by a "-"), so the string 6-8-8-13-13-11-10-14-14-7-4-7-2-1-0-3-9 would turn into 6-8-13-11-10-14-7-4-7-2-1-0-3-9.
String B is a little bit more complicated: both String A and String B have the same amount of items, and I want to transform String 2 depending on the transformation of String 1 into Solution 1. For example, in my data, I have 6-8-8-13-13-11-10-14-14-7-4-7-2-1-0-3-9 as String 1 and DDDDDDDDDDDDDDDDT as String 2. Solution 1 would be 6-8-13-11-10-14-7-4-7-2-1-0-3-9 (remove adjacent duplicate numbers) and Solution 2 would be DDDDDDDDDDDDDT (remove characters that are in the same position as the numbers removed from String 1).

I hope this makes sense and I'm happy to provide any further clarification.

Best,
alecambo

 

[alecambo]

See what happens with this one.

22 09 29.xlsm
	A	B	C	D
1	String A	String B	Solution A	Solution B
2	7-6-1-3-4-2-2	DDDDDTT	7-6-1-3-4-2	DDDDDT
3	3-3-0-2-0-0	DDTDTT	3-0-2-0	DTDT
4	6-8-8-13-13-11-10-14-14-7-4-7-2-1-0-3-9	DDDDDDDDDDDDDDDDT	6-8-13-11-10-14-7-4-7-2-1-0-3-9	DDDDDDDDDDDDDT
Remove Text

Cell Formulas
Range	Formula
C2:C4	C2	=LET(n,LEN(A2)-LEN(SUBSTITUTE(A2,"-","")),s,SEQUENCE(n),ss,SEQUENCE(n+1),ts,TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",100)),ss*100-99,100)),TEXTJOIN("-",1,INDEX(ts,1),IF(INDEX(ts,s+1)=INDEX(ts,s),"",INDEX(ts,s+1))))
D2:D4	D2	=LET(n,LEN(A2)-LEN(SUBSTITUTE(A2,"-","")),s,SEQUENCE(n),ss,SEQUENCE(n+1),ts,TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",100)),ss*100-99,100)),CONCAT(LEFT(B2,1),IF(INDEX(ts,s+1)=INDEX(ts,s),"",MID(B2,s+1,1))))

Sorry for the late reply.

I'm still getting the same error message.

Best,
alecambo

 

[alecambo]

Sub test()

    For i = 2 To Cells(Rows.Count, 1).End(3).Row
   
        bol = Split(Cells(i, 1).Value, "-")
        yeni = Mid(Cells(i, 2).Value, 1, 1)
        For ii = 1 To UBound(bol)
            If bol(ii - 1) = bol(ii) Then
                ekle = ""
                bol(ii) = ""
            Else
                ekle = Mid(Cells(i, 2).Value, ii + 1, 1)
            End If
            yeni = yeni & ekle
        Next ii
        Cells(i, 5).Value = Replace(WorksheetFunction.Trim(Join(bol, " ")), " ", "-")
        Cells(i, 6).Value = yeni
   
    Next i
End Sub

Thanks for the reply. Your code works but it only collapses double characters (9-9 to 9) but it does not work for triple characters (9-9-9 to 9-9)

 

[Peter_SSs]

I'm still getting the same error message.

Could you be using a regional Excel version that uses ; instead of , as the function argument separator?

What happens if you try each of these for the Solution A question?

=LET(ts;TEXTSPLIT(A2;"-");s;SEQUENCE(COLUMNS(ts)-1);TEXTJOIN("-";1;INDEX(ts;1);IF(INDEX(ts;s+1)=INDEX(ts;s);"";INDEX(ts;s+1))))

=LET(n;LEN(A2)-LEN(SUBSTITUTE(A2;"-";""));s;SEQUENCE(n);ss;SEQUENCE(n+1);ts;TRIM(MID(SUBSTITUTE(A2;"-";REPT(" ";100));ss*100-99;100));TEXTJOIN("-";1;INDEX(ts;1);IF(INDEX(ts;s+1)=INDEX(ts;s);"";INDEX(ts;s+1))))

 

[RobP]

<oops> edit - I see he feedback.. my mistake - ignore below.

Hi Pete, I think this is not the case - as @Fluff previous code here: Remove adjacent duplicate characters from String A and remove characters in the same position from String B. worked just find for him.

I do believe though he has missed your last proposal of formulas (which arrived before the VBA code came from Veyselemre.

Probably worth him giving that a try first ?

Rob

 

[Peter_SSs]

Hi Pete, I think this is not the case - as @Fluff previous code here: Remove adjacent duplicate characters from String A and remove characters in the same position from String B. worked just find for him.

Yes, I had seen some of the OP's previous threads where that did not seem to be the issue. However, so far, the only way that I have been able to reproduce the particular error reported by the OP is to change commas to semicolons in my version so though the question was worth asking.

 

[alecambo]

Could you be using a regional Excel version that uses ; instead of , as the function argument separator?

What happens if you try each of these for the Solution A question?

=LET(ts;TEXTSPLIT(A2;"-");s;SEQUENCE(COLUMNS(ts)-1);TEXTJOIN("-";1;INDEX(ts;1);IF(INDEX(ts;s+1)=INDEX(ts;s);"";INDEX(ts;s+1))))

=LET(n;LEN(A2)-LEN(SUBSTITUTE(A2;"-";""));s;SEQUENCE(n);ss;SEQUENCE(n+1);ts;TRIM(MID(SUBSTITUTE(A2;"-";REPT(" ";100));ss*100-99;100));TEXTJOIN("-";1;INDEX(ts;1);IF(INDEX(ts;s+1)=INDEX(ts;s);"";INDEX(ts;s+1))))

Hi, thanks for your reply.

The first solution gives me a #NAME? error.
The second solution seems to work perfectly fine

I didn't miss the post right before veyselemre's VBA, I also tried it and got the "The first argument of LET must be a valid name" message.

Best,
alecambo

 

[Peter_SSs]

The second solution seems to work perfectly fine

Great news! So you should be able to use both the suggestions from post #9 if you swap all the commas for semicolons.

.. and you need to remember that swap for any future suggestions in the forum - that mostly will use commas.

 

[Peter_SSs]

The second solution seems to work perfectly fine

Great news! So you should be able to use both the suggestions from post #9 if you swap all the commas for semicolons.

.. and you need to remember that swap for any future suggestions in the forum - that mostly will use commas.

 

[RobP]

... would be interested to know if Fluffs previous code is still working for you (on those items that do not have the "-" between them though ?

Rob

 

[alecambo]

Great news! So you should be able to use both the suggestions from post #9 if you swap all the commas for semicolons.

.. and you need to remember that swap for any future suggestions in the forum - that mostly will use commas.

Thanks! I've already marked the post as the solution