Ask Experts Questions for FREE Help !
Ask
    excel guy's Avatar
    excel guy Posts: 10, Reputation: 1
    New Member
     
    #1

    Mar 26, 2009, 03:06 AM
    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.
    ROLCAM's Avatar
    ROLCAM Posts: 1,420, Reputation: 23
    Ultra Member
     
    #2

    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's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #3

    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's Avatar
    excel guy Posts: 10, Reputation: 1
    New Member
     
    #4

    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's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #5

    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's Avatar
    excel guy Posts: 10, Reputation: 1
    New Member
     
    #6

    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.

    Quote Originally Posted by ScottGem View Post
    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's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #7

    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's Avatar
    excel guy Posts: 10, Reputation: 1
    New Member
     
    #8

    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
    Quote Originally Posted by ScottGem View Post
    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's Avatar
    ROLCAM Posts: 1,420, Reputation: 23
    Ultra Member
     
    #9

    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's Avatar
    excel guy Posts: 10, Reputation: 1
    New Member
     
    #10

    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.

    Quote Originally Posted by ROLCAM View Post
    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's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #11

    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's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #12

    Mar 26, 2009, 02:39 PM
    Quote Originally Posted by JBeaucaire View Post
    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's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #13

    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's Avatar
    excel guy Posts: 10, Reputation: 1
    New Member
     
    #14

    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
    Quote Originally Posted by JBeaucaire View Post
    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's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #15

    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's Avatar
    excel guy Posts: 10, Reputation: 1
    New Member
     
    #16

    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.


    Quote Originally Posted by JBeaucaire View Post
    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's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #17

    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's Avatar
    excel guy Posts: 10, Reputation: 1
    New Member
     
    #18

    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.

    Quote Originally Posted by JBeaucaire View Post
    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's Avatar
    excel guy Posts: 10, Reputation: 1
    New Member
     
    #19

    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




    Quote Originally Posted by excel guy View Post
    This is really superb! Thank you very much for such enormous help.
    I will never forget this huge help. Thanks a lot really.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #20

    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?

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Compare strings [ 3 Answers ]

How can I compare two strings without using strcmp() function?

No Strings Relationship [ 4 Answers ]

I am sleeping with a guy, we are having I guess what you can call no strings sex. I really like him which I guess is my reason for sleeping with him, deep down I am hoping he will fall for me. Am I wasting my time and does he have any respect for me.

Is no strings attached sex OK [ 4 Answers ]

I just split up with my boyfriend and I have been in touch with a lad from school who I always had a soft spot for but we were good friends. He always used to ask me about sex and stuff at school and he said its because he wanted me. That wa 4 years ago. We have just been talking and he was...

Sex with no strings? Impossible! [ 15 Answers ]

Ok, now I'm really confused! As some of you know I recently told my ex-girlfriend how I wanted to start seeing her again and possibly get back together. She told me she still thought about me all the time and missed me, however she was enjoying her freedom and being single right now. This weekend...


View more questions Search