Find all occurrences and return value of the top cell in a range

Question

Hi,

I am trying the find all occurrences of a value (e.g. cat in my sample screenshot) in each month (e.g. Dec, Jan etc.) and collect all the days (e.g. 1, 4) that it occurs. I then need to do that for other values (e.g. horse, dog etc.)

The value may or may not appear in cell range, and if it does appear it only appears once in a day.

Screenshot of worksheet

I have tried array formulae of index() and match() but couldn't get my head around to get something to work.

The result on another worksheet I am trying to achieve is:

cat

Dec 1 4

Jan 2

dog

Dec

Jan 1

horse

Dec 3 4

Jan

I just need to know how to collect the days.

Thanks.

Answer 1

Modification of UDF code from Concatenate top row cells if column below has 1.

Option Explicit

Public Function conditional_concat(rSTRs As Range, rCRITs As Range, sCRIT As String, _
                                   sLBL As String, Optional sDELIM As String = ", ")
    Dim c As Long, sTMP As String
    For c = 1 To rCRITs.Cells.Count
        If LCase(rCRITs(c).Value2) = LCase(sCRIT) Then _
            sTMP = sTMP & sDELIM & rSTRs.Cells(1, rCRITs(c).Column).Value
    Next c
    conditional_concat = sLBL & Chr(32) & Mid(sTMP, Len(sDELIM) + 1)
End Function