How to compare columns using conditions

[honkin]

Can anyone tell me if this is possible at all?

I have a sheet which keeps track of my horse racing results. The number of runners in the race are recorded in column F and the finish position of the runners is recorded in column AC

I want to determine which races do NOT have the same number of runners as what is shown in column F. What I mean is, there are times when a runner is removed from the race. This could be very close to the start time. Sometimes the number of runners is showing the numbers of runners which were originally supposed to run, whereas the finish position in column AC will show the number of runners which actually started.

What I am after is to find out when F might be showing, let's say 14, but AC only has 12 finish positions. Now the only way to determine which runners were in the same race is to use Date (A), Time (C) and Track (H). If those 3 match, then it is the same race.

So I guess in English it would be sort of like this as an example

If A, B & C match and F shows 14, does AC have the same number of finish positions - 14. Note: 14 is just an example. The number in F will be anywhere from 2 to 30 or so

Without using VBA, is this possible at all? Happy for just a T/F or Y/N sort of result

cheers

 

[Joe4]

I think this is a case where it would be most helpful for us to see exactly what your data looks like in this situation. Can you post an example?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[honkin]

cheers Joe. Thanks for your reply

Here is a sample

2021 Latest Results June 2021.xlsb
	A		C			F		H									Q												AC
17	Date		Time			# of Runners		Course									Horse												Position
18	01/01/2021		12:05			4		Southwell									Perfect Swiss												1
19	01/01/2021		12:05			4		Southwell									Thrill Seeker												2
20	01/01/2021		12:05			4		Southwell									Tukhoom												3
21	01/01/2021		12:05			4		Southwell									Jump The Gun												4
22	01/01/2021		12:20			12		Tramore									Mr Coldstone												1
23	01/01/2021		12:20			12		Tramore									Not Available												2
24	01/01/2021		12:20			12		Tramore									Insignia Of Rank												3
25	01/01/2021		12:20			12		Tramore									Drop Him In												4
26	01/01/2021		12:20			12		Tramore									Garm Colombe												5
27	01/01/2021		12:20			12		Tramore									Iknownothing												6
28	01/01/2021		12:20			12		Tramore									Get Phar												7
29	01/01/2021		12:20			12		Tramore									Tipperary Dexter												8
30	01/01/2021		12:20			12		Tramore									Alexharwill												9
31	01/01/2021		12:20			12		Tramore									Gray Moonlight												10
32	01/01/2021		12:20			12		Tramore									Do No More												11
33	01/01/2021		12:20			12		Tramore									Der Mc												509
34	01/01/2021		12:40			11		Southwell									Maykir												1
35	01/01/2021		12:40			11		Southwell									Daafy												2
36	01/01/2021		12:40			11		Southwell									Geography Teacher												3
37	01/01/2021		12:40			11		Southwell									Compass Point												4
38	01/01/2021		12:40			11		Southwell									Heart Of A Hunter												5
39	01/01/2021		12:40			11		Southwell									True Companion												6
40	01/01/2021		12:40			11		Southwell									Crazy Spin												7
41	01/01/2021		12:40			11		Southwell									De Bruyne Horse												8
42	01/01/2021		12:40			11		Southwell									Caesonia												9
43	01/01/2021		12:40			11		Southwell									Best Tamayuz												10
44	01/01/2021		12:40			11		Southwell									Achill Queen												11
45	01/01/2021		12:45			9		Musselburgh									Fiveandtwenty												1
46	01/01/2021		12:45			9		Musselburgh									Herbiers												2
47	01/01/2021		12:45			9		Musselburgh									Feldspar												3
48	01/01/2021		12:45			9		Musselburgh									Kings Creek												4
49	01/01/2021		12:45			9		Musselburgh									Red Missile												5
50	01/01/2021		12:45			9		Musselburgh									Go Bob Go												6
51	01/01/2021		12:45			9		Musselburgh									Kiss My Face												7
52	01/01/2021		12:45			9		Musselburgh									Ajax Tavern												8
53	01/01/2021		12:45			9		Musselburgh									Gypsy Traveller												9
rc-vdw-place-adapted-2021-01-01

You can see that on this occasion, each race shows the same number of finishers as column F shows. Just be aware that not all finishing positions are listed as consecutive numbers. There are a group of finishing positions used for special conditions, like the horse fell at a fence or unseated a rider and so on. These are all 3 digit numbers beginning with a 5. Not that this matters; the bottom line is that the number of runners listed in column F needs to match the number of entries for that race in column AC...regardless of the actual number showing, if that makes sense. If 14 runners listed in F, then 14 entries in AC. If not, then a False or No response.

Thanks so much

 

[Joe4]

Place this formula in AD18 and copy down for all rows.
It will return TRUE if the number of records listed for that race match the number shown in column F, or it will show FALSE if it does not.

=COUNTIFS(A:A,A18,C:C,C18,H:H,H18)=F18

 

[honkin]

Place this formula in AD18 and copy down for all rows.
It will return TRUE if the number of records listed for that race match the number shown in column F, or it will show FALSE if it does not.

=COUNTIFS(A:A,A18,C:C,C18,H:H,H18)=F18

Absolutely brilliant, Joe and thanks for the prompt reply

cheers

 

[Joe4]

You are welcome!
Glad I was able to help.