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

A

alecambo

New Member
Joined
Apr 10, 2015
Messages
35
Office Version
  1. 2021
Platform
  1. Windows
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 AString BSolution ASolution B
7-6-1-3-4-2-2DDDDDTT
7-6-1-3-4-2
DDDDDT
3-3-0-2-0-0
DDTDTT
3-0-2-0DTDT
6-8-8-13-13-11-10-14-14-7-4-7-2-1-0-3-9DDDDDDDDDDDDDDDDT6-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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
Hi, borrowing some ability from @FLuffs previous post to you in July, I came up with the following:

Book1
ABCDEFG
1String AString BSolution ASolution Bmy solution Amy solution B
27-6-1-3-4-2-2DDDDDTT7-6-1-3-4-2DDDDDT7-6-1-3-4-2DDDDDT
33-3-0-2-0-0DDTDTT3-0-2-0DTDT3-0-2-0DTDT
46-8-8-13-13-11-10-14-14-7-4-7-2-1-0-3-9DDDDDDDDDDDDDDDDT6-8-13-11-10-14-7-4-7-2-1-0-3-9DDDDDDDDDDDDDT6-8-13-11-10-14-7-4-7-2-1-0-3-9DDDDDDDDDDDDDT
Sheet2
Cell Formulas
RangeFormula
E2:E4E2=LET(textstring,TEXTSPLIT(A2,"-"),s,TRANSPOSE(SEQUENCE(COLUMNS(textstring)-1)),CONCAT(IF(INDEX(textstring,1,s)<>INDEX(textstring,1,s+1),INDEX(textstring,1,s)&"-",""),INDEX(textstring,1,COLUMNS(textstring))))
F2:F4F2=LET(textstring,TEXTSPLIT(A2,"-"),s,TRANSPOSE(SEQUENCE(COLUMNS(textstring)-1)),CONCAT(IF(INDEX(textstring,1,s)<>INDEX(textstring,1,s+1),MID(B2,s,1),""),RIGHT(B2)))


Hope it helps.

cheers
Rob
 
Upvote 0
RobP said:
Hi, borrowing some ability from @FLuffs previous post to you in July, I came up with the following:

Book1
ABCDEFG
1String AString BSolution ASolution Bmy solution Amy solution B
27-6-1-3-4-2-2DDDDDTT7-6-1-3-4-2DDDDDT7-6-1-3-4-2DDDDDT
33-3-0-2-0-0DDTDTT3-0-2-0DTDT3-0-2-0DTDT
46-8-8-13-13-11-10-14-14-7-4-7-2-1-0-3-9DDDDDDDDDDDDDDDDT6-8-13-11-10-14-7-4-7-2-1-0-3-9DDDDDDDDDDDDDT6-8-13-11-10-14-7-4-7-2-1-0-3-9DDDDDDDDDDDDDT
Sheet2
Cell Formulas
RangeFormula
E2:E4E2=LET(textstring,TEXTSPLIT(A2,"-"),s,TRANSPOSE(SEQUENCE(COLUMNS(textstring)-1)),CONCAT(IF(INDEX(textstring,1,s)<>INDEX(textstring,1,s+1),INDEX(textstring,1,s)&"-",""),INDEX(textstring,1,COLUMNS(textstring))))
F2:F4F2=LET(textstring,TEXTSPLIT(A2,"-"),s,TRANSPOSE(SEQUENCE(COLUMNS(textstring)-1)),CONCAT(IF(INDEX(textstring,1,s)<>INDEX(textstring,1,s+1),MID(B2,s,1),""),RIGHT(B2)))


Hope it helps.

cheers
Rob
Click to expand...
Hi Rob, thanks for the answer.

I'm not sure if I'm missing something, but when I try to apply the formula I get the error "The first argument of LET must be a valid name". Do you have a clue about what's happening?

Best,
alecambo
 
Upvote 0
Ciao Ale,

