Ask Me Help Desk - How to separate the strings alphabets one by one without having any duplicate

How to separate the strings alphabets one by one without having any duplicate

Hi,

I need to do two things in Excel.

Say I have a phrase "This is Excel 2003". I want that:

1. I scatter the phrase letters one by one. For example in A1 it fills "T" and B2 "H", and C2 "i", D2 "S", and so on until it writes in O1 "3" as it's the last word as there are total 15 characters excluding the spaces.

2. Then, I want that the same way, in the next row it fills the same phrase characters one by one, but skipping the duplicate, i.e. now it will enter "T H I S E X C L 2 0 3" one by one, because IS and E and one zero is repeated.

Can anybody help ?

Thank you.

This is all done in EXCEL.

The example I used is the heading of your
Question.

Just take it in stages:-

How to separate the strings alphabets one by one without having any duplicate

H
O
W
T
O
S
E
P
A
R
A
T
E
T
H
E
S
T
R
I
N
G
S
A
L
P
H
A
B
E
T
S
O
N
E
B
Y
O
N
E
W
I
T
H
O
U
T
H
A
V
I
N
G
A
N
Y
D
U
P
L
I
C
A
T
E
______________________________________
Just list and number.

1 H
2 O
3 W
4 T
5 O
6 S
7 E
8 P
9 A
10 R
11 A
12 T
13 E
14 T
15 H
16 E
17 S
18 T
19 R
20 I
21 N
22 G
23 S
24 A
25 L
26 P
27 H
28 A
29 B
30 E
31 T
32 S
33 O
34 N
35 E
36 B
37 Y
38 O
39 N
40 E
41 W
42 I
43 T
44 H
45 O
46 U
47 T
48 H
49 A
50 V
51 I
52 N
53 G
54 A
55 N
56 Y
57 D
58 U
59 P
60 L
61 I
62 C
63 A
64 T
65 E

______________________________________sort and eliminate the duplicates.

9 A
29 B
62 C
57 D
7 E
22 G
1 H
20 I
25 L
21 N
2 O
8 P
10 R
6 S
4 T
46 U
50 V
3 W
37 Y

______________________________________List the left overs and number again.
65 rows have become 19.
Sort on the original number.

1 H 1
2 O 2
3 W 3
4 T 4
6 S 5
7 E 6
8 P 7
9 A 8
10 R 9
20 I 10
21 N 11
22 G 12
25 L 13
29 B 14
37 Y 15
46 U 16
50 V 17
57 D 18
62 C 19

##############################

Can I ask WHY you need to do this? That might help me provide a solution for you.

You could use ROLCAM's solution, but that's a manual process and I am suspecting you want to do this automatically with any phrase entered.

Hi ROLCAM,

Many thanks for your efforts. But, unfortunately, yes I do automatic solution plus I want to spread the solution horizontally, right away can not spread it vertically.

I am trying to make a program in Excel which will automatically give a solution as per Occult Sciences, and I have been working on this for last 13 years. Now I am thinking to make it ready in a software form, but I can not reveal my secrets upon any one, and fortunately I almost know all other this solution programming as per the Excel formulas except first three things. The two which I have asked here, and the one will be asked after having it's solution. I believe I will not need any further assistance for this my personal program because at least I can make this happen by clicking a button in Excel (in Macros). Could you please help me out throughout the program (only to the extent I can not do myself) or at least for these two questions.

Best Regards,
excel guy

OK, I'm not the best at Excel VBA, but that's what you are going to need to get this done. What you will need to do is loop through the range of columns that contain your phrase. As you loop you will have to maintain an array of letters used. When the loop encounters a letter already in the array, then its skips that letter, otherwards it adds it to the destination range in the next column.

Thank you, Scott!

Can you please give me an example, considering the phrase "Will my dream of having it come true". I will copy your example to learn. Please note that I need it in two steps:

1. Spread the characters horizontally first like "W in A1 I in B1 l in C1 l in D1... and so on till end.
2. In the next row, it does the same but without any repeated word. (The sequence should not be broken).

Thanks so much!

excel guy.

Quote:

Originally Posted by ScottGem

OK, I'm not the best at Excel VBA, but that's what you are going to need to get this done. What you will need to do is loop through the range of columns that contain your phrase. As you loop you will have to maintain an array of letters used. When the loop encounters a letter already in the array, then its skips that letter, otherwards it adds it to the destination range in the next column.

Sorry, but I'm not that well versed in Excel VBA to write viable code. I can give you some pseudo code that might do the job to get you going.

Do While I <= Len(A1)
CurrChar = Mid(A1,I,1)
CurrCell1 = CurrChar
For x = 1 -26
If Array(z,x) = CurrChar
Exit For
Else
Array(z,x) = CurrChar
CurrChar2=CurrChar
End If
Next x
I = I +1
Loop
Disclaimer this is "aircode" and may not be syntactically correct, but it should give you the direction you need. Also remember to use your own names for objects.

You will have to figure out how to determine what cell the currchar goes into for each row. But the code should loop through a phrase entered in cell A1 and parse out each character, then enter the unique letters in another row.

