Results 1 to 3 of 3

Thread: How to identify pairings and repeats in population data

  1. #1

    How to identify pairings and repeats in population data



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?
    Attached Files Attached Files

  2. #2
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    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.

  3. #3
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    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)

    Click image for larger version. 

Name:	example.jpg 
Views:	1 
Size:	22.3 KB 
ID:	2630
    Last edited by WizzardOfOz; 2014-09-09 at 02:04 AM. Reason: auto correct fail

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •