MyBets

[U.F.O]

Is there an option to sort the Mybets info in excel,
so that bets stay in the order that they were placed.

at the moment they move around when bets are matched etc and it would be easier to achieve what i want if they stayed still .

[U.F.O]

Anyone ?

i need to stop the bets changing order in my bets when they are matched / unmatched and stay in the order they were placed.

[U.F.O]

Just to add ive tried the rank function on the bet ref, finding the number lowest first ,

but it doesn't return the correct results when the order is changed when connected to gruss.

[GaryRussell]

Sorry, there isn't an option. Are they needed in the same order to match against other data or another reason? There might be another solution depending on exactly why you need them in the same order.

[U.F.O]

Basically Gary im placing multiple back bets manually on the ladder and then when they are matched I am wanting various lay bets at different odds placed automatically by excel.

i am using a variation of the multiple trigger example spread sheet I downloaded from the sample spreadsheet section,
I have assigned a row on the selections sheet for each back bet I place in time order first to last so lay bets can then be fired when the corresponding back bet is matched by use of a lookup formula.

For example 1st row places lay bets when the first back bet placed is matched, 2nd row for the second and so on.

Ive got it all working perfectly, but I have to at the moment manually copy the bet refs of the back bets placed and sort then into the order of the time placed. ive tried to do this by automatically ranking the bet refs smallest to biggest but carn't get it to work

Hope this make sense.

[U.F.O]

Ok on further investigation it seems to be the lookup formula that's not working properly.

i have the bet ref in cell L7 in this example

=LOOKUP(L7, MyBets!$A$2:$A$500, MyBets!$E$2:$E$500)

it returns U when bet is unmatched

but when it get's matched it displays NA
instead of F as expected .

[U.F.O]

#N/A 23105452186
U 23105452406
U 23105452732
U 23105453105
F 23105453264



A recent example, the only bet that has been matched is the one with NA
next to it, but an F has somehow appeard on a bet that is not matched.

if works if you do it manually but when mybet changes the order as bets are matched it doesn't seem to update correctly.

[GaryRussell]

The problem is that lookup requires the bet refs to be in order. You can use vlookup which has the option to not have it sorted.

The following vlookup should work. The FALSE parameter means it does not have to be sorted.

Code: Select all

=VLOOKUP(L7,MyBets!$A$2:$A$500,5,FALSE)

You should also be aware that if it's partially matched then the bet can appear twice, once with F and once with U. You can use countif to determine how many times it appears. If it appears once with F then it's fully matched.

eg.

Code: Select all

=COUNTIF(MyBets!$A$2:$A$500,L7)

In summary if the above vlookup returns F and countif returns 1 then it's fully matched.

[U.F.O]

Thanks Gary but i just get REF with that Vlookup formula


If I change it to ,1, as shown below it returns the same bet ref
number anything else returns REF

=VLOOKUP(L7,MyBets!$A$2:$A$500,1,FALSE)

[GaryRussell]

Sorry, I somehow copied the range from your formula wrong. It should be

Code: Select all

=VLOOKUP(L7,MyBets!$A$2:$E$500,5,FALSE)

[U.F.O]

Thanks Gary that works now

i really should have picked that up for myself especially as i had already established it worked for column A.

Ive got the spreadsheet working as i wanted now,

thanks for your help