PDA

View Full Version : Solved: get all the names in the column



benong
03-23-2011, 06:52 PM
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
03-23-2011, 07:05 PM
You could use a filter. AutoFilter would be easiest, but AdvancedFilter's copy to other location and unique only features might be useful.

benong
03-23-2011, 07:42 PM
Thanks mikerickson,
is there a excel formulae for this job instead of using filter?

mikerickson
03-24-2011, 06:55 AM
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
03-24-2011, 06:17 PM
here is the sample file, thanks.

mikerickson
03-25-2011, 11:09 AM
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 argumens 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
03-27-2011, 06:44 PM
Dear Mikerickson,
Thank you so much for your guidance :)