need help reading out multiple cells of column as a string in a single cell

Hello everyone (and sorry for that poor description in my threadtitle)

I recently re-started working with Excel 2007 after many years of not using that software and am stuck with something already. I want to analyse genetic relationships among animals and have a table that goes like this:

column A: name of individuals, e.g.
A1: "animal1"
A2: "animal2"
A3: "animal3"
(...)
A100: "animal100"

column B: name of father of individual in A, e.g.
B1: "fatherA"
B2: "fatherA"
B3: "fatherB"
(...)
B100: "fatherQ"

Now, here comes my problem, as in the next column C I want to list out all individuals that were fathered by the very father in B as a string, for example, like this
C1: "animal1, animal2, (plus all others in column A with "fatherA")"
C2: "animal1, animal2, (plus all others in column A with "fatherA")"
C3: "animal3, (plus all others in column A with "fatherB")"
and so on...

I have spent the whole day trying to figure out what to put in cell C1 to produce the desired outcome, but without big success. Therefore, if anyone has a suggestion, I would greatly appreciate your help.

Thanks for your attention,
N

Use a User-Defined Function...

Have a read through this thread...

http://www.excelforum.com/excel-prog...ry-system.html

You can't accomplish this with builtin Excel functions. You have to add a new string concatenation function to your sheet.

My favorite UDF (User Defined Function) that accepts a "match parameter" is ConcatIF(), and I like it because it functions like a SUMIF() with two extra parameters, so I understand it.

Here's the code:

Option Explicit

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
            Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
    ' code base by Mike Rickson, MrExcel MVP
    ' used as exactly like SUMIF() with two additional parameters
    ' of delimiter and "no duplicates" as TRUE/FALSE if concatenated values
    ' might include duplicates  ex. =ConcatIf($A$1:$A$10, C1, $B$1:$B$10, ",", True)

Dim i As Long, j As Long

With compareRange.Parent
    Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
End With

If compareRange Is Nothing Then Exit Function
If stringsRange Is Nothing Then Set stringsRange = compareRange
Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
                                    stringsRange.Column - compareRange.Column)
    
    For i = 1 To compareRange.Rows.Count
        For j = 1 To compareRange.Columns.Count
            If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                    ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                End If
            End If
        Next j
    Next i
    ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End Function

How to install the User Defined Function:
1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The function is installed and ready to use.
==========

It's used just like a SUMIF() formula, but it has two extra parameters.
Let's put:
Animals in Column A
Fathers in Column B
A father to "match" in D1
Our list in E1...enter this formula in E1:

=ConCatIf(B1:B100,D1,A1:A100,", ",TRUE)

First parameter: Range to evaluate
Second param: comparison string
Third param: Range to return values from
Fourth param: The delimiting string for the concatenation, I'm using a comma/space in this example
Fifth param: (optional) TRUE means eliminate duplicated values, FALSE (or omitted) means string all values

Please post a sample workbook demonstrating a fully representative set of data and then a sample of your desired results.

Narayan~

Please start a new thread with a new title...