Sorry, Scott. I am also not good at VBA either. I am looking for some simple solutions.

Thanks a lot anyway.

Best Regards,
excel guy

Quote:

Originally Posted by ScottGem

Sorry, but I'm not that well versed in Excel VBA to write viable code. I can give you some psuedo code that might do the job to get you going.

Do While i <= Len(A1)
CurrChar = Mid(A1,i,1)
CurrCell1 = CurrChar
For x = 1 -26
If Array(z,x) = CurrChar
Exit For
Else
Array(z,x) = CurrChar
CurrChar2=CurrChar
End If
Next x
i = i +1
Loop
Disclaimer this is "aircode" and may not be syntactically correct, but it should give you the direction you need. Also remember to use your own names for objects.

You will have to figure out how to determine what cell the currchar goes into for each row. But the code should loop through a phrase entered in cell A1 and parse out each character, then enter the unique letters in another row.

Here are your two steps:-

Will my dream of having it come true

W I L L M Y D R E A M O F H A V I N G I T C O M E T R U E

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

W L M Y D R E A O F H V N G T C U

1 3 5 6 7 8 9 10 12 13 14 16 18 19 21 22 28

16 8 9 17 3 12 4 1 11 5 7 15 10 6 13 2 14

Sorry, ROLCAM. I just need a formula or function as there are many chances of errors if we do manually, also the procedure consists 8 Steps, and this is the first step, and the most important for me, as others seem possible.

I am learning VBA from the scratch now, may be I reach till that point ever :)

Thanks so much for your kind efforts.

Quote:

Originally Posted by ROLCAM

Will my dream of having it come true

W I L L M Y D R E A M O F H A V I N G I T C O M E T R U E

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

W L M Y D R E A O F H V N G T C U

1 3 5 6 7 8 9 10 12 13 14 16 18 19 21 22 28

16 8 9 17 3 12 4 1 11 5 7 15 10 6 13 2 14

A little fun with AutoFilter gets it done without having to analyze each cell ourselves:

Code:

Sub LetterList() Range("A1", Range("A1").End(xlToRight)).Copy Range("HZ1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Range("HZ1:HZ" & Range("HZ1").End(xlDown).Row).AdvancedFilter _ Action:=xlFilterCopy, CopyToRange:=Range("IA1"), Unique:=True Range("HZ:HZ").ClearContents Range("IA1", Range("IA1").End(xlDown)).Copy Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=True Range("IA:IA").ClearContents Range("A1").Select End Sub

This is designed to work with row 1 only... results in row 2 as originally described.

Quote:

Originally Posted by JBeaucaire

A little fun with AutoFilter gets it done without having to analyze each cell ourselves:

I was wondering when you would show up ;)

That's a neat solution, but does it assume that the phrase was typed into each cell in row 1, so Row2 not has the unique letters? If so, that may not quite satisfy the requirements. I got the impression that the phrase is type into ONE cell and he wants the function to parse it out into 2 rows, one with each letter in a column the other will a list of unique letters. I could be wrong though

You are correct. I misread it when he said "I scatter the phrase letters"...

If that is the case and the sentence is in cell A1 altogether, then use this version.

Code:

