Check out some similar questions!

excel guy · Mar 26, 2009, 03:06 AM

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.

ROLCAM · Mar 26, 2009, 05:40 AM

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

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

ScottGem · Mar 26, 2009, 05:49 AM

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.

excel guy · Mar 26, 2009, 06:17 AM

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

ScottGem · Mar 26, 2009, 09:06 AM

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.

excel guy · Mar 26, 2009, 09:36 AM

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.

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.

ScottGem · Mar 26, 2009, 09:47 AM

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.

excel guy · Mar 26, 2009, 09:56 AM

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

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.

ROLCAM · Mar 26, 2009, 12:28 PM

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

excel guy · Mar 26, 2009, 12:32 PM

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.

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

JBeaucaire · Mar 26, 2009, 02:32 PM

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.

ScottGem · Mar 26, 2009, 02:39 PM

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

JBeaucaire · Mar 26, 2009, 03:17 PM

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)

excel guy · Mar 26, 2009, 07:10 PM

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

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.

JBeaucaire · Mar 26, 2009, 09:39 PM

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.

excel guy · Mar 26, 2009, 10:21 PM

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.

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)

JBeaucaire · Mar 27, 2009, 03:12 AM

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

excel guy · Mar 27, 2009, 04:22 AM

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

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

excel guy · Mar 27, 2009, 05:49 AM

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

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.

JBeaucaire · Mar 27, 2009, 10:02 AM

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?