Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   How to separate the strings alphabets one by one without having any duplicate (https://www.askmehelpdesk.com/showthread.php?t=334065)

  • Mar 26, 2009, 03:06 AM
    excel guy
    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.
  • Mar 26, 2009, 05:40 AM
    ROLCAM

    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

    ##############################
  • Mar 26, 2009, 05:49 AM
    ScottGem

    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.
  • Mar 26, 2009, 06:17 AM
    excel guy
    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
  • Mar 26, 2009, 09:06 AM
    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.
  • Mar 26, 2009, 09:36 AM
    excel guy
    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.

  • Mar 26, 2009, 09:47 AM
    ScottGem

    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.
  • Mar 26, 2009, 09:56 AM
    excel guy
    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.

  • Mar 26, 2009, 12:28 PM
    ROLCAM
    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
  • Mar 26, 2009, 12:32 PM
    excel guy
    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

  • Mar 26, 2009, 02:32 PM
    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.
  • Mar 26, 2009, 02:39 PM
    ScottGem
    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
  • Mar 26, 2009, 03:17 PM
    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)
  • Mar 26, 2009, 07:10 PM
    excel guy
    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.

  • Mar 26, 2009, 09:39 PM
    JBeaucaire

    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.
  • Mar 26, 2009, 10:21 PM
    excel guy
    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)

  • Mar 27, 2009, 03:12 AM
    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

  • Mar 27, 2009, 04:22 AM
    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.

    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


  • Mar 27, 2009, 05:49 AM
    excel guy
    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.

  • Mar 27, 2009, 10:02 AM
    JBeaucaire

    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?

  • All times are GMT -7. The time now is 07:36 AM.