Ignore blank cells on formula to join text

[Maddy1234]

Hi all,

I am using a UDF from njimack to achieve the following result in Excel 2013.

[TABLE="class: cms_table_grid, width: 500, align: center"]
<tbody>[TR]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[TD]Column 3[/TD]
[TD]Formula result[/TD]
[/TR]
[TR]
[TD]1st[/TD]
[TD]2nd[/TD]
[TD]3rd[/TD]
[TD]1st, 2nd, 3rd[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]3rd[/TD]
[TD]3rd[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This is his code.

[COLOR=#333333]Function Concatenate_Range(myrange As Range, Optional myDelimiter As String)[/COLOR]Dim Cell As Range


Application.Volatile


For Each Cell In myrange
    If Len(Cell.Value) > 0 Then
        Concatenate_Range = Concatenate_Range & Cell & myDelimiter
    Else: Concatenate_Range = Concatenate_Range
    End If
Next Cell


If Len(myDelimiter) > 0 Then Concatenate_Range = Left(Concatenate_Range, Len(Concatenate_Range) - Len(myDelimiter))

 [COLOR=#333333]End Function[/COLOR]

This gives a fantastic result. However I wanted to improve it a bit. Instead of getting the result as "1st, 2nd, 3rd" I want the last comma to change to a "&".

So the desired result would be this. "1st, 2nd & 3rd" OR "1st & 2nd". You get the gist.

Any help is extremely appreciated. Thank you guys.

 

[Maddy1234]

Thank you for your kind reply Rick but as soon as I read your question I began to see the glaring problem in my application.

To answer your question, yes the cell B1 always starts with 1st but no, it will not always correspond to a Monday. For example 1st of March was Thursday. So for March 3rd and 4th will be weekends and should appear in Weekend OT Dates column.

Can you still solve this the way it is or will it be easier if we change the dates from just text to dates format.(dd/mm)

Your code works perfectly for the dummy data set though.

Assuming the answer to my above question is "yes", then give this completely revised function a try...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function ConcatOT() As String
  Dim Cell As Range, OTrng As Range
  Const Delimiter = ", "
  Application.Volatile
  Set OTrng = Intersect(Application.Caller.EntireRow, IIf(Application.Caller.Column = 9, Columns("B:F"), Columns("G:H")))
  For Each Cell In OTrng
    If Cell.Value > 0 Then ConcatOT = ConcatOT & Delimiter & Intersect(Cell.EntireColumn, Rows(1))
  Next
  ConcatOT = Mid(ConcatOT, Len(Delimiter) + 1)
  If InStr(ConcatOT, Delimiter) Then
    ConcatOT = Application.Replace(ConcatOT, InStrRev(ConcatOT, Delimiter), Len(Delimiter), " and ")
  End If
End Function[/TD]
[/TR]
</tbody>[/TABLE]

Note that the function named has changed to ConcatOT and that it takes no arguments. Simply put this in cell I2...

=ConcatOT()

and copy it across to cell J2, then copy them both down to the end of your data.

 

[Rick Rothstein]

Some questions...

Can I assume your headers do not stop at 7th then (that is, should I assume there are as many headers as a month has days)?

Where would I get the month and year at then (is that in cell A1 where you show what I thought was a header saying "Date")?

 

[Maddy1234]

Thanks a lot for your help Rick but I have found a solution in a different post. But I have a different problem now. If you have spare time please do have a look at it do provide some valuable insight.

Here's the link.

Assuming the answer to my above question is "yes", then give this completely revised function a try...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function ConcatOT() As String
  Dim Cell As Range, OTrng As Range
  Const Delimiter = ", "
  Application.Volatile
  Set OTrng = Intersect(Application.Caller.EntireRow, IIf(Application.Caller.Column = 9, Columns("B:F"), Columns("G:H")))
  For Each Cell In OTrng
    If Cell.Value > 0 Then ConcatOT = ConcatOT & Delimiter & Intersect(Cell.EntireColumn, Rows(1))
  Next
  ConcatOT = Mid(ConcatOT, Len(Delimiter) + 1)
  If InStr(ConcatOT, Delimiter) Then
    ConcatOT = Application.Replace(ConcatOT, InStrRev(ConcatOT, Delimiter), Len(Delimiter), " and ")
  End If
End Function[/TD]
[/TR]
</tbody>[/TABLE]

Note that the function named has changed to ConcatOT and that it takes no arguments. Simply put this in cell I2...

=ConcatOT()

and copy it across to cell J2, then copy them both down to the end of your data.

 

[Maddy1234]

You are absolutely correct in that there are as many headers as many days in a month. Since this was a huge issue I changed the headers to date format and achieved the result. Here's the post.

Some questions...

Can I assume your headers do not stop at 7th then (that is, should I assume there are as many headers as a month has days)?

Where would I get the month and year at then (is that in cell A1 where you show what I thought was a header saying "Date")?