Count breeding birds by year and sex.

H

harzer

Board Regular
Joined
Dec 15, 2021
Messages
122
Office Version
  1. 2016
Platform
  1. Windows
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:

Elevage_Test2.xls
ABCDEFGHIJK
1JeunePèreMèreEleveurAgeVolièreCageNé(e)ToursInformationElevage
2876-054/2004 FB1C1D1E1F1G116-03-20044TFemelle x
3876-025/2006 FB2C2D2E2F2G215-04-20064TFemelle
4GUP14-028/2006 FB3C3D3E3F3G315-04-20064TFemelle
5GUP14-041/2006 MB4C4D4E4F4G415-04-20064TMâle x
6GUP14-011/2007 FB7C7D7E7F7G715-04-20074TFemelle
7AMA55-014/2007 MB8C8D8E8F8G815-04-20074TMâle x
8876-022/2007 MB9C9D9E9F9G915-04-20074TMâle
9876-033/2007 FB10C10D10E10F10G1015-04-20074TFemelle x
10GUP14-001/2009 MB32C32D32E32F32G3215-04-20095TMâle x
11GUP14-003/2009 FB34C34D34E34F34G3415-04-20094TFemelle
1225-004/2009 MB35C35D35E35F35G3515-04-20094TMâle
13GUP14-005/2009 MB36C36D36E36F36G3615-04-20094TMâle x
14GUP14-006/2009 FB37C37D37E37F37G3715-04-20095TFemelle x
15YaM-008/2009 MB38C38D38E38F38G3815-04-20094TMâle
16C27-002/2009 FB33C33D33E33F33G3316-04-20094TFemelle
17HCH57-019/2010 FB74C74D74E74F74G7415-04-20104TFemelle x
18SC57-018/2010 FB73C73D73E73F73G7316-04-20104TFemelle
19EA27-003/2011 MB112C112D112E112F112G11215-04-20115TMâle x
20PIG14-004/2011 MB113C113D113E113F113G11315-04-20115TMâle
21PIG14-009/2011 MB114C114D114E114F114G11415-04-20115TMâle x
22YaM-022/2011 MB120C120D120E120F120G12015-04-20114TMâle
23PIG14-040/2011 FB757C757D757E757F757G75716-04-20114TFemelle x
24PIG14-002/2012 MB170C170D170E170F170G17014-04-20125TMâle
25EA27-001/2012 MB169C169D169E169F169G16917-04-20125TMâle x
26EA27-010/2013 MB348C348D348E348F348G34813-04-20135TMâle
27EA27-021/2013 MB376C376D376E376F376G37621-04-20135TMâle
28EA27-012/2013 FB352C352D352E352F352G35225-04-20134TFemelle x
29EA27-011/2013 MB350C350D350E350F350G3508-06-20134TMâle x
30EA27-013/2013 FB353C353D353E353F353G3538-06-20134TFemelle
31EA27-001/2014 FB516C516D516E516F516G51611-04-20145TFemelle x
32EA27-002/2014 MB517C517D517E517F517G51711-04-20145TMâle x
33EA27-003/2014 FB519C519D519E519F519G51911-04-20145TFemelle
34PIG14-003/2014 MB518C518D518E518F518G51812-04-20145TMâle
35EA27-004/2014 FB520C520D520E520F520G52028-04-20144TFemelle x
36EA27-004/2015 FB713C713D713E713F713G7139-04-20155TFemelle x
37MAE76-003/2015 FB712C712D712E712F712G71210-04-20154TFemelle
38EA27-005/2015 FB714C714D714E714F714G71411-04-20155TFemelle
39EA27-040/2015 FB758C758D758E758F758G75815-04-20154TFemelle
40EA27-041/2015 MB759C759D759E759F759G75915-04-20154TMâle x
41PIG14-006/2015 FB715C715D715E715F715G71521-04-20155TFemelle x
42EA27-009/2016 FB899C899D899E899F899G89920-04-20164TFemelle
43EA27-010/2016 FB900C900D900E900F900G90020-04-20164TFemelle x
44EA27-011/2016 MB901C901D901E901F901G90120-04-20164TMâle
45EA27-012/2016 FB902C902D902E902F902G90220-04-20164TFemelle x
46EA27-003/2017 MB1056C1056D1056E1056F1056G105610-04-20174TMâle
47EA27-001/2017 MB1054C1054D1054E1054F1054G105411-04-20174TMâle
48EA27-002/2017 FB1055C1055D1055E1055F1055G105511-04-20174TFemelle
49PIG14-004/2017 FB1057C1057D1057E1057F1057G105712-04-20174TFemelle x
50PIG14-001/2018 FB1171C1171D1171E1171F1171G117112-04-20184TFemelle
51EA27-001/2018 FB1173C1173D1173E1173F1173G117319-04-20184TFemelle
52EA27-002/2018 FB1175C1175D1175E1175F1175G117519-04-20184TFemelle x
53JED32-001/2018 FB1172C1172D1172E1172F1172G117227-05-20184TFemelle x
54JED32-002/2018 FB1174C1174D1174E1174F1174G117427-05-20184TFemelle
55EA27-001/2019 MB1290C1290D1290E1290F1290G129019-04-20195TMâle
56EA27-002/2019 FB1291C1291D1291E1291F1291G129119-04-20195TFemelle
57EA27-003/2019 MB1292C1292D1292E1292F1292G129219-04-20195TMâle x
58EA27-005/2019 FB1293C1293D1293E1293F1293G129319-04-20195TFemelle
59EA27-006/2019 FB1294C1294D1294E1294F1294G129419-04-20195TFemelle x
60EA27-007/2019 FB1295C1295D1295E1295F1295G129519-04-20195TFemelle
61EA27-008/2019 FB1296C1296D1296E1296F1296G129619-04-20195TFemelle x
62EA27-009/2019 FB1297C1297D1297E1297F1297G129722-04-20195TFemelle
63EA27-010/2019 FB1298C1298D1298E1298F1298G129822-04-20195TFemelle
64EA27-011/2019 FB1299C1299D1299E1299F1299G129922-04-20195TFemelle x
65EA27-001/2020 FB1397C1397D1397E1397F1397G139715-04-20205TFemelle
66EA27-002/2020 MB1398C1398D1398E1398F1398G139815-04-20205TMâle x
67EA27-001+/2020 MB1396C1396D1396E1396F1396G13962-06-20204TMâle x
68EA27-001/2022 MB1564C1564D1564E1564F1564G156423-04-20224TMâle
69EA27-002/2022 FB1565C1565D1565E1565F1565G156526-04-20224TFemelle 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
AAABAC
1AnnéeMâleFemelle
220041
320061
4200711
5200921
620101
7201121
820121
9201311
10201412
11201512
1220162
1320171
1420182
15201913
1620202
1720221
Parents


