How to identify pairings and repeats in population data

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?

Thanks for your reply, but that's not quite working as I'd hoped.

Line 5 and 6 are identical pairs to lines 2 and 3 but the other way round (BA rather than AB). Your formula has mistaken it for a different pair and created another pair_ID.

Thanks very much for your help. This assigns the a unique pair_ID perfectly.

Is there any way for the formula to also identify the pair further down the spreadsheet to save me having to add everything twice when a line contains the identical ID numbers for both birds and the same year?

For example, imagine columns C and D have no contents and I enter 'EF70113' into cell C2. I would like the formula to search all of column A for 'EF70113' and assuming column B contains '1977' as per line 2, it enters 'ED81312' into column C and also copies the pair_ID of '1' into column D for that corresponding line.

Essentially, as I complete line 2 in the example, line 5 will be completed also.

Sorry, maybe I'm just not explaining myself very well.

Within the spreadsheet, every 'pair' will appear twice for the year that they were together. Once in column A and again in column C once I've entered it.

To prevent me from entering data into column C 6000 times, is there a way for the spreadsheet to detect that the number that I've entered into column C also appears further on in column A and providing the year is the same, automatically copies the ID of its partner for that year.

Back to my initial 'human' population census as an example, let's suggest that the individual in cell A2 is Mr Jones, and in 1977 he lived with Mrs Jones and , thanks to your formula, a unique ID is created for 'Mr and Mrs Jones'.

Further down the spreadsheet, Mrs Jones will be listed in column A under 1977 also. Is there a way for a formula to realise that Mrs Jones , in 1977, was living with Mr Jones (as in line 2) and automatically complete the cells in column C with Mr Jones and therefore completing the unique ID for 'Mr and Mrs Jones' as in cell D2?

That is fantastic. Thank you for saving me so much time.

I really appreciate it