Sub LetterList() Dim lgth As Long lgth = Len(Range("A1")) lgth = Len(txt) 'Parse original text Range(Cells(2, 1), Cells(2, lgth)).FormulaR1C1 = "=MID(SUBSTITUTE(R1C1,"" "",""""),COLUMN(),1)" Range(Cells(1, 1), Cells(1, lgth)).Value = Range(Cells(2, 1), Cells(2, lgth)).Value Range(Cells(2, 1), Cells(2, lgth)).ClearContents 'Autofilter the letters Range("A1", Range("A1").End(xlToRight)).Copy Range("HZ1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Range("HZ1:HZ" & Range("HZ1").End(xlDown).Row).AdvancedFilter _ Action:=xlFilterCopy, CopyToRange:=Range("IA1"), Unique:=True Range("HZ:HZ").ClearContents Range("IA1", Range("IA1").End(xlDown)).Copy Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=True Range("IA:IA").ClearContents Range("A1").Select End Sub

I'm sure there is far more elegant code to written, but this at least works... (grin)

This is really superb, JBeaucaire. Thank you so much.
But, I have felt that this works with my step two, i.e. it only filters. Can you please let me know if we write our question or phrase in and it does it automatically or at least first it separates them. For example I write:
This is Microsoft excel (say in A1)
Now it itself does it in next row like this "T h i s i s m i c r o s o f t e x c e l" and then it goes to the code you have provided with.

I really am glad that you are genuinely rocket scientist.

Best Regards,
excel guy

Quote:

Originally Posted by JBeaucaire

A little fun with AutoFilter gets it done without having to analyze each cell ourselves:

Code:

Sub LetterList() Range("A1", Range("A1").End(xlToRight)).Copy Range("HZ1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Range("HZ1:HZ" & Range("HZ1").End(xlDown).Row).AdvancedFilter _ Action:=xlFilterCopy, CopyToRange:=Range("IA1"), Unique:=True Range("HZ:HZ").ClearContents Range("IA1", Range("IA1").End(xlDown)).Copy Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=True Range("IA:IA").ClearContents Range("A1").Select End Sub

This is designed to work with row 1 only...results in row 2 as originally described.

Did you run the macro to see how it behaves? (grin)

Put anything you want in A1. Run the macro and the result will be the two rows laid out the way you described in post #1.

It's showing Debugger and the formula is not working here, sorry.
For example please type in "Will my dream of having it come true" in A1.

Quote:

Originally Posted by JBeaucaire

You are correct. I misread it when he said "I scatter the phrase letters"...

If that is the case and the sentence is in cell A1 altogether, then use this version.

Code:

Sub LetterList() Dim lgth As Long lgth = Len(Range("A1")) lgth = Len(txt) 'Parse original text Range(Cells(2, 1), Cells(2, lgth)).FormulaR1C1 = "=MID(SUBSTITUTE(R1C1,"" "",""""),COLUMN(),1)" Range(Cells(1, 1), Cells(1, lgth)).Value = Range(Cells(2, 1), Cells(2, lgth)).Value Range(Cells(2, 1), Cells(2, lgth)).ClearContents 'Autofilter the letters Range("A1", Range("A1").End(xlToRight)).Copy Range("HZ1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Range("HZ1:HZ" & Range("HZ1").End(xlDown).Row).AdvancedFilter _ Action:=xlFilterCopy, CopyToRange:=Range("IA1"), Unique:=True Range("HZ:HZ").ClearContents Range("IA1", Range("IA1").End(xlDown)).Copy Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=True Range("IA:IA").ClearContents Range("A1").Select End Sub

I'm sure there is far more elegant code to written, but this at least works... (grin)

Debuggers always highlight the problem line, so be sure to provide that info when posting.

Slight update, might run more cleanly for you:

Code:

Option Explicit Sub LetterList() Dim lgth As Long, txt As Range Set txt = Range("A1") lgth = Len(txt) 'Parse original text Range(Cells(2, 1), Cells(2, lgth)).FormulaR1C1 = "=MID(SUBSTITUTE(R1C1,"" "",""""),COLUMN(),1)" Range(Cells(1, 1), Cells(1, lgth)).Value = Range(Cells(2, 1), Cells(2, lgth)).Value Range(Cells(2, 1), Cells(2, lgth)).ClearContents 'Autofilter the letters Range("A1", Range("A1").End(xlToRight)).Copy Range("HZ1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Range("HZ1:HZ" & Range("HZ1").End(xlDown).Row).AdvancedFilter _ Action:=xlFilterCopy, CopyToRange:=Range("IA1"), Unique:=True Range("HZ:HZ").ClearContents Range("IA1", Range("IA1").End(xlDown)).Copy Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=True Range("IA:IA").ClearContents Range("A1").Select End Sub

This is really superb! Thank you very much for such enormous help.
I will never forget this huge help. Thanks a lot really.

Quote:

Originally Posted by JBeaucaire

Debuggers always highlight the problem line, so be sure to provide that info when posting.

Slight update, might run more cleanly for you:

Code:

Option Explicit Sub LetterList() Dim lgth As Long, txt As Range Set txt = Range("A1") lgth = Len(txt) 'Parse original text Range(Cells(2, 1), Cells(2, lgth)).FormulaR1C1 = "=MID(SUBSTITUTE(R1C1,"" "",""""),COLUMN(),1)" Range(Cells(1, 1), Cells(1, lgth)).Value = Range(Cells(2, 1), Cells(2, lgth)).Value Range(Cells(2, 1), Cells(2, lgth)).ClearContents 'Autofilter the letters Range("A1", Range("A1").End(xlToRight)).Copy Range("HZ1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Range("HZ1:HZ" & Range("HZ1").End(xlDown).Row).AdvancedFilter _ Action:=xlFilterCopy, CopyToRange:=Range("IA1"), Unique:=True Range("HZ:HZ").ClearContents Range("IA1", Range("IA1").End(xlDown)).Copy Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=True Range("IA:IA").ClearContents Range("A1").Select End Sub

Now, can we kindly move towards the next step I mentioned in my post:

Now I want that the row which is having the duplicated eliminated (Unique:=True), should work in this sequence and I want it to repeat the process in next two lines.
For Example I have got from I am a boy:
I a m b o y
Now I want that Excel displace it like this:

say we give them numbers: I (1) a(2) m(3) b(4) o(5) y(6)
So, in first cell it places I in the second y (last one) third a (second one) forth o (second last) and so on.

As a result it will be like this:

I a m b o y(this is the second row we already have solved)
I y a o m b
I b y m a o

Quote:

Originally Posted by excel guy

This is really superb! Thank you very much for such enormous help.
I will never forget this huge help. Thanks a lot really.

Why are you doing this? This seems pointless and it is a time intensive project that you don't seem to be applying any of your own methods/formula to "move towards".

So what is this all about?

You have to have all that data in that specific format? Or are you just looking for randomized set of the second data?