Hello everyone,
My database is called: "Parents".
Cells in the "A" column end either with "M" for Male or "F" for Female.
In the "H" column, we find the date of birth, in days, months and year, we will see later that we will recover only the year to use it as a variable.
In the "K" column, we find the subjects kept for my breeding, they are represented by the "x" character in this same "K" column.
Here is a very small content of my database:
I would like an answer in vba to count the number of subjects kept for my breeding according to the year and sex.
Let me explain :
1. Subjects kept for my breeding (See character "x" in column "K") which indicates that they are kept for breeding.).
2. Determine the year of birth (see the last four digits in column "H").
3. Determine the sex by taking the last character in column "A". ("M" character for male and "F" character for female.
Unless I am mistaken, here are the results that I placed in the "AA" column:
I remain at your disposal for further information.
My database is called: "Parents".
Cells in the "A" column end either with "M" for Male or "F" for Female.
In the "H" column, we find the date of birth, in days, months and year, we will see later that we will recover only the year to use it as a variable.
In the "K" column, we find the subjects kept for my breeding, they are represented by the "x" character in this same "K" column.
Here is a very small content of my database:
Elevage_Test2.xls | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | Jeune | Père | Mère | Eleveur | Age | Volière | Cage | Né(e) | Tours | Information | Elevage | ||
2 | 876-054/2004 F | B1 | C1 | D1 | E1 | F1 | G1 | 16-03-2004 | 4T | Femelle | x | ||
3 | 876-025/2006 F | B2 | C2 | D2 | E2 | F2 | G2 | 15-04-2006 | 4T | Femelle | |||
4 | GUP14-028/2006 F | B3 | C3 | D3 | E3 | F3 | G3 | 15-04-2006 | 4T | Femelle | |||
5 | GUP14-041/2006 M | B4 | C4 | D4 | E4 | F4 | G4 | 15-04-2006 | 4T | Mâle | x | ||
6 | GUP14-011/2007 F | B7 | C7 | D7 | E7 | F7 | G7 | 15-04-2007 | 4T | Femelle | |||
7 | AMA55-014/2007 M | B8 | C8 | D8 | E8 | F8 | G8 | 15-04-2007 | 4T | Mâle | x | ||
8 | 876-022/2007 M | B9 | C9 | D9 | E9 | F9 | G9 | 15-04-2007 | 4T | Mâle | |||
9 | 876-033/2007 F | B10 | C10 | D10 | E10 | F10 | G10 | 15-04-2007 | 4T | Femelle | x | ||
10 | GUP14-001/2009 M | B32 | C32 | D32 | E32 | F32 | G32 | 15-04-2009 | 5T | Mâle | x | ||
11 | GUP14-003/2009 F | B34 | C34 | D34 | E34 | F34 | G34 | 15-04-2009 | 4T | Femelle | |||
12 | 25-004/2009 M | B35 | C35 | D35 | E35 | F35 | G35 | 15-04-2009 | 4T | Mâle | |||
13 | GUP14-005/2009 M | B36 | C36 | D36 | E36 | F36 | G36 | 15-04-2009 | 4T | Mâle | x | ||
14 | GUP14-006/2009 F | B37 | C37 | D37 | E37 | F37 | G37 | 15-04-2009 | 5T | Femelle | x | ||
15 | YaM-008/2009 M | B38 | C38 | D38 | E38 | F38 | G38 | 15-04-2009 | 4T | Mâle | |||
16 | C27-002/2009 F | B33 | C33 | D33 | E33 | F33 | G33 | 16-04-2009 | 4T | Femelle | |||
17 | HCH57-019/2010 F | B74 | C74 | D74 | E74 | F74 | G74 | 15-04-2010 | 4T | Femelle | x | ||
18 | SC57-018/2010 F | B73 | C73 | D73 | E73 | F73 | G73 | 16-04-2010 | 4T | Femelle | |||
19 | EA27-003/2011 M | B112 | C112 | D112 | E112 | F112 | G112 | 15-04-2011 | 5T | Mâle | x | ||
20 | PIG14-004/2011 M | B113 | C113 | D113 | E113 | F113 | G113 | 15-04-2011 | 5T | Mâle | |||
21 | PIG14-009/2011 M | B114 | C114 | D114 | E114 | F114 | G114 | 15-04-2011 | 5T | Mâle | x | ||
22 | YaM-022/2011 M | B120 | C120 | D120 | E120 | F120 | G120 | 15-04-2011 | 4T | Mâle | |||
23 | PIG14-040/2011 F | B757 | C757 | D757 | E757 | F757 | G757 | 16-04-2011 | 4T | Femelle | x | ||
24 | PIG14-002/2012 M | B170 | C170 | D170 | E170 | F170 | G170 | 14-04-2012 | 5T | Mâle | |||
25 | EA27-001/2012 M | B169 | C169 | D169 | E169 | F169 | G169 | 17-04-2012 | 5T | Mâle | x | ||
26 | EA27-010/2013 M | B348 | C348 | D348 | E348 | F348 | G348 | 13-04-2013 | 5T | Mâle | |||
27 | EA27-021/2013 M | B376 | C376 | D376 | E376 | F376 | G376 | 21-04-2013 | 5T | Mâle | |||
28 | EA27-012/2013 F | B352 | C352 | D352 | E352 | F352 | G352 | 25-04-2013 | 4T | Femelle | x | ||
29 | EA27-011/2013 M | B350 | C350 | D350 | E350 | F350 | G350 | 8-06-2013 | 4T | Mâle | x | ||
30 | EA27-013/2013 F | B353 | C353 | D353 | E353 | F353 | G353 | 8-06-2013 | 4T | Femelle | |||
31 | EA27-001/2014 F | B516 | C516 | D516 | E516 | F516 | G516 | 11-04-2014 | 5T | Femelle | x | ||
32 | EA27-002/2014 M | B517 | C517 | D517 | E517 | F517 | G517 | 11-04-2014 | 5T | Mâle | x | ||
33 | EA27-003/2014 F | B519 | C519 | D519 | E519 | F519 | G519 | 11-04-2014 | 5T | Femelle | |||
34 | PIG14-003/2014 M | B518 | C518 | D518 | E518 | F518 | G518 | 12-04-2014 | 5T | Mâle | |||
35 | EA27-004/2014 F | B520 | C520 | D520 | E520 | F520 | G520 | 28-04-2014 | 4T | Femelle | x | ||
36 | EA27-004/2015 F | B713 | C713 | D713 | E713 | F713 | G713 | 9-04-2015 | 5T | Femelle | x | ||
37 | MAE76-003/2015 F | B712 | C712 | D712 | E712 | F712 | G712 | 10-04-2015 | 4T | Femelle | |||
38 | EA27-005/2015 F | B714 | C714 | D714 | E714 | F714 | G714 | 11-04-2015 | 5T | Femelle | |||
39 | EA27-040/2015 F | B758 | C758 | D758 | E758 | F758 | G758 | 15-04-2015 | 4T | Femelle | |||
40 | EA27-041/2015 M | B759 | C759 | D759 | E759 | F759 | G759 | 15-04-2015 | 4T | Mâle | x | ||
41 | PIG14-006/2015 F | B715 | C715 | D715 | E715 | F715 | G715 | 21-04-2015 | 5T | Femelle | x | ||
42 | EA27-009/2016 F | B899 | C899 | D899 | E899 | F899 | G899 | 20-04-2016 | 4T | Femelle | |||
43 | EA27-010/2016 F | B900 | C900 | D900 | E900 | F900 | G900 | 20-04-2016 | 4T | Femelle | x | ||
44 | EA27-011/2016 M | B901 | C901 | D901 | E901 | F901 | G901 | 20-04-2016 | 4T | Mâle | |||
45 | EA27-012/2016 F | B902 | C902 | D902 | E902 | F902 | G902 | 20-04-2016 | 4T | Femelle | x | ||
46 | EA27-003/2017 M | B1056 | C1056 | D1056 | E1056 | F1056 | G1056 | 10-04-2017 | 4T | Mâle | |||
47 | EA27-001/2017 M | B1054 | C1054 | D1054 | E1054 | F1054 | G1054 | 11-04-2017 | 4T | Mâle | |||
48 | EA27-002/2017 F | B1055 | C1055 | D1055 | E1055 | F1055 | G1055 | 11-04-2017 | 4T | Femelle | |||
49 | PIG14-004/2017 F | B1057 | C1057 | D1057 | E1057 | F1057 | G1057 | 12-04-2017 | 4T | Femelle | x | ||
50 | PIG14-001/2018 F | B1171 | C1171 | D1171 | E1171 | F1171 | G1171 | 12-04-2018 | 4T | Femelle | |||
51 | EA27-001/2018 F | B1173 | C1173 | D1173 | E1173 | F1173 | G1173 | 19-04-2018 | 4T | Femelle | |||
52 | EA27-002/2018 F | B1175 | C1175 | D1175 | E1175 | F1175 | G1175 | 19-04-2018 | 4T | Femelle | x | ||
53 | JED32-001/2018 F | B1172 | C1172 | D1172 | E1172 | F1172 | G1172 | 27-05-2018 | 4T | Femelle | x | ||
54 | JED32-002/2018 F | B1174 | C1174 | D1174 | E1174 | F1174 | G1174 | 27-05-2018 | 4T | Femelle | |||
55 | EA27-001/2019 M | B1290 | C1290 | D1290 | E1290 | F1290 | G1290 | 19-04-2019 | 5T | Mâle | |||
56 | EA27-002/2019 F | B1291 | C1291 | D1291 | E1291 | F1291 | G1291 | 19-04-2019 | 5T | Femelle | |||
57 | EA27-003/2019 M | B1292 | C1292 | D1292 | E1292 | F1292 | G1292 | 19-04-2019 | 5T | Mâle | x | ||
58 | EA27-005/2019 F | B1293 | C1293 | D1293 | E1293 | F1293 | G1293 | 19-04-2019 | 5T | Femelle | |||
59 | EA27-006/2019 F | B1294 | C1294 | D1294 | E1294 | F1294 | G1294 | 19-04-2019 | 5T | Femelle | x | ||
60 | EA27-007/2019 F | B1295 | C1295 | D1295 | E1295 | F1295 | G1295 | 19-04-2019 | 5T | Femelle | |||
61 | EA27-008/2019 F | B1296 | C1296 | D1296 | E1296 | F1296 | G1296 | 19-04-2019 | 5T | Femelle | x | ||
62 | EA27-009/2019 F | B1297 | C1297 | D1297 | E1297 | F1297 | G1297 | 22-04-2019 | 5T | Femelle | |||
63 | EA27-010/2019 F | B1298 | C1298 | D1298 | E1298 | F1298 | G1298 | 22-04-2019 | 5T | Femelle | |||
64 | EA27-011/2019 F | B1299 | C1299 | D1299 | E1299 | F1299 | G1299 | 22-04-2019 | 5T | Femelle | x | ||
65 | EA27-001/2020 F | B1397 | C1397 | D1397 | E1397 | F1397 | G1397 | 15-04-2020 | 5T | Femelle | |||
66 | EA27-002/2020 M | B1398 | C1398 | D1398 | E1398 | F1398 | G1398 | 15-04-2020 | 5T | Mâle | x | ||
67 | EA27-001+/2020 M | B1396 | C1396 | D1396 | E1396 | F1396 | G1396 | 2-06-2020 | 4T | Mâle | x | ||
68 | EA27-001/2022 M | B1564 | C1564 | D1564 | E1564 | F1564 | G1564 | 23-04-2022 | 4T | Mâle | |||
69 | EA27-002/2022 F | B1565 | C1565 | D1565 | E1565 | F1565 | G1565 | 26-04-2022 | 4T | Femelle | x | ||
Parents |
I would like an answer in vba to count the number of subjects kept for my breeding according to the year and sex.
Let me explain :
1. Subjects kept for my breeding (See character "x" in column "K") which indicates that they are kept for breeding.).
2. Determine the year of birth (see the last four digits in column "H").
3. Determine the sex by taking the last character in column "A". ("M" character for male and "F" character for female.
Unless I am mistaken, here are the results that I placed in the "AA" column:
Elevage_Test2.xls | |||||
---|---|---|---|---|---|
AA | AB | AC | |||
1 | Année | Mâle | Femelle | ||
2 | 2004 | 1 | |||
3 | 2006 | 1 | |||
4 | 2007 | 1 | 1 | ||
5 | 2009 | 2 | 1 | ||
6 | 2010 | 1 | |||
7 | 2011 | 2 | 1 | ||
8 | 2012 | 1 | |||
9 | 2013 | 1 | 1 | ||
10 | 2014 | 1 | 2 | ||
11 | 2015 | 1 | 2 | ||
12 | 2016 | 2 | |||
13 | 2017 | 1 | |||
14 | 2018 | 2 | |||
15 | 2019 | 1 | 3 | ||
16 | 2020 | 2 | |||
17 | 2022 | 1 | |||
Parents |
I remain at your disposal for further information.