I remain at your disposal for further information.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
Good morning,
I forgot to specify a proposition with array, like =
For cmpt1 = LBound(Montab, 1) To UBound(Montab, 1)
(or dictionary!) to search for results quickly.
Thank you so much.
 
Upvote 0
with Power Query

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Né(e)", type date}}),
    #"Extracted Year" = Table.TransformColumns(#"Changed Type",{{"Né(e)", Date.Year, Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Extracted Year", each ([Elevage] = "x")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Né(e)", "Information"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Information]), "Information", "Count")
in
    #"Pivoted Column"

Né(e)Femelle Mâle
20041
20061
200711
200912
20101
201112
20121
201311
201421
201521
20162
20171
20182
201931
20202
20221
 
Upvote 0
Hello alansidman,
Thank you for your reply,
To begin, I want to inform you that I am not an expert in Excel, for good reason, I have never used Power Query and I do not know how it works.
I took the code that I put in a Module but it doesn't work.
Can you please suggest me a solution in vba.
Friendly greetings.
 
Upvote 0
I am not a VBA expert. This is Mcode and not VBA.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.
 
Upvote 0
Hi Alan
How to edit this line of code to ignore case
= Table.SelectRows(#"Extracted Year", each ([Elevage] = "x"))
That is, consider lines with "x" or "X"

M.
 
Upvote 0
Hey Marcelo. I would highlight the column and then Transform-->Format and then either select Upper Case or Lower Case to make them consistent.
 
Upvote 0
Alan,
Thank you.
Something like?
I changed columns order (last code line name in Portuguese)

VBA Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Né(e)", type date}}),
    #"Extracted Year" = Table.TransformColumns(#"Changed Type",{{"Né(e)", Date.Year, Int64.Type}}),
    #"Text in Lowercase" = Table.TransformColumns(#"Extracted Year",{{"Elevage", Text.Lower, type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Text in Lowercase", each ([Elevage] = "x")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Né(e)", "Information"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Information]), "Information", "Count"),
    #"Colunas Reordenadas" = Table.ReorderColumns(#"Pivoted Column",{"Né(e)", "Mâle ", "Femelle "})
in
    #"Colunas Reordenadas"
[/CODE]

18022023 com Power BI.xlsx
ABC
1Né(e)Mâle Femelle
220041
320061
4200711
5200921
620101
7201121
820121
9201311
10201412
11201512
1220162
1320171
1420182
15201913
1620202
1720221
Plan4


M.
 
Last edited:
Upvote 0
A VBA possible solution
Assumes Dates in column H are real dates (numbers, not text)

Code: 
Sub aTest()
    Dim dic As Object, vData As Variant, i As Long
    Dim vKey As Variant, arrAux As Variant, vResult As Variant
    
    Set dic = CreateObject("Scripting.Dictionary")
    With Sheets("Parents")
        vData = .Range("A2:K" & .Cells(.Rows.Count, "A").End(xlUp).Row)
        For i = 1 To UBound(vData, 1)
            If UCase(vData(i, 11)) = "X" Then
                If Not dic.exists(Year(vData(i, 8))) Then
                    If UCase(Right(vData(i, 1), 1)) = "F" Then
                        dic(Year(vData(i, 8))) = Array(0, 1)
                    Else
                        dic(Year(vData(i, 8))) = Array(1, 0)
                    End If
                Else
                    arrAux = dic(Year(vData(i, 8)))
                    If UCase(Right(vData(i, 1), 1)) = "F" Then
                        arrAux(1) = arrAux(1) + 1
                    Else
                        arrAux(0) = arrAux(0) + 1
                    End If
                    dic(Year(vData(i, 8))) = arrAux
                End If
            End If
        Next i
        'MsgBox dic.Count
        .Range("AA1:AC1") = Array("Année", "Mâle", "Femelle")
        vResult = .Range("AA2").Resize(dic.Count, 3)
        i = 0
        For Each vKey In dic.keys
            i = i + 1
            vResult(i, 1) = vKey
            vResult(i, 2) = IIf(dic(vKey)(0) = 0, "", dic(vKey)(0))
            vResult(i, 3) = IIf(dic(vKey)(1) = 0, "", dic(vKey)(1))
        Next vKey
        .Range("AA2").Resize(dic.Count, 3) = vResult
    End With
End Sub

M.
 
Upvote 0
Solution
Hello Marcelo Branco & alansidman,
To begin, I reply to alansidman and say thank you for his proposal, and thank you also for the links to learn how to install Power Query, as well as the one for an explain introduction to the Power Query functionality and finally for the link which explains and shows how to use Power Query code.
You taught me this AddIn (Power Query) that I didn't know, but I still have to implement your proposal on my Excel file, I hope to get there unless you put a link to put your file on which you have worked and thus you have saved me from having even more gray hair.
Now I answer Marcelo Branco and tell him that he has the same first name as my neighbor (and my neighbor is a very nice person like all the Marcelos).
Thank you for your proposal that I implemented since I know a little vba, it works very well and gives me the desired result.
Well done to you both and thank you again.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
358
mrshuck
M
L
  • Solved
auto repeating the dates in specific cells in column by writing in two input box
Replies
3
Views
118
mumps
mumps
H
  • Solved
Count nonblank lines (between blank lines)
2
Replies
15
Views
701
Alex Blakenburg
A
Dexir
  • Solved
Countifs query
Replies
3
Views
189
Dexir
Dexir
H
  • Solved
Creation of arrows for 2 family trees.
Replies
8
Views
186
harzer
H

Forum statistics

Threads
1,215,219
Messages
6,123,688
Members
449,117
Latest member
Aaagu

Share this page

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
Back
Top