concatenating and formatting dynamic number of strings

[jbenfleming]

So here is what I'm trying to accomplish. There is a number out to three decimals in column A. Date in column B (mm/dd/yyyy). Say I have this data:

[TABLE="width: 100"]
<tbody>[TR]
[TD]2.300[/TD]
[TD]8/1/2018[/TD]
[/TR]
[TR]
[TD]2.400[/TD]
[TD]9/1/2018[/TD]
[/TR]
[TR]
[TD]2.850[/TD]
[TD]10/1/2018[/TD]
[/TR]
</tbody>[/TABLE]


I want to concatenate this strings to where I get a single string of "2.33 until 08/01/2018, 2.400 until 09/01/2018, 2.850 until 10/01/2018"

I have accomplished this with the below formula. But as you can see it is quite messy. I will end up having this formula be able to work with any number of rows between 1 and 30, inclusive.

=LEFT(CONCATENATE(IF(ISNUMBER(A1),FIXED(A1,3) & " until " & TEXT(B1,"mm/dd/yyyy") & ", ",""),IF(ISNUMBER(A2),FIXED(A2,3) & " until " & TEXT(B2,"mm/dd/yyyy") & ", ",""),IF(ISNUMBER(A3),FIXED(A3,3) & " until " & TEXT(B3,"mm/dd/yyyy") & ", ","")),LEN(CONCATENATE(IF(ISNUMBER(A1),FIXED(A1,3) & " until " & TEXT(B1,"mm/dd/yyyy") & ", ",""),IF(ISNUMBER(A2),FIXED(A2,3) & " until " & TEXT(B2,"mm/dd/yyyy") & ", ",""),IF(ISNUMBER(A3),FIXED(A3,3) & " until " & TEXT(B3,"mm/dd/yyyy") & ", ","")))-2)

I know, it's pretty ridiculous. Wondering if any of you fine folks have a simpler solution in mind. Thanks and have a great day.

 

[Fluff]

At the moment you are adding ", " to the end of the string & then removing it. So you can simplify it slightly like
=CONCATENATE(IF(ISNUMBER(A1),FIXED(A1,3) & " until " & TEXT(B1,"mm/dd/yyyy") & ", ",""),IF(ISNUMBER(A2),FIXED(A2,3) & " until " & TEXT(B2,"mm/dd/yyyy") & ", ",""),IF(ISNUMBER(A3),FIXED(A3,3) & " until " & TEXT(B3,"mm/dd/yyyy"),""))

Do you need to keep this as formulae only, or would you be interested in a UDF, or normal macro?

 

[jbenfleming]

Only problem is that if i were to use this formula for say, 3 rows, and then delete the third row. It would update but leave a comma on the end. That's why I wrap the thing with that =left. I'd rather avoid a macro, it would be easy to write but this is for a coworker and I always try to avoid macros when helping others. Didn't think about making a UDF though. Not sure where to start with that.

 

[Fluff]

How about

Function CreateText(Rng As Range) As String
   Dim Cl As Range
   For Each Cl In Rng
      If IsNumeric(Cl.Value) And Cl <> "" Then
         If CreateText = "" Then
            CreateText = Application.Fixed(Cl, 3) & " until " & format(Cl.Offset(, 1), "mm/dd/yyyy")
         Else
            CreateText = CreateText & ", " & Application.Fixed(Cl, 3) & " until " & format(Cl.Offset(, 1), "mm/dd/yyyy")
         End If
      End If
   Next Cl
End Function

Used like
=CreateText(A1:A3)

 

[jbenfleming]

This is perfect. Thank you so much!

 

[Fluff]

Glad to help & thanks for the feedback