Finding relatives from a spreadsheet

[Mohammedyousif]

Hello all,

I just joined a company as HR manager. There are about 1000 people in our payroll and I want to know who is related to who. Where I live, a requirement to be hired is to provide all your IDs and from these IDs I managed to make a spreadsheet similar to the one attached. It contains these columns:
Person Father's Name Grand Father's Name Great Grandfather's name Last Name Mother's Name Maternal Grandfather's Name Maternal Greafather's Name Mother's Maiden Name (Mother's Last Name)

I want to identify
1- Brothers
2- Paternal Cousins
3- People who share the same Paternal grandfather
4- Maternal Cousins
5- People who share the same Maternal grandmother

Note: In the list I attached you will find dummy names so there might not be real connections. In the spreadsheet I have, there are almost 1000 employees. Most of them are family. Identifying them is essential for the corporate.

Can excel support this sort of data filtering using formulas?

Thanks!

[mehmetcik]

Enter these formulas in the cells listed
Select J2 to P2
Fill down.

J2 =IF(or(B2=B3,B2=B1),1,"")
K2 =IF(or(C3&D3 =C2&D2, C3&D3 =C1&D1),1,"")
L2 =IF(or(B3&C3&D3 =B2&C2&D2, B3&C3&D3 =B1&C1&D1),1,"")
M2 =IF(or(F2=F3, F2=F1),1,"")
N2 =IF(or(F2&G2=F3&G3, F2&G2=F1&G1),1,"")
O2 =IF(or(F2&G2&H2=F3&G3&H3, F2&G2&H2=F1&G1&H1),1,"")
P2 =IF(or(F2&G2&H2&I2=F3&G3&H3&I3, F2&G2&H2&I2=F1&G1&H1&I1),1,"")

Sort by one of your headings and any matches will be flagged.