Page 1 of 3 123 LastLast
Results 1 to 10 of 27

Thread: Concatenating your Balls

  1. #1

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    DocAElstein, welcome to the ExcelFox community.

    The text to column feature in Excel is used to split text based on a delimiter, like spaces, commas (,) etc. I am almost certain that it doesn't work the other way round.

    Now, coming to your specific query about Jill referring to the Text To Column feature to convert the comma separated ball numbers for each day, in to different rows, I assure you that was just one part of the conversion. In the interest of time, and to keep that separate from the overall objective of the podcast, Jill certainly omitted to showcase the entire method he adopted to convert the data to what he was showing in that podcast video.

    What he would have done is, after pasting the comma separated data from MegaMillions site, split the data in to separate columns using Text To Column, and then used some formulae or VBA to make all data in to columns. That's my reading, and a lot of other Excel experts would agree. But, with due credit to Jill, I wish and hope that I am wrong
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    For those interested to hear Jill's podcast which is being referred to in this thread, here's the video.

    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Quote Originally Posted by Excel Fox View Post
    DocAElstein, welcome to the ExcelFox community.

    The text to column feature in Excel.................
    Hi,
    Thanks very much for replying. Happy to be here in the Forum!.
    . I was mainly interested in a reply from Rick to my specific question on a line in his code as I had asked in #15 from the MrExcel thread ( Multiple Columns into Single Column using Data, Text to Column ), as I had not managed to catch him there.

    . But nevertheless Thanks for the interesting infomation. I expect your infomation would have been of particular interest to the initiator of that Thread. (I could possibly referrence him here, but I am not too sure about the Forum Rules about that sort of thing? Maybe you can advise me on that one?)


    . I had simply replied in that MrExcel Thread in parralel with Rick to that thread and had asked him for some clarification of one line in his code. Unfortunately it is difficult to get in touch with him.
    . Thanks again.
    . Hope I can be active as well in the future in this forum!

    Alan Elston
    Bavaria,
    Germany

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    OK. The conditional evaluation returns an array of values, whereas a direct non-conditional evaluation would return a single value 'in some cases'. There could be a valid theory behind that, but that is not something I am privy to. It's probably the way EVALUATE function works. So anyway, to understand that difference a bit more clearly, you can test the following to codes. Both are actually trying to do the same time, but since the evaluate function 'in some cases' only returns a single value in the second code, the entire output in A1:A10 becomes the same. Having said that, in this case below, both the codes seem to be working fine in some systems, and not in some other systems. I will get back to you with a suitable example.

    Code:
    Range("A1:A10") = Evaluate("IF(1," & Range("A1:z10").Columns(2).Address & "&"" - ""&" & Range("A1:z10").Columns(3).Address & "&"" - ""&" & Range("A1:z10").Columns(4).Address & ")")
    Code:
    Range("A1:A10") = Evaluate(Range("A1:z10").Columns(2).Address & "&"" - ""&" & Range("A1:z10").Columns(3).Address & "&"" - ""&" & Range("A1:z10").Columns(4).Address)
    And by the way, you don't necessarily need to use ROW()

    Rick just used that as a means to move the IF function's execution to the TRUE condition. Now, you would know that anything except 0 (zero) means TRUE in the Excel boolean world. And since the minimum value of ROW() is greater than 0 (yes, it's 1 and above), using ROW(), or using 1, has the same effect in the IF condition.

    Hope this makes it clear.

    EDIT: Will get back with more clarity.
    Last edited by Excel Fox; 08-21-2014 at 01:31 AM. Reason: Information modified/manipulated a bit
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #6
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Thanks very much. I'll try that all out and keep watching this space!

  7. #7
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Here's another sample by Vishesh. There also he is using a similar logic. But when I tested it on my machine, the code works fine even if I remove the IF condition.

    Evaluate (VBA) for Concatenation | ExcelExperts.com

    Let me investigate that!
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  8. #8
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    OK, here's another thread that confirms my claim. Phew!

    VBA Trick of the Week :: Avoid Loop for Range Calculations – Evaluate | Useful Gyaan

    Specifically, read the below excerpt


    Suppose we’ve some text values in range A1:A10 and we want to extract and keep only the first three letters of the values in all cells of this range. We could try to do so using the below code:
    Code:
        Set rngData = ThisWorkbook.Worksheets("Sheet1").Range("A1:B10")
        rngData = Evaluate("Left(" & rngData.Address & ",3)")
    BUT, you’ll find it does not work. It will fill the whole range with first 3 letters of cell A1. The reason is that if the Excel function used in Evaluate does not accept an array, the Evaluate function will not return an array. So in order to make this function return an array we need to modify the code slightly like this:

    Code:
        Set rngData = ThisWorkbook.Worksheets("Sheet1").Range("A1:A10")
        rngData = Evaluate("if(Row(1:10),left(" & rngData.Address & ",3))")
    In this case, ROW(1:10) returns an array of numbers from 1 to 10. Any numeric value other than 0 returned from a logical function is considered as TRUE, So there are 10 vertical TRUE values. For each TRUE, it will return the corresponding cell’s value from A1:A10.
    And the reason why it was working in the previous examples was probably because we weren't using any other function within the EVALUATE function. Hopefully, this makes more sense.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  9. #9
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    By the way, you can reference this thread to the OP at the other forum, as long as you feel it is relevant, and is not intended to manipulate traffic. Hope that's as straight as it gets.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  10. #10
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Wow, great, Thanks for all that info. - I'll work me way throught it now (or maybe tomorrow night in my daily "VBA Hour" - It is nearly bed-time now here in Bavaria)

    Thanks again
    Alan

    .P.s....
    By the way, you can reference this thread to the OP at the other forum, as long as you feel it is relevant, and is not intended to manipulate traffic. Hope that's as straight as it gets.
    ...OK....(should be OK - Rick referrences this Forum in all his replies!)
    Last edited by DocAElstein; 08-21-2014 at 03:17 AM. Reason: used Code tags instead of Quote tags!!

Similar Threads

  1. Replies: 3
    Last Post: 02-24-2014, 05:48 AM
  2. Replies: 6
    Last Post: 07-26-2013, 11:42 AM
  3. Replies: 3
    Last Post: 05-23-2013, 11:17 PM
  4. Replies: 7
    Last Post: 05-09-2013, 11:16 PM
  5. Converge Data From Multiple Columns To Single Column
    By ayazgreat in forum Excel Help
    Replies: 3
    Last Post: 12-14-2012, 10:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •