Extracting values from Column

**abjeffre** · 12-01-2011, 04:30 PM

Hello,

I have three columns one with

(A)Person ID # (B) Fathers ID # C) Mother ID #

I want to find a way that I can derive another two collumns that would contain Ego ID # and Sibling ID # on a second sheet, or in columns J &K
So it would look:

(A) Person ID # (B)Sibling ID#

I need a formula that create two columns with all of the pairings of people who share Fathers and Mothers. In other words, create two new columns that have the pairs of Person ID #'s who have the same value for Father ID # and Mother ID #. Does this make sense? If possible I would like the formula to exclude those people whos father and mother ID #s are 0.

**NBVC** · 12-01-2011, 04:39 PM

PID's 2, 3 and 4 have the same parents.. how will column J and K look for this set?

**abjeffre** · 12-01-2011, 04:46 PM

J k
2 3
3 4
4 2

**NBVC** · 12-01-2011, 10:15 PM

Try this:

Duplicate column A to column J... then in K2 add a helper formula:

=B2&"_"&C2

copied down.

Then in L2:

=IF(OR(COUNTIF($K$2:$K$273,K2)=1,K2="0_0"),"",IF(COUNTIF(K$2:K2,K2)=COUNTIF($K$2:$K$273,K2),INDEX($J$2:$J$273,MATCH(K2,$K$2:$K$273,0)),INDEX($J$2:$J$273,SMALL(IF($K$2:$K$273=K2,IF($J$2:$J$273<>J2,ROW($J$2:$J$273)-ROW($J$2)+1)),COUNTIF(K$2:K2,K2)))))

confirmed with CTRL+SHIFT+ENTER and not just ENTER and copy down.

this will show blanks if the parents are 0's or if there are no siblings (i.e. duplicate parents).

See attached.

After you have done this, you can hide column K or you can move column K over to column I and delete the blank column K so that the helper is with the original...

**abjeffre** · 12-10-2011, 02:30 PM

Thank you that really helped,
Do you know if there is a way that I can automatically pair down columns J and L to only have values in when there are values in both columns?

**abjeffre** · 12-10-2011, 05:56 PM

There is a problem with the formula.

I have been using it on other sheets and when there is a situation where there is a large number of siblings it does not account for all of them.

Any Ideas how to correct it?