0

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.

Edmond
  • 15
  • 4
  • If you find a VBA UDF solution too difficult, search the [tag:textjoin] tag. –  Dec 06 '17 at 02:23
  • That is **exactly** what the marked duplicate does. –  Dec 06 '17 at 02:29
  • Thank you, @Jeeped. I am studying it now. I will see how to join the dates in order. – Edmond Dec 06 '17 at 02:33
  • @Jeeped, do I delete this question now? – Edmond Dec 06 '17 at 02:33
  • If you have found an answer, leave this question so that others may use it to find the marked duplicate. If that duplicate has **not** answered your inquiry, then edit this question sufficiently that we can understand why it was not answered. –  Dec 06 '17 at 02:35
  • @Jeeped, the VBA UDF solution is pretty close, can I work in a cell range instead of by row? – Edmond Dec 06 '17 at 02:56
  • Did you try it? –  Dec 06 '17 at 02:57
  • Yes, I have tried it by changing =conditional_concat(A$1:E$1, A2:E2) to =conditional_concat(A$1:E$1, A2:E3) but the result does not include the second row. – Edmond Dec 06 '17 at 03:05

1 Answers1

0

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

enter image description here

  • can you please have a look at the function? When I tried for 'horse', it only gives me Dec 3 not Dec 3, 4. I have =conditional_concat(A1:H1,A2:H3,K2,A1) – Edmond Dec 06 '17 at 03:57
  • I have changed it to "For c = 1 To rCRITs.Cells.Count" from "For c = 1 To rSTRs.Cells.Count" and it works although it do it by rows then columns. That's okay. Thank you so much @Jeeped. – Edmond Dec 06 '17 at 04:07