Complex - Multiple Criteria

[dunmore83]

Excel 2012
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	Ear Tag	Sex	NLIS		Birth Date				01-Jul-14	02-Jul-14	03-Jul-14	04-Jul-14	05-Jul-14	06-Jul-14	07-Jul-14	08-Jul-14
3
4
5	1658	Heifer	-	04.09.11	04-Sep-11				1,031	1,032	1,033	1,034	1,035	1,036	1,037	1,038
6	2112	Heifer	982123490405654	10.08.12	10-Aug-12				690	691	692	693	694	695	696	697
7	272	Heifer	982123490425668	27.08.12	27-Aug-12				673	674	675	676	677	678	679	680
8	2282	Heifer	982123490425757	28.09.12	28-Sep-12				641	642	643	644	645	646	647	648
9	3007	Heifer	982123490425176	24.01.13	24-Jan-13				523	524	525	526	527	528	529	530
10	3008	Heifer	982123490417932	24.01.13	24-Jan-13				523	524	525	526	527	528	529	530
11	3018	Heifer	982123490425858	29.01.13	29-Jan-13				518	519	520	521	522	523	524	525
12	3026	Heifer	982123498847561	31.01.13	31-Jan-13				516	517	518	519	520	521	522	523
13	3040	Heifer	982123490449063	06.02.13	06-Feb-13				510	511	512	513	514	515	516	517
14	3042	Heifer	982123490417737	06.02.13	06-Feb-13				510	511	512	513	514	515	516	517
15	3053	Heifer	982123490405560	07.02.13	07-Feb-13				509	510	511	512	513	514	515	516
16	3052	Heifer	982123490449125	07.02.13	07-Feb-13				509	510	511	512	513	514	515	516
17	3051	Heifer	982123489462454	07.02.13	07-Feb-13				509	510	511	512	513	514	515	516
18	3055	Heifer	982123490425967	08.02.13	08-Feb-13				508	509	510	511	512	513	514	515
19	3054	Heifer	982123490425848	08.02.13	08-Feb-13				508	509	510	511	512	513	514	515
20	3064	Heifer	982123490417515	11.02.13	11-Feb-13				505	506	507	508	509	510	511	512
21	3061	Heifer	982123490419708	11.02.13	11-Feb-13				505	506	507	508	509	510	511	512
22	3065	Heifer	982123498847685	12.02.13	12-Feb-13				504	505	506	507	508	509	510	511
23	3069	Heifer	982091016308716	14.02.13	14-Feb-13				502	503	504	505	506	507	508	509
24	3071	Heifer	982123490447819	14.02.13	14-Feb-13				502	503	504	505	506	507	508	509
25	3074	Heifer	982123490425039	17.02.13	17-Feb-13				499	500	501	502	503	504	505	506
26	3084	Heifer	982123490226691	26.02.13	26-Feb-13				490	491	492	493	494	495	496	497
27	3088	Heifer	982123490425140	28.02.13	28-Feb-13				488	489	490	491	492	493	494	495
28	3090	Heifer	982123489464042	03.03.13	03-Mar-13				485	486	487	488	489	490	491	492
29	3098	Heifer	982123489459356	11.03.13	11-Mar-13				477	478	479	480	481	482	483	484
30	3104	Heifer	982123489466360	01.04.13	01-Apr-13				456	457	458	459	460	461	462	463
31	3161	Heifer	982123490224041	09.08.13	09-Aug-13				326	327	328	329	330	331	332	333
32	3176	Heifer	982000191763119	15.08.13	15-Aug-13				320	321	322	323	324	325	326	327
33	3179	Heifer	982000191805821	16.08.13	16-Aug-13				319	320	321	322	323	324	325	326
34	3190	Heifer	982000191727512	18.08.13	18-Aug-13				317	318	319	320	321	322	323	324
35	3186	Heifer	982123498851839	18.08.13	18-Aug-13				317	318	319	320	321	322	323	324
36	3191	Heifer	982000191736709	19.08.13	19-Aug-13				316	317	318	319	320	321	322	323
ages-excel-forum

 

[gaz_chops]

It seems to be throwing up another issue!!

There are 53 sales on the 15-May-15 that fall within the bull group 03-Sep-14 to 31-Jan-15 however these 53 are showing in a different group & none are showing for this group ??!!

I can't see any sales for May-15!

 

[gaz_chops]

Ah you have added more data to your last uploaded file!
This could cause issues if you are updating the file while I am working on it, you are prob best to copy the new data to the last file I uploaded.

 

[gaz_chops]

Also another one... J88 in the Livestock Sales is showing a birth date of 31-May-15. However it is actually 16-Sep-14 in the source data sheet. Any ideas?!!

THANKS!!!!!

Because when you have copied the formula from the Deaths sheet you haven't changed all the references! It is matching the Tag on the Deaths sheet!

=IF(G80="","",IFERROR(INDEX('Stock-Source-Data'!$K$10:$K$1322,MATCH(Deaths!G80,'Stock-Source-Data'!$T$10:$T$1322,0)),"No Date"))

You need to check things like this yourself, before you ask me.

 

[gaz_chops]

Use the latest uploaded file.

I have added the data for Livestock Sales that was missing, the dates should now be correct.

https://www.dropbox.com/s/fl0ccq78o1aofda/Gaz-Comparison.xlsx?dl=0

 

[dunmore83]

Thanks. Sorry for messing a few things up! I was trying to follow your methods through to the other columns.

All looks good now except one thing stands out.... how is it possible 1 death is showing in AT26 (Stock-Reconc-Bulls) when the group only begins on 1-Mar-15 ? The 1 death is showing in Jan-15

 

[gaz_chops]

You should check these yourself, Tag No. 527 has a birth date of 8/4/15 & a death date of 23/1/15?

 

[dunmore83]

Oops... that was a data entry mistake. sorry about that!

 

[gaz_chops]

Oops... that was a data entry mistake. sorry about that!

I double checked all of the formulas and they all appear to work ok, so if there are any anomalies it will prob be the data or user error ?Gaz

 

[dunmore83]

Hi Gaz,

I have rolled over these formulas from 2014/15 to 2015/16 & the heifers by age category worksheet doesn't appear to have updated. All numbers are 0's. Any ideas?

Also seems to be a few issues with the 1st line in recon heifers & recon bulls for each of the groups which pulls a summary of the data from before July 2015. If working correctly I guess this line should automatically equal the summary line for each group at the end of the previous financial year.

https://www.dropbox.com/s/hvjdwlmqn0tpct6/2014-15-Stock-Recon.xlsx?dl=0

https://www.dropbox.com/s/0lldv27ub5o49ug/2015-16-Stock-Recon.xlsx?dl=0

 

[gaz_chops]

I have rolled over these formulas from 2014/15 to 2015/16 & the heifers by age category worksheet doesn't appear to have updated. All numbers are 0's. Any ideas?

This is because there is no data after the 30/6/15.

Also seems to be a few issues with the 1st line in recon heifers & recon bulls for each of the groups which pulls a summary of the data from before July 2015. If working correctly I guess this line should automatically equal the summary line for each group at the end of the previous financial year.

This appears to be fine, the opening equals the closing!