IF({1},___) Index returning array

**DocAElstein** · 08-17-2014, 01:05 AM

**DocAElstein** · 08-17-2014, 01:05 AM

fsfsf

**Excel Fox** · 08-21-2014, 12:01 AM

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

**Excel Fox** · 08-21-2014, 12:03 AM

For those interested to hear Jill's podcast which is being referred to in this thread, here's the video.

**DocAElstein** · 08-21-2014, 12:51 AM

Originally Posted by Excel Fox

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

**DocAElstein** · 08-21-2014, 12:51 AM

Originally Posted by Excel Fox

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

**Excel Fox** · 08-21-2014, 01:20 AM

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.

**DocAElstein** · 08-21-2014, 01:42 AM

Thanks very much. I'll try that all out and keep watching this space!

**DocAElstein** · 08-21-2014, 01:42 AM

Thanks very much. I'll try that all out and keep watching this space!

**Excel Fox** · 08-21-2014, 01:48 AM

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!