How to transfer data from another sheet, but with conditions

honkin

Active Member
Joined
Mar 20, 2012
Messages
277
Office Version
  1. 2016
Platform
  1. MacOS
Hi

I have a rather major issue which has just arisen and I am hoping there is a solution.

A few days after the end of each month, the data results file is downloaded, formatted to suit the sheet it is to go the data is added, then the profit and loss calculations take place. That's the easy part and up until a couple of weeks ago, it was a pretty normal procedure. Unfortunately there has been a change at the data supplier whereby the number of runners listed (column F) no longer matches the number of placings listed (column AC). This is not the case in all races, but the only ones it really affects is anything around 8, 9 or even possibly 10 runners.

Pretty much all over the world, a dividend is paid to 3rd place horses in races of 8 runners or more. If there are 7 runners or less, then only 2 placings are paid and less than 4, only 1st place is paid.

So the issue here is that for whatever reason, the data supplier is now listing the original number of runners when the race is announced; let's say 8. During the race day, 2 horses are withdrawn making only 6 horses start, therefore now only paying 1st and 2nd. The P&L formula I have always used cannot differentiate this and puts a profit amount where it shouldn't be. This is very new and the data for April 2021 was fine, but for May, it has now changed.

Here is the P&L formula I use

=IF(OR(AND(F3>=4,F3<=7,AC3<=2), AND(F3>=8,AC3<=3)),(Z3-1)*98,-100)

This has always worked fine, as I mentioned, but now I need to have a way of checking that if, say, F3>=8 but the corresponding results in AC only show 7, or 6 placings or less, then it is listed as a loss.

There is a further issue which is that not all placings are listed as where they finished. There some codes which begin with 5 and are 3 digits, ie 501, 509 and so on. These mean things like Unseated The Rider, Pulled Up and so on. The numbers are not so important, as all it means is the horse started, but did not finish.

As an example of a possible 8 horse race, AC may look like this

1
2
3
4
5
6
509
512

This still shows there were 8 runners which started.

Now to help, I always have the data sorted as follows, A - Date, C - Time, H - Track, AC - Finish Position & Q - Horse. This at least means that the finish positions are consecutive.

I am able to write some code to do the matching part, but not to include checking number of runners vs Finish Position. On any given day, if A, C & Q match, then it is the same horse. I just need a way of telling it that if A, C & Q match and F3>=8, AND the corresponding cells in AC show 8 entries, then it uses the profit formula, if not, it is a loss.

As I said, the matching part is easy and I have this code which does it for me, thanks to one of the members here helping me sort out a time issue

=IFERROR(INDEX('[2021 Latest Results January 2021.xlsb]VDW-Place-Adapted-2021-01-01-20'!$AE$3:$AE$103352,MATCH(1,INDEX((A3='[2021 Latest Results January 2021.xlsb]VDW-Place-Adapted-2021-01-01-20'!$A$3:$A$103352)*(ROUND(C3,5)=ROUND('[2021 Latest Results January 2021.xlsb]VDW-Place-Adapted-2021-01-01-20'!$C$3:$C$103352,5))*(Q3='[2021 Latest Results January 2021.xlsb]VDW-Place-Adapted-2021-01-01-20'!$Q$3:$Q$103352),0,1),0)),"No")

I can change the sheet references as required, but what can be added to check the requirement I have? It just needs to check that if F3>=8 AND the corresponding cells in AC have 8 entries, then use the (Z3-1)*98 profit formula, otherwise it is a loss.

Here is what an incorrect one looks like

2021 Latest Results May 2021.xlsb
ABCFQACADAE
335254/23/21Friday15:158Kakamora1144.0662.72
335264/23/21Friday15:158Good Boy Bobby2-100147
335274/23/21Friday15:158Fugitives Drift3-100-100
335284/23/21Friday15:158Tayzar4-100-100
335294/23/21Friday15:158Getareason501-100-100
335304/23/21Friday15:158Big Bad Bear512-100-100
2021 Latest Results May 2021


You can see there are 8 runners listed in F, but only 6 entries in AC

I hope that helps and there is a fix
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Forum statistics

Threads
1,148,108
Messages
5,744,876
Members
423,907
Latest member
zerocool88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top