VBA to combine related strings into one cell, with char(10) separator

[squidmark]

Hi everyone.

Please, save me from the purgatory that is going through 62,000 lines manually.

I've got a list of notes some 62,000 lines long. Column A is the customer number. Column B is the note.

I need to combine all of the notes in column B into a single cell with the other notes relating to the common customer, with a carriage return (char(10)) in between, putting them each on a different line in the same cell.

There are customers with more than 50 notes, so no form of if/then is going to work (that I know of).

Sample Data, each line is in a different row, so the following would be in the A1:B13 range:

ColA ColB

3500 8/18/2010 - lvm for A/P
3500 1/4/2011 - emld RE pmt stat
3500 2/3/2011 - emld RE pmt stat
3848 2/3/2011 - lvm for A/P
3848 1/13/2011 - lvm for A/P
3848 1/28/2011 - lvm for A/P
3860 2/3/2011 - emld RE pmt stat
3860 3/8/2011 - emld RE pmt stat
3860 4/5/2011 - emld RE pmt stat
3860 3/4/2011 - lvm for A/P
3860 2/10/2011 - lvm for A/P
3860 8/18/2010 - clld & n/a
3860 8/12/2010 - called & N/A

The sample below would end up in three cells, as in the following (the range here would be A1:B3, as all the notes for customer 3500 are in cell B1, all those for customer 3848 are in cell B2, etc...)

ColA ColB
3500 8/18/2010 - lvm for A/P
....... 1/4/2011 - emld RE pmt stat
....... 2/3/2011 - emld RE pmt stat
3848 2/3/2011 - lvm for A/P
...... 1/13/2011 - lvm for A/P
...... 1/28/2011 - lvm for A/P
3860 2/3/2011 - emld RE pmt stat
...... 3/8/2011 - emld RE pmt stat
...... 4/5/2011 - emld RE pmt stat
...... 3/4/2011 - lvm for A/P
...... 2/10/2011 - lvm for A/P
...... 8/18/2010 - clld & n/a
...... 8/12/2010 - called & N/A

(the dots are supposed to be blanks, but I'm having formatting issues)

There has to be a good VBA way to do this. But I'm just starting to learn VBA and this is kicking my rear-end.

 

[mirabeau]

Hi Squidmark,

Seems a straightforward enough problem which, for 60,000 or so rows, shouldn't really take more than a second. You're happy so far so that's good. But, just for fun, try this code on your (copied to a test sheet) data. Data in ColsA and B, output is in Cols E and F, but can easily overwrite A and B if you like.

Sub cellnotes()
Dim t As Single
t = Timer
Dim lr&, a, d As Object, i&, c()
Dim x, y
lr = Range("A" & Rows.Count).End(3).Row
ReDim c(1 To lr, 1 To 2)
a = Range("A1").Resize(lr, 2)
Set d = CreateObject("scripting.dictionary")
d.comparemode = 1
For i = 1 To lr
x = a(i, 1): y = a(i, 2)
   If Not d.exists(x) Then
        k = k + 1
        d(x) = k
        c(k, 1) = x: c(k, 2) = y
    Else
         c(d(x), 2) = c(d(x), 2) & Chr(10) & y
    End If
Next i
[e1].Resize(k, 2) = c
MsgBox "Code took " & Format(Timer - t, "0.000") & " secs."
End Sub

 

[squidmark]

You're welcome.

    [COLOR="Red"]Columns("B:B").WrapText = False[/COLOR]
    Application.ScreenUpdating = True

End Sub

Macro Tip: you can can record a small macro to get example bits of code to add to your main macro.

Thanks. What I meant was, when text wrapping is off, new notes are not on a new line in the cell. It's like there is no carriage return within the cell.

But with the wrapping on, the notes that go longer than 255 characters wrap and show on a second line within the cell. Ideally, each line in each cell of column B would start with a date, and the really long notes would just overlap into the blank columns to the right

This is getting ticky-tack, and I feel kind of silly asking for this when I've gotten so much charitable help from you already. I do greatly appreciate your help.

 

[shg]

Ideally, each line in each cell of column B would start with a date, and the really long notes would just overlap into the blank columns to the right

You can't have it both ways; if wrap text is on, all the text will wrap; if it's not, line feeds are ignored.

 

[squidmark]

You can't have it both ways; if wrap text is on, all the text will wrap; if it's not, line feeds are ignored.

OK. Thanks.