How to identify pairings and repeats in population data

[eight1four]

Hi guys

I have a problem that I hope someone can help me to solve.

I have a spreadsheet containing the ID numbers of over 6000 individuals within a population and the years in which they bred. My task is to input the breeding pair for each bird and assign a unique ID number to each 'pair' of birds.

I have attached a completed example to this post.

To simplify things, if we imagine that it's human population data of married couples within a town where:

Column A represents ALL individuals
Column B represents the year of the census
Column C represents the spouse of the individual in column A
Column D is a unique assigned ID number for that 'couple'

As column A is made up of ALL individuals and almost all couples appear in more that one census, I'm looking for a way to simplify my data input.

As I enter data into column C, I want that 'couple's' data to be copied into the corresponding cells for that couple further down the spreadsheet where the spouse of that couple is listed, providing the year is correct of course.

Example:

As I enter the ID into cell C8 and D8, the data from A8 and D8 are entered into cells C9 and D9 respectively as the year in B8 and B9 are identical.

It is important that only the pairs in the correct years are copied. If you look at line 12, one of the individuals from a previous pair have a different partner and therefore a new unique pair_ID has been created.

Is that something that's possible?

 

[WizzardOfOz]

Yes in your example I created two named ranges Male_bird for column A and Female_Bird for column C

Then in D19 you need the formula
=COUNTIFS(Male_Bird,"=" & A19,Female_Bird,"=" & C19)

And then copy it down.

 

[WizzardOfOz]

Sorry forgot to add that I would recommend changing the formula to values. Otherwise you have problems on sorting and inserting rows.

Alternatively change named ranges to
=COUNTIFS($A$2:A19,"=" & A19,$C$2:C19,"=" &C19)
setting the start absolute and the end to the relative row position

If you add new rows you will need to copy the formula down from the top to the end again, changing the ID field.

Alternatively suggest get rid of the ID and simply use a pivot table. (row: Indiv_ID, Partner, col: Year, Values: Count of year)