ah.. shoot. Its the TEXTSPLIT() function. I forgot its still in Beta testing (I have it on O365, but its not available on your office 2021 version).
I will see if there is another way I can change it.

Sorry
Rob
 
Upvote 0
RobP said:
ah.. shoot. Its the TEXTSPLIT() function.
Click to expand...
I don't think that not having TEXTSPLIT should produce that particular error message.

If TEXTSPLIT was available, what about these?

22 09 29.xlsm
ABCD
1String AString BSolution ASolution B
27-6-1-3-4-2-2DDDDDTT7-6-1-3-4-2DDDDDT
33-3-0-2-0-0DDTDTT3-0-2-0DTDT
46-8-8-13-13-11-10-14-14-7-4-7-2-1-0-3-9DDDDDDDDDDDDDDDDT6-8-13-11-10-14-7-4-7-2-1-0-3-9DDDDDDDDDDDDDT
Remove Text
Cell Formulas
RangeFormula
C2:C4C2=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))))
D2:D4D2=LET(ts,TEXTSPLIT(A2,"-"),s,SEQUENCE(COLUMNS(ts)-1),CONCAT(LEFT(B2,1),IF(INDEX(ts,s+1)=INDEX(ts,s),"",MID(B2,s+1,1))))
 
Upvote 0
Peter_SSs said:
I don't think that not having TEXTSPLIT should produce that particular error message.

If TEXTSPLIT was available, what about these?

22 09 29.xlsm
ABCD
1String AString BSolution ASolution B
27-6-1-3-4-2-2DDDDDTT7-6-1-3-4-2DDDDDT
33-3-0-2-0-0DDTDTT3-0-2-0DTDT
46-8-8-13-13-11-10-14-14-7-4-7-2-1-0-3-9DDDDDDDDDDDDDDDDT6-8-13-11-10-14-7-4-7-2-1-0-3-9DDDDDDDDDDDDDT
Remove Text
Cell Formulas
RangeFormula
C2:C4C2=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))))
D2:D4D2=LET(ts,TEXTSPLIT(A2,"-"),s,SEQUENCE(COLUMNS(ts)-1),CONCAT(LEFT(B2,1),IF(INDEX(ts,s+1)=INDEX(ts,s),"",MID(B2,s+1,1))))
Click to expand...
Hi, thanks for the answer.

I'm still getting the same error message.

Best,
alecambo
 
Upvote 0
As I imagined (but agree with you Peter in that its a little odd)

@Peter_SSs - would you happen to know, that if something is in "Beta", whether it gets rolled out to O2021 at some point, or would it only appear in O365 versions ?

Rob
 
Upvote 0
Those new functions are no longer on Beta, they have been rolled out to 365 users on the Month (Current) channel & the semi-annual channel will probably get them in Jan.
However they will never be available on xl 2021
 
Upvote 0
See what happens with this one.

22 09 29.xlsm
ABCD
1String AString BSolution ASolution B
27-6-1-3-4-2-2DDDDDTT7-6-1-3-4-2DDDDDT
33-3-0-2-0-0DDTDTT3-0-2-0DTDT
46-8-8-13-13-11-10-14-14-7-4-7-2-1-0-3-9DDDDDDDDDDDDDDDDT6-8-13-11-10-14-7-4-7-2-1-0-3-9DDDDDDDDDDDDDT
Remove Text
Cell Formulas
RangeFormula
C2:C4C2=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:D4D2=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))))
 
Upvote 0
Solution
VBA Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
Remove Duplicates with Dynamic Columns Array
Replies
0
Views
324
trough
T
C
  • Solved
Transposing Horizontal String
Replies
6
Views
130
c0087
C
P
  • Question
vba at each change
2
Replies
15
Views
696
kevin9999
K
J
  • Solved
IFS
Replies
4
Views
134
Peter_SSs
Peter_SSs
N
  • Question
Need Formula help
Replies
5
Views
379
AhoyNC
A

Forum statistics

Threads
1,215,509
Messages
6,125,216
Members
449,215
Latest member
texmansru47

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top