Concatenate Between Entries

**billykiller05** · 05-05-2008, 12:32 PM

I have the following list. I need a way to concatenate the dates between the new entry(s). The number of dates can vary between new entries.

New Entry
9/2005
10/2005
11/2005
New Entry
9/2005
10/2005
11/2005
New Entry
9/2005
10/2005
11/2005
New Entry
6/2006
7/2006
New Entry
6/2006
7/2006
New Entry
9/2006
10/2006
New Entry
12/2006
1/2007
New Entry
3/2007
4/2007
New Entry
3/2007
4/2007
5/2007
New Entry
4/2007
5/2007
6/2007
New Entry
4/2007
5/2007
6/2007
7/2007
New Entry
5/2007
6/2007
7/2007
8/2007
New Entry
5/2007
6/2007
8/2007
9/2007
New Entry
5/2007
6/2007
8/2007
9/2007
New Entry
6/2007
8/2007
9/2007
New Entry
6/2007
8/2007
9/2007
12/2007
New Entry
6/2007
8/2007
9/2007
12/2007
01/2008

**mrice** · 05-05-2008, 02:45 PM

This macro will create a comma separated concatentated list in column C.

Sub Test()
Columns(3).Clear
For N = 1 To Cells(65536, 1).End(xlUp).Row + 1
    If Cells(N, 1) = "New Entry" Or Cells(N, 1) = "" Then
        Counter = Counter + 1
        If Counter <> 1 Then
            Cells(Counter - 1, 3) = Left(Cells(Counter - 1, 3), Len(Cells(Counter - 1, 3)) - 1)
        End If
    Else
        Cells(Counter, 3) = Cells(Counter, 3) & Cells(N, 1) & ","
    End If
Next N
End Sub

**billykiller05** · 05-06-2008, 11:33 AM

I copied and pasted the macro but it keeps hanging up on the line below.

Cells(Counter, 3) = Cells(Counter, 3) & Cells(L, 1) & ","

**mrice** · 05-06-2008, 01:32 PM

Should be N not L as in the original reply.

Does this help?

**billykiller05** · 05-06-2008, 01:51 PM

I have attached my file. I would like a formula or a macro which can concatenate the mo/year between "New Entry" cells and enter the values in column E beside the "New Entry" cells as I manually did in cell E1 and E5.

**Bjornar** · 05-06-2008, 02:32 PM

Try this code:

Public Sub ConcatDates()
    Dim dRow As Double
    Dim dEntryRow As Double
    Dim sDates As String
    
    dRow = 1
    
    Do While Not Cells(dRow, 4).Value = ""
        If Cells(dRow, 4).Value = "New Entry" Then
            If dEntryRow > 0 Then
                Cells(dEntryRow, 5).Value = sDates
                sDates = ""
            End If
            dEntryRow = dRow
        Else
            sDates = sDates & Month(Cells(dRow, 4).Value) & "/" & Year(Cells(dRow, 4).Value)
            If Not Cells(dRow + 1, 4).Value = "New Entry" Then
                sDates = sDates & ", "
            End If
        End If
        dRow = dRow + 1
    Loop
    
    Cells(dEntryRow, 5).Value = sDates

End Sub