List Based On Matches

[deZine]

I am looking for a way to show a list within one cell based on matching criteria. I would prefer to accomplish this without VBA if possible.



If the name in column AH matches AI10 then include the number that is in column I in the list.

Cell AJ10 is the desired output.

Please let me know if my description is inadequate. Thanks in advance for your help!

 

[sheetspread]

We see requests for these from time to time. The problem is that excel cannot concatenate a range by formula (at least in versions 2003 and 2007). I know a simple UDF that will do this for you, though. Let me know if your no VBA preference is flexible.

 

[deZine]

Thanks Sheetspread. If VBA is the only way to go then I am flexible. I am not sure if it matters, but the data does not start until row 516 and will go no further than 546. Thanks again for your help.

 

[sheetspread]

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, _ 
    Optional ByVal stringsRange As Range, Optional Delimiter As String) As String 
    Dim i As Long, j As Long, criteriaMet As Boolean 
     
    Set compareRange = Application.Intersect(compareRange, _ 
    compareRange.Parent.UsedRange) 
     
    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 
                ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j)) 
            End If 
        Next j 
    Next i 
    ConcatIf = mid(ConcatIf, Len(Delimiter) + 1) 
End Function

Excel Workbook
	I	AH	AI	AJ	AK
516	3	John	*	*	*
517	4	John	*	*	*
518	5	Mike	*	*	*
519	6	Steve	*	*	*
520	7	Robert	*	*	*
521	8	John	*	*	*
522	9	Robert	*	*	*
523	*	*	*	*	*
524	*	*	*	*	*
525	*	*	*	*	*
526	*	*	*	*	*
527	*	*	*	*	*
528	*	*	*	*	*
529	*	*	*	*	*
530	*	*	*	*	*
531	*	*	*	*	*
532	*	*	*	*	*
533	*	*	*	*	*
534	*	*	*	*	*
535	*	*	*	*	*
536	*	*	*	*	*
537	*	*	*	*	*
538	*	*	*	*	*
539	*	*	*	*	*
540	*	*	*	*	*
541	*	*	*	*	*
542	*	*	*	*	*
543	*	*	*	*	*
544	*	*	*	*	*
545	*	*	*	*	*
546	*	*	*	*	*
547	*	*	John	Number 3,4,8	*
Sheet1

VBA genius Mike Rickson wrote the code

 

[deZine]

Works perfectly! Thanks sheetspread!

 

[deZine]

Thank you very much for your help. I have to admit that I don't really understand the solution that you gave me. I have a very similar issue that I am trying to adapt your solution to. In this instance I need it to check two columns at the same time for matches.

This is what I tried without success:
="Number "&ConcatIf((AN516:AN546,AL516:AL546),B26,I516:I546,",")

 

[sheetspread]

I'll keep trying, but modifying the code hasn't gotten me there yet. ConcatIf seems to be like sumif in being limited to one condition. There are multi-criteria concatenation macros on the web, those too require some adjustment for this array.