Hi,
Im sure some of you have used the index match function when you downloaded the Betfair Promo racing data to place all relevant data on a race into a single Row.
Apart from the ninjas many of you will also dislike the index- match function.Finally we have a new simple function that can replace the index match when used with the also new unique function. I thought id share the relevant usage which I picked up from a utube video by the xl ninja Leila Gharani.In fact the video showed 2 ways to do the same thing the first so complicated (worse than index match) ,it would have taken me years to understand. In contrast the second function TEXTJOIN is ridiculously easy to use.
The code is =TEXTJOIN(",",TRUE,IF($A$5:$A$1100=$S5,ROUND($O$5:$O$1100,2),""))
The A's were the EVENT ID CODES, S5 from the unique list of those codes and the O,s were the prices, The round is not a normal part of the formula but I had to use to round the prices from about 9 decimal places.
The data outputs with CSE in array form in one cell which you can distribute to adjoining cells with text to columns after using copy 123 first. Heres the link to that utube tutorial
https://www.youtube.com/watch?v=fDB1Ktyhp3Y The video is titled RETURN MULTIPLE MATCH RESULTS IN EXCEL 2 METHODS)
I thought I would share what was for me a eureka moment.
New simple Xl function to replace index match
-
spreadbetting
- Posts: 3140
- Joined: Sun Jan 31, 2010 8:06 pm
I think as soon as I started learning a little VBA and found you could write your own custom functions it really opened excel for me.
-
Dr Ginhog
- Posts: 260
- Joined: Wed Nov 03, 2010 12:10 am
- Location: Ballygarvan,Cork Ireland T12D2VR
- Contact:
Ditto Betangel. The existence of Macros where the VBA is self created makes to some degree VBA not needed. We get by with
copy pasting the VBA of others. If only we had time to do it all. I imagine knowing more of VBA gives you a different perspective. Macros
have many limitations.
BTW Excel with the new changes have just pipped BA to the best program ever created. Its back to the BA team now!
copy pasting the VBA of others. If only we had time to do it all. I imagine knowing more of VBA gives you a different perspective. Macros
have many limitations.
BTW Excel with the new changes have just pipped BA to the best program ever created. Its back to the BA team now!
-
Dr Ginhog
- Posts: 260
- Joined: Wed Nov 03, 2010 12:10 am
- Location: Ballygarvan,Cork Ireland T12D2VR
- Contact:
I noticed in the TEXTJOIN formula if the lookup list length is not made the same as the unique list length (which they would not bet normally) it fails to work.If i remember correctly you get an error message?
Its worth noting also the lack of CPU usage in the TEXTJOIN workaround function compared to index match where the formulas had to exist in many more multiple cells. In fact that part of index-match was a nightmare and you had to always immediately convert from formulas to
values. If you had multiple sheets open forget it, the sheets would hang for some time even with a half decent rig.
Its worth noting also the lack of CPU usage in the TEXTJOIN workaround function compared to index match where the formulas had to exist in many more multiple cells. In fact that part of index-match was a nightmare and you had to always immediately convert from formulas to
values. If you had multiple sheets open forget it, the sheets would hang for some time even with a half decent rig.
