Get all the names in the column

[benong]

hi,
I'm having problems with excel.
column A contains the student name.
column B contains the student favourite fruit name.
i would like to know how to get the names of those student who likes apple? Pls advise, thanks.


col A col B
peter apple
alan orange
susan banana
john apple
mary apple
.... .....

Student who likes apple: peter, john, mary

[mikerickson]

You could use a filter. AutoFilter would be easiest, but AdvancedFilter's copy to other location and unique only features might be useful.

[benong]

Thanks mikerickson,
is there a excel formulae for this job instead of using filter?

[mikerickson]

Yes there are formulas that could be used. (The type of array formulas that I have in mind are ugly and will slow the workbook considerably.)

Could you post a (small) sample workbook, showing where the data is and the desired result.

[benong]

here is the sample file, thanks.

[mikerickson]

Ah.. I was thinking filtering rows, you were thinking concatenate into a single cell.

The UDF ConcatIf will do what you want.
In the attached formulas like =ConcatIf($A:$A, "apple", $B:$B, ", ") will do what you want.

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
Rem the first three arguments of ConcatIf mirror those of SUMIF
Rem the Delimiter and NoDuplicates arguments are optional (default "" and False)
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

[benong]

Dear Mikerickson,
Thank you so much for your guidance :)