Grouping Sibling ID into Family ID

Hi,

I have an Excel file where I have the columns:
- Individual ID
- Year (either 89 or 94 - this is not particularly relevant for what I want to do in Excel right now but I will need it for some additional purpose later on)
- Sibling ID
- Family ID (currently blank)

I basically want to group individuals into families. If individuals do not have a family, then they will have their own family ID. If individuals have siblings, then they will share the family ID with their sibling. I would like the family IDs to be in ascending order from 1.

Some information on how the sibling ID column works: In the sibling ID column, the individual fills in the "Individual ID" of their sibling, so for example, if Individual 34's sister is Individual 35, Individual 34 would fill in 35 in the "Sibling ID" column and Individual 35 would fill in 34 in the "Sibling ID" column.

I don't think this should cause an issue but sometimes, a sibling ID is provided but the individual with that corresponding ID is not in the dataset (for example, Individual ID 24 provides a Sibling ID of 23 but Individual 23 is not in the dataset).

Also, if there are more than 2 siblings in the dataset (for example, for Individual ID 58, 59 and 60), then the Sibling ID they provide looks like this:
- Individual 58 provides a Sibling ID of 59
- Individual 59 provides a Sibling ID of 60
- Individual 60 provides a Sibling ID of 58

Please see the Excel file for an example

Welcome to the forum.

Maybe this will help you create a solution.

I am having a hard time following your description but have included a system of Id numbering that starts off with 1 number and from there digits are added for each generation. I should be obvious what constitutes a family in the example. To create a listing just use the number generated by the diagram for each individual. I have no idea what the years signify in your example.

In the diagram that I have given you, siblings just add 1 digit to the family ID.

One way that you could do something like this is to assign a number to each family. Follow the family number enter a - and assign a number like 1 for father 2 for mother 3 and up for children. When the children have children, their ID would be the family number with their individual number. For example, family 83 has a father and mother 83-1 and 83-2 they have 3 children 83-3, 83-4, 83-5. 83-3 gets married so the family would be 83-3-1 and 83-3-2 for the parents, the children would be 83-3-3, 83-3-4 etc..

Another way is to assign a family number then each member of the family is assigned a consecutive number.. When a child starts a family, the family number is the original family number with the individual ID appended...I show this on Sheet2.

There are many ways of doing this. The trick is to design something that will give you the results that you want for the future.