Concatenate

[Helpless1]

HELP! I am a very minimal user and have a huge annoying project that I keep
wondering if there is an easier way to accomplish. I am doing conversion work
and have about 29000 lines left of customer information. When the info from
our old system was imported the customer notes were inserted in a new cell
with every line of text. My current method is to filter by customer number
then concatenate every line of customer notes, then copy and paste special as
values. I then delete the other lines of text and move to the next customer
number. PLEASE tell me there is any easier way! Restore my sanity!

[Tom Ogilvy]

Assume the customer number is in column A and the text in column B

select the cells in column A that you want to process, then run this macro

Sub ProcessData()
Dim rng As Range, cell As Range, s As String
Set rng = Selection(1)
For Each cell In Selection
If cell.Value <> cell.Offset(1, 0).Value Then
s = s & " " & cell.Value
rng.Offset(0, 1).Value = Application.Trim(s)
If rng.Row <> cell.Row Then
Range(rng.Offset(1, 0), cell).Resize(, 2).ClearContents
End If
Set rng = cell.Offset(1, 0)
s = ""
Else
s = s & " " & cell.Value
End If

Next
Columns(2).SpecialCells(xlBlanks).EntireRow.Delete
End Sub


Test this on a copy of your data

--
Regards,
Tom Ogilvy

"Helpless1" wrote:

> HELP! I am a very minimal user and have a huge annoying project that I keep
> wondering if there is an easier way to accomplish. I am doing conversion work
> and have about 29000 lines left of customer information. When the info from
> our old system was imported the customer notes were inserted in a new cell
> with every line of text. My current method is to filter by customer number
> then concatenate every line of customer notes, then copy and paste special as
> values. I then delete the other lines of text and move to the next customer
> number. PLEASE tell me there is any easier way! Restore my sanity!

[Bob Phillips]

Is it all of a standard layout, that is customer name, say 5 lines of notes?
If so this should do it

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim rng As Range
Const nBlock As Long = 5 '<=== change to suit

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
If iLastRow Mod nBlock <> 0 Then
iLastRow = iLastRow + nBlock - iLastRow Mod 5
End If
For i = iLastRow - 4 To 1 Step -nBlock
Cells(i, "B").Value = Cells(i + 1, "A").Value
Cells(i, "C").Value = Cells(i + 2, "A").Value
Cells(i, "D").Value = Cells(i + 3, "A").Value
Cells(i, "E").Value = Cells(i + 4, "A").Value
If rng Is Nothing Then
Set rng = Rows(i + 1).Resize(4)
Else
Set rng = Union(rng, Rows(i + 1).Resize(4))
End If
Next i

If Not rng Is Nothing Then rng.Delete

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Helpless1" <Helpless1@discussions.microsoft.com> wrote in message
news:4D0A82E3-C455-4D75-A4D1-F093AE98095E@microsoft.com...
> HELP! I am a very minimal user and have a huge annoying project that I
keep
> wondering if there is an easier way to accomplish. I am doing conversion
work
> and have about 29000 lines left of customer information. When the info
from
> our old system was imported the customer notes were inserted in a new cell
> with every line of text. My current method is to filter by customer number
> then concatenate every line of customer notes, then copy and paste special
as
> values. I then delete the other lines of text and move to the next
customer
> number. PLEASE tell me there is any easier way! Restore my sanity!

[crazybass2]

If I understand Helpless1 correctly, the notes are in the second column so "s
= s & " " & cell.offset(0,1).value"

If I'm correct, Tom's code should be:

Sub ProcessData()
Dim rng As Range, cell As Range, s As String
Set rng = Selection(1)
For Each cell In Selection
If cell.Value <> cell.Offset(1, 0).Value Then
s = s & " " & cell.Offset(0, 1).Value
rng.Offset(0, 1).Value = Application.Trim(s)
If rng.Row <> cell.Row Then
Range(rng.Offset(1, 0), cell).Resize(, 2).ClearContents
End If
Set rng = cell.Offset(1, 0)
s = ""
Else
s = s & " " & cell.Offset(0, 1).Value
End If

Next
Columns(2).SpecialCells(xlBlanks).EntireRow.Delete
End Sub


Mike

"Tom Ogilvy" wrote:

> Assume the customer number is in column A and the text in column B
>
> select the cells in column A that you want to process, then run this macro
>
> Sub ProcessData()
> Dim rng As Range, cell As Range, s As String
> Set rng = Selection(1)
> For Each cell In Selection
> If cell.Value <> cell.Offset(1, 0).Value Then
> s = s & " " & cell.Value
> rng.Offset(0, 1).Value = Application.Trim(s)
> If rng.Row <> cell.Row Then
> Range(rng.Offset(1, 0), cell).Resize(, 2).ClearContents
> End If
> Set rng = cell.Offset(1, 0)
> s = ""
> Else
> s = s & " " & cell.Value
> End If
>
> Next
> Columns(2).SpecialCells(xlBlanks).EntireRow.Delete
> End Sub
>
>
> Test this on a copy of your data
>
> --
> Regards,
> Tom Ogilvy
>
> "Helpless1" wrote:
>
> > HELP! I am a very minimal user and have a huge annoying project that I keep
> > wondering if there is an easier way to accomplish. I am doing conversion work
> > and have about 29000 lines left of customer information. When the info from
> > our old system was imported the customer notes were inserted in a new cell
> > with every line of text. My current method is to filter by customer number
> > then concatenate every line of customer notes, then copy and paste special as
> > values. I then delete the other lines of text and move to the next customer
> > number. PLEASE tell me there is any easier way! Restore my sanity!

[Helpless1]

Tom,
Thanks so much for your help! I should have mentioned before however that I
have other information between the customer number and note. The customer
number is in column A and the notes are in column K. This other information
is the contact information which appears with every row of notes. I need to
end up with only one row for each customer with the notes in column K in one
cell. I cannot tell you how much I appreciate you looking at this for me!

-Helpless1

"Tom Ogilvy" wrote:

> Assume the customer number is in column A and the text in column B
>
> select the cells in column A that you want to process, then run this macro
>
> Sub ProcessData()
> Dim rng As Range, cell As Range, s As String
> Set rng = Selection(1)
> For Each cell In Selection
> If cell.Value <> cell.Offset(1, 0).Value Then
> s = s & " " & cell.Value
> rng.Offset(0, 1).Value = Application.Trim(s)
> If rng.Row <> cell.Row Then
> Range(rng.Offset(1, 0), cell).Resize(, 2).ClearContents
> End If
> Set rng = cell.Offset(1, 0)
> s = ""
> Else
> s = s & " " & cell.Value
> End If
>
> Next
> Columns(2).SpecialCells(xlBlanks).EntireRow.Delete
> End Sub
>
>
> Test this on a copy of your data
>
> --
> Regards,
> Tom Ogilvy
>
> "Helpless1" wrote:
>
> > HELP! I am a very minimal user and have a huge annoying project that I keep
> > wondering if there is an easier way to accomplish. I am doing conversion work
> > and have about 29000 lines left of customer information. When the info from
> > our old system was imported the customer notes were inserted in a new cell
> > with every line of text. My current method is to filter by customer number
> > then concatenate every line of customer notes, then copy and paste special as
> > values. I then delete the other lines of text and move to the next customer
> > number. PLEASE tell me there is any easier way! Restore my sanity!

[crazybass2]

In the code I sent, change the Offset(0,1) to Offest(0,10) for instance if
Customer ID is in column A and Notes in column K.

Mike

"Helpless1" wrote:

> Tom,
> Thanks so much for your help! I should have mentioned before however that I
> have other information between the customer number and note. The customer
> number is in column A and the notes are in column K. This other information
> is the contact information which appears with every row of notes. I need to
> end up with only one row for each customer with the notes in column K in one
> cell. I cannot tell you how much I appreciate you looking at this for me!
>
> -Helpless1
>
> "Tom Ogilvy" wrote:
>
> > Assume the customer number is in column A and the text in column B
> >
> > select the cells in column A that you want to process, then run this macro
> >
> > Sub ProcessData()
> > Dim rng As Range, cell As Range, s As String
> > Set rng = Selection(1)
> > For Each cell In Selection
> > If cell.Value <> cell.Offset(1, 0).Value Then
> > s = s & " " & cell.Value
> > rng.Offset(0, 1).Value = Application.Trim(s)
> > If rng.Row <> cell.Row Then
> > Range(rng.Offset(1, 0), cell).Resize(, 2).ClearContents
> > End If
> > Set rng = cell.Offset(1, 0)
> > s = ""
> > Else
> > s = s & " " & cell.Value
> > End If
> >
> > Next
> > Columns(2).SpecialCells(xlBlanks).EntireRow.Delete
> > End Sub
> >
> >
> > Test this on a copy of your data
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Helpless1" wrote:
> >
> > > HELP! I am a very minimal user and have a huge annoying project that I keep
> > > wondering if there is an easier way to accomplish. I am doing conversion work
> > > and have about 29000 lines left of customer information. When the info from
> > > our old system was imported the customer notes were inserted in a new cell
> > > with every line of text. My current method is to filter by customer number
> > > then concatenate every line of customer notes, then copy and paste special as
> > > values. I then delete the other lines of text and move to the next customer
> > > number. PLEASE tell me there is any easier way! Restore my sanity!

[crazybass2]

If I understand Helpless1 correctly, the notes are in the second column so "s
= s & " " & cell.offset(0,1).value"

If I'm correct, Tom's code should be:

Sub ProcessData()
Dim rng As Range, cell As Range, s As String
Set rng = Selection(1)
For Each cell In Selection
If cell.Value <> cell.Offset(1, 0).Value Then
s = s & " " & cell.Offset(0, 1).Value
rng.Offset(0, 1).Value = Application.Trim(s)
If rng.Row <> cell.Row Then
Range(rng.Offset(1, 0), cell).Resize(, 2).ClearContents
End If
Set rng = cell.Offset(1, 0)
s = ""
Else
s = s & " " & cell.Offset(0, 1).Value
End If

Next
Columns(2).SpecialCells(xlBlanks).EntireRow.Delete
End Sub


Mike

"Tom Ogilvy" wrote:

> Assume the customer number is in column A and the text in column B
>
> select the cells in column A that you want to process, then run this macro
>
> Sub ProcessData()
> Dim rng As Range, cell As Range, s As String
> Set rng = Selection(1)
> For Each cell In Selection
> If cell.Value <> cell.Offset(1, 0).Value Then
> s = s & " " & cell.Value
> rng.Offset(0, 1).Value = Application.Trim(s)
> If rng.Row <> cell.Row Then
> Range(rng.Offset(1, 0), cell).Resize(, 2).ClearContents
> End If
> Set rng = cell.Offset(1, 0)
> s = ""
> Else
> s = s & " " & cell.Value
> End If
>
> Next
> Columns(2).SpecialCells(xlBlanks).EntireRow.Delete
> End Sub
>
>
> Test this on a copy of your data
>
> --
> Regards,
> Tom Ogilvy
>
> "Helpless1" wrote:
>
> > HELP! I am a very minimal user and have a huge annoying project that I keep
> > wondering if there is an easier way to accomplish. I am doing conversion work
> > and have about 29000 lines left of customer information. When the info from
> > our old system was imported the customer notes were inserted in a new cell
> > with every line of text. My current method is to filter by customer number
> > then concatenate every line of customer notes, then copy and paste special as
> > values. I then delete the other lines of text and move to the next customer
> > number. PLEASE tell me there is any easier way! Restore my sanity!

[Helpless1]

Tom,
Thanks so much for your help! I should have mentioned before however that I
have other information between the customer number and note. The customer
number is in column A and the notes are in column K. This other information
is the contact information which appears with every row of notes. I need to
end up with only one row for each customer with the notes in column K in one
cell. I cannot tell you how much I appreciate you looking at this for me!

-Helpless1

"Tom Ogilvy" wrote:

> Assume the customer number is in column A and the text in column B
>
> select the cells in column A that you want to process, then run this macro
>
> Sub ProcessData()
> Dim rng As Range, cell As Range, s As String
> Set rng = Selection(1)
> For Each cell In Selection
> If cell.Value <> cell.Offset(1, 0).Value Then
> s = s & " " & cell.Value
> rng.Offset(0, 1).Value = Application.Trim(s)
> If rng.Row <> cell.Row Then
> Range(rng.Offset(1, 0), cell).Resize(, 2).ClearContents
> End If
> Set rng = cell.Offset(1, 0)
> s = ""
> Else
> s = s & " " & cell.Value
> End If
>
> Next
> Columns(2).SpecialCells(xlBlanks).EntireRow.Delete
> End Sub
>
>
> Test this on a copy of your data
>
> --
> Regards,
> Tom Ogilvy
>
> "Helpless1" wrote:
>
> > HELP! I am a very minimal user and have a huge annoying project that I keep
> > wondering if there is an easier way to accomplish. I am doing conversion work
> > and have about 29000 lines left of customer information. When the info from
> > our old system was imported the customer notes were inserted in a new cell
> > with every line of text. My current method is to filter by customer number
> > then concatenate every line of customer notes, then copy and paste special as
> > values. I then delete the other lines of text and move to the next customer
> > number. PLEASE tell me there is any easier way! Restore my sanity!

[crazybass2]

In the code I sent, change the Offset(0,1) to Offest(0,10) for instance if
Customer ID is in column A and Notes in column K.

Mike

"Helpless1" wrote:

> Tom,
> Thanks so much for your help! I should have mentioned before however that I
> have other information between the customer number and note. The customer
> number is in column A and the notes are in column K. This other information
> is the contact information which appears with every row of notes. I need to
> end up with only one row for each customer with the notes in column K in one
> cell. I cannot tell you how much I appreciate you looking at this for me!
>
> -Helpless1
>
> "Tom Ogilvy" wrote:
>
> > Assume the customer number is in column A and the text in column B
> >
> > select the cells in column A that you want to process, then run this macro
> >
> > Sub ProcessData()
> > Dim rng As Range, cell As Range, s As String
> > Set rng = Selection(1)
> > For Each cell In Selection
> > If cell.Value <> cell.Offset(1, 0).Value Then
> > s = s & " " & cell.Value
> > rng.Offset(0, 1).Value = Application.Trim(s)
> > If rng.Row <> cell.Row Then
> > Range(rng.Offset(1, 0), cell).Resize(, 2).ClearContents
> > End If
> > Set rng = cell.Offset(1, 0)
> > s = ""
> > Else
> > s = s & " " & cell.Value
> > End If
> >
> > Next
> > Columns(2).SpecialCells(xlBlanks).EntireRow.Delete
> > End Sub
> >
> >
> > Test this on a copy of your data
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Helpless1" wrote:
> >
> > > HELP! I am a very minimal user and have a huge annoying project that I keep
> > > wondering if there is an easier way to accomplish. I am doing conversion work
> > > and have about 29000 lines left of customer information. When the info from
> > > our old system was imported the customer notes were inserted in a new cell
> > > with every line of text. My current method is to filter by customer number
> > > then concatenate every line of customer notes, then copy and paste special as
> > > values. I then delete the other lines of text and move to the next customer
> > > number. PLEASE tell me there is any easier way! Restore my sanity!

[Tom Ogilvy]

Here is a revision. Assumes you have a header row in row 1 and data starts
in row2. Again, test on a copy of your data.

Sub ProcessData()
Dim rng As Range, cell As Range, s As String
Set rng = Range(cells(2,1),Row.count,1).End(xlup))
For Each cell In rng
If cell.Value <> cell.Offset(1, 0).Value Then
s = s & " " & cell.Offset(0,10).Value
rng.Offset(0, 10).Value = Application.Trim(s)
If rng.Row <> cell.Row Then
Range(rng.Offset(1, 0), cell).Entirerow.ClearContents
End If
Set rng = cell.Offset(1, 0)
s = ""
Else
s = s & " " & cell.Offset(0,10).Value
End If

Next
Columns(10).SpecialCells(xlBlanks).EntireRow.Delete
End Sub

--
Regards,
Tom Ogilvy

"Helpless1" wrote:

> Tom,
> Thanks so much for your help! I should have mentioned before however that I
> have other information between the customer number and note. The customer
> number is in column A and the notes are in column K. This other information
> is the contact information which appears with every row of notes. I need to
> end up with only one row for each customer with the notes in column K in one
> cell. I cannot tell you how much I appreciate you looking at this for me!
>
> -Helpless1
>
> "Tom Ogilvy" wrote:
>
> > Assume the customer number is in column A and the text in column B
> >
> > select the cells in column A that you want to process, then run this macro
> >
> > Sub ProcessData()
> > Dim rng As Range, cell As Range, s As String
> > Set rng = Selection(1)
> > For Each cell In Selection
> > If cell.Value <> cell.Offset(1, 0).Value Then
> > s = s & " " & cell.Value
> > rng.Offset(0, 1).Value = Application.Trim(s)
> > If rng.Row <> cell.Row Then
> > Range(rng.Offset(1, 0), cell).Resize(, 2).ClearContents
> > End If
> > Set rng = cell.Offset(1, 0)
> > s = ""
> > Else
> > s = s & " " & cell.Value
> > End If
> >
> > Next
> > Columns(2).SpecialCells(xlBlanks).EntireRow.Delete
> > End Sub
> >
> >
> > Test this on a copy of your data
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Helpless1" wrote:
> >
> > > HELP! I am a very minimal user and have a huge annoying project that I keep
> > > wondering if there is an easier way to accomplish. I am doing conversion work
> > > and have about 29000 lines left of customer information. When the info from
> > > our old system was imported the customer notes were inserted in a new cell
> > > with every line of text. My current method is to filter by customer number
> > > then concatenate every line of customer notes, then copy and paste special as
> > > values. I then delete the other lines of text and move to the next customer
> > > number. PLEASE tell me there is any easier way! Restore my sanity!

[Helpless1]

Mike,
Thank you very much. What should I be selecting to apply the macro to? I
have been selecting the whole row of those that have like customer numbers.
When I do this it concatenates all the cells in the row instead of the notes
in column K from multiple rows. I'm sorry I'm so needy!

"crazybass2" wrote:

> In the code I sent, change the Offset(0,1) to Offest(0,10) for instance if
> Customer ID is in column A and Notes in column K.
>
> Mike
>
> "Helpless1" wrote:
>
> > Tom,
> > Thanks so much for your help! I should have mentioned before however that I
> > have other information between the customer number and note. The customer
> > number is in column A and the notes are in column K. This other information
> > is the contact information which appears with every row of notes. I need to
> > end up with only one row for each customer with the notes in column K in one
> > cell. I cannot tell you how much I appreciate you looking at this for me!
> >
> > -Helpless1
> >
> > "Tom Ogilvy" wrote:
> >
> > > Assume the customer number is in column A and the text in column B
> > >
> > > select the cells in column A that you want to process, then run this macro
> > >
> > > Sub ProcessData()
> > > Dim rng As Range, cell As Range, s As String
> > > Set rng = Selection(1)
> > > For Each cell In Selection
> > > If cell.Value <> cell.Offset(1, 0).Value Then
> > > s = s & " " & cell.Value
> > > rng.Offset(0, 1).Value = Application.Trim(s)
> > > If rng.Row <> cell.Row Then
> > > Range(rng.Offset(1, 0), cell).Resize(, 2).ClearContents
> > > End If
> > > Set rng = cell.Offset(1, 0)
> > > s = ""
> > > Else
> > > s = s & " " & cell.Value
> > > End If
> > >
> > > Next
> > > Columns(2).SpecialCells(xlBlanks).EntireRow.Delete
> > > End Sub
> > >
> > >
> > > Test this on a copy of your data
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Helpless1" wrote:
> > >
> > > > HELP! I am a very minimal user and have a huge annoying project that I keep
> > > > wondering if there is an easier way to accomplish. I am doing conversion work
> > > > and have about 29000 lines left of customer information. When the info from
> > > > our old system was imported the customer notes were inserted in a new cell
> > > > with every line of text. My current method is to filter by customer number
> > > > then concatenate every line of customer notes, then copy and paste special as
> > > > values. I then delete the other lines of text and move to the next customer
> > > > number. PLEASE tell me there is any easier way! Restore my sanity!

[Helpless1]

This revision gives syntax error message in line 3.

"Tom Ogilvy" wrote:

> Here is a revision. Assumes you have a header row in row 1 and data starts
> in row2. Again, test on a copy of your data.
>
> Sub ProcessData()
> Dim rng As Range, cell As Range, s As String
> Set rng = Range(cells(2,1),Row.count,1).End(xlup))
> For Each cell In rng
> If cell.Value <> cell.Offset(1, 0).Value Then
> s = s & " " & cell.Offset(0,10).Value
> rng.Offset(0, 10).Value = Application.Trim(s)
> If rng.Row <> cell.Row Then
> Range(rng.Offset(1, 0), cell).Entirerow.ClearContents
> End If
> Set rng = cell.Offset(1, 0)
> s = ""
> Else
> s = s & " " & cell.Offset(0,10).Value
> End If
>
> Next
> Columns(10).SpecialCells(xlBlanks).EntireRow.Delete
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
> "Helpless1" wrote:
>
> > Tom,
> > Thanks so much for your help! I should have mentioned before however that I
> > have other information between the customer number and note. The customer
> > number is in column A and the notes are in column K. This other information
> > is the contact information which appears with every row of notes. I need to
> > end up with only one row for each customer with the notes in column K in one
> > cell. I cannot tell you how much I appreciate you looking at this for me!
> >
> > -Helpless1
> >
> > "Tom Ogilvy" wrote:
> >
> > > Assume the customer number is in column A and the text in column B
> > >
> > > select the cells in column A that you want to process, then run this macro
> > >
> > > Sub ProcessData()
> > > Dim rng As Range, cell As Range, s As String
> > > Set rng = Selection(1)
> > > For Each cell In Selection
> > > If cell.Value <> cell.Offset(1, 0).Value Then
> > > s = s & " " & cell.Value
> > > rng.Offset(0, 1).Value = Application.Trim(s)
> > > If rng.Row <> cell.Row Then
> > > Range(rng.Offset(1, 0), cell).Resize(, 2).ClearContents
> > > End If
> > > Set rng = cell.Offset(1, 0)
> > > s = ""
> > > Else
> > > s = s & " " & cell.Value
> > > End If
> > >
> > > Next
> > > Columns(2).SpecialCells(xlBlanks).EntireRow.Delete
> > > End Sub
> > >
> > >
> > > Test this on a copy of your data
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Helpless1" wrote:
> > >
> > > > HELP! I am a very minimal user and have a huge annoying project that I keep
> > > > wondering if there is an easier way to accomplish. I am doing conversion work
> > > > and have about 29000 lines left of customer information. When the info from
> > > > our old system was imported the customer notes were inserted in a new cell
> > > > with every line of text. My current method is to filter by customer number
> > > > then concatenate every line of customer notes, then copy and paste special as
> > > > values. I then delete the other lines of text and move to the next customer
> > > > number. PLEASE tell me there is any easier way! Restore my sanity!

[Bob Phillips]

I think Tom meant

Set rng = Range(cells(2,1),Cells(Rows.count,1).End(xlup))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Helpless1" <Helpless1@discussions.microsoft.com> wrote in message
news:81E74D6E-068F-4E50-8492-9A333B6C5004@microsoft.com...
> This revision gives syntax error message in line 3.
>
> "Tom Ogilvy" wrote:
>
> > Here is a revision. Assumes you have a header row in row 1 and data
starts
> > in row2. Again, test on a copy of your data.
> >
> > Sub ProcessData()
> > Dim rng As Range, cell As Range, s As String
> > Set rng = Range(cells(2,1),Row.count,1).End(xlup))
> > For Each cell In rng
> > If cell.Value <> cell.Offset(1, 0).Value Then
> > s = s & " " & cell.Offset(0,10).Value
> > rng.Offset(0, 10).Value = Application.Trim(s)
> > If rng.Row <> cell.Row Then
> > Range(rng.Offset(1, 0), cell).Entirerow.ClearContents
> > End If
> > Set rng = cell.Offset(1, 0)
> > s = ""
> > Else
> > s = s & " " & cell.Offset(0,10).Value
> > End If
> >
> > Next
> > Columns(10).SpecialCells(xlBlanks).EntireRow.Delete
> > End Sub
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Helpless1" wrote:
> >
> > > Tom,
> > > Thanks so much for your help! I should have mentioned before however
that I
> > > have other information between the customer number and note. The
customer
> > > number is in column A and the notes are in column K. This other
information
> > > is the contact information which appears with every row of notes. I
need to
> > > end up with only one row for each customer with the notes in column K
in one
> > > cell. I cannot tell you how much I appreciate you looking at this for
me!
> > >
> > > -Helpless1
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > Assume the customer number is in column A and the text in column B
> > > >
> > > > select the cells in column A that you want to process, then run this
macro
> > > >
> > > > Sub ProcessData()
> > > > Dim rng As Range, cell As Range, s As String
> > > > Set rng = Selection(1)
> > > > For Each cell In Selection
> > > > If cell.Value <> cell.Offset(1, 0).Value Then
> > > > s = s & " " & cell.Value
> > > > rng.Offset(0, 1).Value = Application.Trim(s)
> > > > If rng.Row <> cell.Row Then
> > > > Range(rng.Offset(1, 0), cell).Resize(, 2).ClearContents
> > > > End If
> > > > Set rng = cell.Offset(1, 0)
> > > > s = ""
> > > > Else
> > > > s = s & " " & cell.Value
> > > > End If
> > > >
> > > > Next
> > > > Columns(2).SpecialCells(xlBlanks).EntireRow.Delete
> > > > End Sub
> > > >
> > > >
> > > > Test this on a copy of your data
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > > "Helpless1" wrote:
> > > >
> > > > > HELP! I am a very minimal user and have a huge annoying project
that I keep
> > > > > wondering if there is an easier way to accomplish. I am doing
conversion work
> > > > > and have about 29000 lines left of customer information. When the
info from
> > > > > our old system was imported the customer notes were inserted in a
new cell
> > > > > with every line of text. My current method is to filter by
customer number
> > > > > then concatenate every line of customer notes, then copy and paste
special as
> > > > > values. I then delete the other lines of text and move to the next
customer
> > > > > number. PLEASE tell me there is any easier way! Restore my sanity!

[Helpless1]

Well folks,
That seemed as though it started to work (concatenated the notes as I
needed) then the screen started kind of blinking and then the program stopped
responding.
I feel bad taking up all of your time so stop helping whenever you need to.
Thank you so much.

"Bob Phillips" wrote:

> I think Tom meant
>
> Set rng = Range(cells(2,1),Cells(Rows.count,1).End(xlup))
>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Helpless1" <Helpless1@discussions.microsoft.com> wrote in message
> news:81E74D6E-068F-4E50-8492-9A333B6C5004@microsoft.com...
> > This revision gives syntax error message in line 3.
> >
> > "Tom Ogilvy" wrote:
> >
> > > Here is a revision. Assumes you have a header row in row 1 and data
> starts
> > > in row2. Again, test on a copy of your data.
> > >
> > > Sub ProcessData()
> > > Dim rng As Range, cell As Range, s As String
> > > Set rng = Range(cells(2,1),Row.count,1).End(xlup))
> > > For Each cell In rng
> > > If cell.Value <> cell.Offset(1, 0).Value Then
> > > s = s & " " & cell.Offset(0,10).Value
> > > rng.Offset(0, 10).Value = Application.Trim(s)
> > > If rng.Row <> cell.Row Then
> > > Range(rng.Offset(1, 0), cell).Entirerow.ClearContents
> > > End If
> > > Set rng = cell.Offset(1, 0)
> > > s = ""
> > > Else
> > > s = s & " " & cell.Offset(0,10).Value
> > > End If
> > >
> > > Next
> > > Columns(10).SpecialCells(xlBlanks).EntireRow.Delete
> > > End Sub
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Helpless1" wrote:
> > >
> > > > Tom,
> > > > Thanks so much for your help! I should have mentioned before however
> that I
> > > > have other information between the customer number and note. The
> customer
> > > > number is in column A and the notes are in column K. This other
> information
> > > > is the contact information which appears with every row of notes. I
> need to
> > > > end up with only one row for each customer with the notes in column K
> in one
> > > > cell. I cannot tell you how much I appreciate you looking at this for
> me!
> > > >
> > > > -Helpless1
> > > >
> > > > "Tom Ogilvy" wrote:
> > > >
> > > > > Assume the customer number is in column A and the text in column B
> > > > >
> > > > > select the cells in column A that you want to process, then run this
> macro
> > > > >
> > > > > Sub ProcessData()
> > > > > Dim rng As Range, cell As Range, s As String
> > > > > Set rng = Selection(1)
> > > > > For Each cell In Selection
> > > > > If cell.Value <> cell.Offset(1, 0).Value Then
> > > > > s = s & " " & cell.Value
> > > > > rng.Offset(0, 1).Value = Application.Trim(s)
> > > > > If rng.Row <> cell.Row Then
> > > > > Range(rng.Offset(1, 0), cell).Resize(, 2).ClearContents
> > > > > End If
> > > > > Set rng = cell.Offset(1, 0)
> > > > > s = ""
> > > > > Else
> > > > > s = s & " " & cell.Value
> > > > > End If
> > > > >
> > > > > Next
> > > > > Columns(2).SpecialCells(xlBlanks).EntireRow.Delete
> > > > > End Sub
> > > > >
> > > > >
> > > > > Test this on a copy of your data
> > > > >
> > > > > --
> > > > > Regards,
> > > > > Tom Ogilvy
> > > > >
> > > > > "Helpless1" wrote:
> > > > >
> > > > > > HELP! I am a very minimal user and have a huge annoying project
> that I keep
> > > > > > wondering if there is an easier way to accomplish. I am doing
> conversion work
> > > > > > and have about 29000 lines left of customer information. When the
> info from
> > > > > > our old system was imported the customer notes were inserted in a
> new cell
> > > > > > with every line of text. My current method is to filter by
> customer number
> > > > > > then concatenate every line of customer notes, then copy and paste
> special as
> > > > > > values. I then delete the other lines of text and move to the next
> customer
> > > > > > number. PLEASE tell me there is any easier way! Restore my sanity!
>
>
>

[Bob Phillips]

I feel like I am in a bit of a vacuum. Can you post the data that you have
that does this?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Helpless1" <Helpless1@discussions.microsoft.com> wrote in message
news:7E4FC8DE-5A7F-4C0E-BA46-1F48841DD88A@microsoft.com...
> Well folks,
> That seemed as though it started to work (concatenated the notes as I
> needed) then the screen started kind of blinking and then the program
stopped
> responding.
> I feel bad taking up all of your time so stop helping whenever you need
to.
> Thank you so much.
>
> "Bob Phillips" wrote:
>
> > I think Tom meant
> >
> > Set rng = Range(cells(2,1),Cells(Rows.count,1).End(xlup))
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Helpless1" <Helpless1@discussions.microsoft.com> wrote in message
> > news:81E74D6E-068F-4E50-8492-9A333B6C5004@microsoft.com...
> > > This revision gives syntax error message in line 3.
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > Here is a revision. Assumes you have a header row in row 1 and data
> > starts
> > > > in row2. Again, test on a copy of your data.
> > > >
> > > > Sub ProcessData()
> > > > Dim rng As Range, cell As Range, s As String
> > > > Set rng = Range(cells(2,1),Row.count,1).End(xlup))
> > > > For Each cell In rng
> > > > If cell.Value <> cell.Offset(1, 0).Value Then
> > > > s = s & " " & cell.Offset(0,10).Value
> > > > rng.Offset(0, 10).Value = Application.Trim(s)
> > > > If rng.Row <> cell.Row Then
> > > > Range(rng.Offset(1, 0), cell).Entirerow.ClearContents
> > > > End If
> > > > Set rng = cell.Offset(1, 0)
> > > > s = ""
> > > > Else
> > > > s = s & " " & cell.Offset(0,10).Value
> > > > End If
> > > >
> > > > Next
> > > > Columns(10).SpecialCells(xlBlanks).EntireRow.Delete
> > > > End Sub
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > > "Helpless1" wrote:
> > > >
> > > > > Tom,
> > > > > Thanks so much for your help! I should have mentioned before
however
> > that I
> > > > > have other information between the customer number and note. The
> > customer
> > > > > number is in column A and the notes are in column K. This other
> > information
> > > > > is the contact information which appears with every row of notes.
I
> > need to
> > > > > end up with only one row for each customer with the notes in
column K
> > in one
> > > > > cell. I cannot tell you how much I appreciate you looking at this
for
> > me!
> > > > >
> > > > > -Helpless1
> > > > >
> > > > > "Tom Ogilvy" wrote:
> > > > >
> > > > > > Assume the customer number is in column A and the text in column
B
> > > > > >
> > > > > > select the cells in column A that you want to process, then run
this
> > macro
> > > > > >
> > > > > > Sub ProcessData()
> > > > > > Dim rng As Range, cell As Range, s As String
> > > > > > Set rng = Selection(1)
> > > > > > For Each cell In Selection
> > > > > > If cell.Value <> cell.Offset(1, 0).Value Then
> > > > > > s = s & " " & cell.Value
> > > > > > rng.Offset(0, 1).Value = Application.Trim(s)
> > > > > > If rng.Row <> cell.Row Then
> > > > > > Range(rng.Offset(1, 0), cell).Resize(, 2).ClearContents
> > > > > > End If
> > > > > > Set rng = cell.Offset(1, 0)
> > > > > > s = ""
> > > > > > Else
> > > > > > s = s & " " & cell.Value
> > > > > > End If
> > > > > >
> > > > > > Next
> > > > > > Columns(2).SpecialCells(xlBlanks).EntireRow.Delete
> > > > > > End Sub
> > > > > >
> > > > > >
> > > > > > Test this on a copy of your data
> > > > > >
> > > > > > --
> > > > > > Regards,
> > > > > > Tom Ogilvy
> > > > > >
> > > > > > "Helpless1" wrote:
> > > > > >
> > > > > > > HELP! I am a very minimal user and have a huge annoying
project
> > that I keep
> > > > > > > wondering if there is an easier way to accomplish. I am doing
> > conversion work
> > > > > > > and have about 29000 lines left of customer information. When
the
> > info from
> > > > > > > our old system was imported the customer notes were inserted
in a
> > new cell
> > > > > > > with every line of text. My current method is to filter by
> > customer number
> > > > > > > then concatenate every line of customer notes, then copy and
paste
> > special as
> > > > > > > values. I then delete the other lines of text and move to the
next
> > customer
> > > > > > > number. PLEASE tell me there is any easier way! Restore my
sanity!
> >
> >
> >

[Helpless1]

I'm so sorry Bob. Thank you. I felt a little uneasy about publishing all of
our customer's information to the web so I have recreated the exact way my
data is set up. When the notes came from the old system Excel inserted the
contact info in every row next to the notes. I have included the cells the
way that I need them just for clearification. This isn't showing up quite
right but it through columns A-K.

What I have:

Cust # Name Add1 Add2 Add3 City State Zip Phone Fax Notes

6546 AAA Add1 Add2 Add3 City State Zip Phone Fax Notes line 1
6456 AAA Add1 Add2 Add3 City State Zip Phone Fax Notes line 2
6456 AAA Add1 Add2 Add3 City State Zip Phone Fax Notes line 3
6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes line 1
6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes line 2
6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes lines 3
6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes lines 4
6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes lines 5
6458 Arrow Add1 Add2 Add3 City State Zip Phone Fax Notes line 1
6458 Arrow Add1 Add2 Add3 City State Zip Phone Fax Notes line 2
6458 Arrow Add1 Add2 Add3 City State Zip Phone Fax Notes lines 3

What I need:

6546 AAA Add1 Add2 Add3 City State Zip Phone Fax Notes line 1Notes line
2Notes line 3
6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes line 1Notes line
2Notes lines 3Notes lines 4Notes lines 5
6458 Arrow Add1 Add2 Add3 City State Zip Phone Fax Notes line 1Notes line
2Notes lines 3

I can email you this small file if that would help.

"Bob Phillips" wrote:

> I feel like I am in a bit of a vacuum. Can you post the data that you have
> that does this?
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Helpless1" <Helpless1@discussions.microsoft.com> wrote in message
> news:7E4FC8DE-5A7F-4C0E-BA46-1F48841DD88A@microsoft.com...
> > Well folks,
> > That seemed as though it started to work (concatenated the notes as I
> > needed) then the screen started kind of blinking and then the program
> stopped
> > responding.
> > I feel bad taking up all of your time so stop helping whenever you need
> to.
> > Thank you so much.
> >
> > "Bob Phillips" wrote:
> >
> > > I think Tom meant
> > >
> > > Set rng = Range(cells(2,1),Cells(Rows.count,1).End(xlup))
> > >
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace somewhere in email address with gmail if mailing direct)
> > >
> > > "Helpless1" <Helpless1@discussions.microsoft.com> wrote in message
> > > news:81E74D6E-068F-4E50-8492-9A333B6C5004@microsoft.com...
> > > > This revision gives syntax error message in line 3.
> > > >
> > > > "Tom Ogilvy" wrote:
> > > >
> > > > > Here is a revision. Assumes you have a header row in row 1 and data
> > > starts
> > > > > in row2. Again, test on a copy of your data.
> > > > >
> > > > > Sub ProcessData()
> > > > > Dim rng As Range, cell As Range, s As String
> > > > > Set rng = Range(cells(2,1),Row.count,1).End(xlup))
> > > > > For Each cell In rng
> > > > > If cell.Value <> cell.Offset(1, 0).Value Then
> > > > > s = s & " " & cell.Offset(0,10).Value
> > > > > rng.Offset(0, 10).Value = Application.Trim(s)
> > > > > If rng.Row <> cell.Row Then
> > > > > Range(rng.Offset(1, 0), cell).Entirerow.ClearContents
> > > > > End If
> > > > > Set rng = cell.Offset(1, 0)
> > > > > s = ""
> > > > > Else
> > > > > s = s & " " & cell.Offset(0,10).Value
> > > > > End If
> > > > >
> > > > > Next
> > > > > Columns(10).SpecialCells(xlBlanks).EntireRow.Delete
> > > > > End Sub
> > > > >
> > > > > --
> > > > > Regards,
> > > > > Tom Ogilvy
> > > > >
> > > > > "Helpless1" wrote:
> > > > >
> > > > > > Tom,
> > > > > > Thanks so much for your help! I should have mentioned before
> however
> > > that I
> > > > > > have other information between the customer number and note. The
> > > customer
> > > > > > number is in column A and the notes are in column K. This other
> > > information
> > > > > > is the contact information which appears with every row of notes.
> I
> > > need to
> > > > > > end up with only one row for each customer with the notes in
> column K
> > > in one
> > > > > > cell. I cannot tell you how much I appreciate you looking at this
> for
> > > me!
> > > > > >
> > > > > > -Helpless1
> > > > > >
> > > > > > "Tom Ogilvy" wrote:
> > > > > >
> > > > > > > Assume the customer number is in column A and the text in column
> B
> > > > > > >
> > > > > > > select the cells in column A that you want to process, then run
> this
> > > macro
> > > > > > >
> > > > > > > Sub ProcessData()
> > > > > > > Dim rng As Range, cell As Range, s As String
> > > > > > > Set rng = Selection(1)
> > > > > > > For Each cell In Selection
> > > > > > > If cell.Value <> cell.Offset(1, 0).Value Then
> > > > > > > s = s & " " & cell.Value
> > > > > > > rng.Offset(0, 1).Value = Application.Trim(s)
> > > > > > > If rng.Row <> cell.Row Then
> > > > > > > Range(rng.Offset(1, 0), cell).Resize(, 2).ClearContents
> > > > > > > End If
> > > > > > > Set rng = cell.Offset(1, 0)
> > > > > > > s = ""
> > > > > > > Else
> > > > > > > s = s & " " & cell.Value
> > > > > > > End If
> > > > > > >
> > > > > > > Next
> > > > > > > Columns(2).SpecialCells(xlBlanks).EntireRow.Delete
> > > > > > > End Sub
> > > > > > >
> > > > > > >
> > > > > > > Test this on a copy of your data
> > > > > > >
> > > > > > > --
> > > > > > > Regards,
> > > > > > > Tom Ogilvy
> > > > > > >
> > > > > > > "Helpless1" wrote:
> > > > > > >
> > > > > > > > HELP! I am a very minimal user and have a huge annoying
> project
> > > that I keep
> > > > > > > > wondering if there is an easier way to accomplish. I am doing
> > > conversion work
> > > > > > > > and have about 29000 lines left of customer information. When
> the
> > > info from
> > > > > > > > our old system was imported the customer notes were inserted
> in a
> > > new cell
> > > > > > > > with every line of text. My current method is to filter by
> > > customer number
> > > > > > > > then concatenate every line of customer notes, then copy and
> paste
> > > special as
> > > > > > > > values. I then delete the other lines of text and move to the
> next
> > > customer
> > > > > > > > number. PLEASE tell me there is any easier way! Restore my
> sanity!
> > >
> > >
> > >
>
>
>

[Bob Phillips]

That's perfectly understandable mate, the data was fine (apart from a couple
of typos in the ids, which I spotted).

I think (hope!) this does it for you

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim rng As Range

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
Cells(i, "K").Resize(1, 20).Copy Cells(i - 1, "L")
Cells(i, "A").Value = ""
End If
Next i


Range("A1").Resize(iLastRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Helpless1" <Helpless1@discussions.microsoft.com> wrote in message
news:C9178FD3-2AAF-4474-851F-A4B635AFAB63@microsoft.com...
> I'm so sorry Bob. Thank you. I felt a little uneasy about publishing all
of
> our customer's information to the web so I have recreated the exact way my
> data is set up. When the notes came from the old system Excel inserted the
> contact info in every row next to the notes. I have included the cells the
> way that I need them just for clearification. This isn't showing up quite
> right but it through columns A-K.
>
> What I have:
>
> Cust # Name Add1 Add2 Add3 City State Zip Phone Fax Notes
>
> 6546 AAA Add1 Add2 Add3 City State Zip Phone Fax Notes line 1
> 6456 AAA Add1 Add2 Add3 City State Zip Phone Fax Notes line 2
> 6456 AAA Add1 Add2 Add3 City State Zip Phone Fax Notes line 3
> 6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes line 1
> 6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes line 2
> 6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes lines 3
> 6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes lines 4
> 6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes lines 5
> 6458 Arrow Add1 Add2 Add3 City State Zip Phone Fax Notes line 1
> 6458 Arrow Add1 Add2 Add3 City State Zip Phone Fax Notes line 2
> 6458 Arrow Add1 Add2 Add3 City State Zip Phone Fax Notes lines 3
>
> What I need:
>
> 6546 AAA Add1 Add2 Add3 City State Zip Phone Fax Notes line 1Notes line
> 2Notes line 3
> 6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes line 1Notes line
> 2Notes lines 3Notes lines 4Notes lines 5
> 6458 Arrow Add1 Add2 Add3 City State Zip Phone Fax Notes line 1Notes line
> 2Notes lines 3
>
> I can email you this small file if that would help.
>
> "Bob Phillips" wrote:
>
> > I feel like I am in a bit of a vacuum. Can you post the data that you
have
> > that does this?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Helpless1" <Helpless1@discussions.microsoft.com> wrote in message
> > news:7E4FC8DE-5A7F-4C0E-BA46-1F48841DD88A@microsoft.com...
> > > Well folks,
> > > That seemed as though it started to work (concatenated the notes as I
> > > needed) then the screen started kind of blinking and then the program
> > stopped
> > > responding.
> > > I feel bad taking up all of your time so stop helping whenever you
need
> > to.
> > > Thank you so much.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > I think Tom meant
> > > >
> > > > Set rng = Range(cells(2,1),Cells(Rows.count,1).End(xlup))
> > > >
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (replace somewhere in email address with gmail if mailing direct)
> > > >
> > > > "Helpless1" <Helpless1@discussions.microsoft.com> wrote in message
> > > > news:81E74D6E-068F-4E50-8492-9A333B6C5004@microsoft.com...
> > > > > This revision gives syntax error message in line 3.
> > > > >
> > > > > "Tom Ogilvy" wrote:
> > > > >
> > > > > > Here is a revision. Assumes you have a header row in row 1 and
data
> > > > starts
> > > > > > in row2. Again, test on a copy of your data.
> > > > > >
> > > > > > Sub ProcessData()
> > > > > > Dim rng As Range, cell As Range, s As String
> > > > > > Set rng = Range(cells(2,1),Row.count,1).End(xlup))
> > > > > > For Each cell In rng
> > > > > > If cell.Value <> cell.Offset(1, 0).Value Then
> > > > > > s = s & " " & cell.Offset(0,10).Value
> > > > > > rng.Offset(0, 10).Value = Application.Trim(s)
> > > > > > If rng.Row <> cell.Row Then
> > > > > > Range(rng.Offset(1, 0), cell).Entirerow.ClearContents
> > > > > > End If
> > > > > > Set rng = cell.Offset(1, 0)
> > > > > > s = ""
> > > > > > Else
> > > > > > s = s & " " & cell.Offset(0,10).Value
> > > > > > End If
> > > > > >
> > > > > > Next
> > > > > > Columns(10).SpecialCells(xlBlanks).EntireRow.Delete
> > > > > > End Sub
> > > > > >
> > > > > > --
> > > > > > Regards,
> > > > > > Tom Ogilvy
> > > > > >
> > > > > > "Helpless1" wrote:
> > > > > >
> > > > > > > Tom,
> > > > > > > Thanks so much for your help! I should have mentioned before
> > however
> > > > that I
> > > > > > > have other information between the customer number and note.
The
> > > > customer
> > > > > > > number is in column A and the notes are in column K. This
other
> > > > information
> > > > > > > is the contact information which appears with every row of
notes.
> > I
> > > > need to
> > > > > > > end up with only one row for each customer with the notes in
> > column K
> > > > in one
> > > > > > > cell. I cannot tell you how much I appreciate you looking at
this
> > for
> > > > me!
> > > > > > >
> > > > > > > -Helpless1
> > > > > > >
> > > > > > > "Tom Ogilvy" wrote:
> > > > > > >
> > > > > > > > Assume the customer number is in column A and the text in
column
> > B
> > > > > > > >
> > > > > > > > select the cells in column A that you want to process, then
run
> > this
> > > > macro
> > > > > > > >
> > > > > > > > Sub ProcessData()
> > > > > > > > Dim rng As Range, cell As Range, s As String
> > > > > > > > Set rng = Selection(1)
> > > > > > > > For Each cell In Selection
> > > > > > > > If cell.Value <> cell.Offset(1, 0).Value Then
> > > > > > > > s = s & " " & cell.Value
> > > > > > > > rng.Offset(0, 1).Value = Application.Trim(s)
> > > > > > > > If rng.Row <> cell.Row Then
> > > > > > > > Range(rng.Offset(1, 0), cell).Resize(, 2).ClearContents
> > > > > > > > End If
> > > > > > > > Set rng = cell.Offset(1, 0)
> > > > > > > > s = ""
> > > > > > > > Else
> > > > > > > > s = s & " " & cell.Value
> > > > > > > > End If
> > > > > > > >
> > > > > > > > Next
> > > > > > > > Columns(2).SpecialCells(xlBlanks).EntireRow.Delete
> > > > > > > > End Sub
> > > > > > > >
> > > > > > > >
> > > > > > > > Test this on a copy of your data
> > > > > > > >
> > > > > > > > --
> > > > > > > > Regards,
> > > > > > > > Tom Ogilvy
> > > > > > > >
> > > > > > > > "Helpless1" wrote:
> > > > > > > >
> > > > > > > > > HELP! I am a very minimal user and have a huge annoying
> > project
> > > > that I keep
> > > > > > > > > wondering if there is an easier way to accomplish. I am
doing
> > > > conversion work
> > > > > > > > > and have about 29000 lines left of customer information.
When
> > the
> > > > info from
> > > > > > > > > our old system was imported the customer notes were
inserted
> > in a
> > > > new cell
> > > > > > > > > with every line of text. My current method is to filter by
> > > > customer number
> > > > > > > > > then concatenate every line of customer notes, then copy
and
> > paste
> > > > special as
> > > > > > > > > values. I then delete the other lines of text and move to
the
> > next
> > > > customer
> > > > > > > > > number. PLEASE tell me there is any easier way! Restore my
> > sanity!
> > > >
> > > >
> > > >
> >
> >
> >

[Tom Ogilvy]

Note that if you have a lot of data/rows to delete, this line

Range("A1").Resize(iLastRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

may take a long time to execute. Same as with mine. Hope you don't think
your system is "not responding" again.

--
Regards,
Tom Ogilvy

"Bob Phillips" <bob.NGs@somewhere.com> wrote in message
news:%23G25wggpGHA.2360@TK2MSFTNGP05.phx.gbl...
> That's perfectly understandable mate, the data was fine (apart from a
> couple
> of typos in the ids, which I spotted).
>
> I think (hope!) this does it for you
>
> Sub Test()
> Dim iLastRow As Long
> Dim i As Long
> Dim rng As Range
>
> iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
> For i = iLastRow To 2 Step -1
> If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
> Cells(i, "K").Resize(1, 20).Copy Cells(i - 1, "L")
> Cells(i, "A").Value = ""
> End If
> Next i
>
>
> Range("A1").Resize(iLastRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
> End Sub
>
>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Helpless1" <Helpless1@discussions.microsoft.com> wrote in message
> news:C9178FD3-2AAF-4474-851F-A4B635AFAB63@microsoft.com...
>> I'm so sorry Bob. Thank you. I felt a little uneasy about publishing all
> of
>> our customer's information to the web so I have recreated the exact way
>> my
>> data is set up. When the notes came from the old system Excel inserted
>> the
>> contact info in every row next to the notes. I have included the cells
>> the
>> way that I need them just for clearification. This isn't showing up quite
>> right but it through columns A-K.
>>
>> What I have:
>>
>> Cust # Name Add1 Add2 Add3 City State Zip Phone Fax Notes
>>
>> 6546 AAA Add1 Add2 Add3 City State Zip Phone Fax Notes line 1
>> 6456 AAA Add1 Add2 Add3 City State Zip Phone Fax Notes line 2
>> 6456 AAA Add1 Add2 Add3 City State Zip Phone Fax Notes line 3
>> 6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes line 1
>> 6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes line 2
>> 6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes lines 3
>> 6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes lines 4
>> 6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes lines 5
>> 6458 Arrow Add1 Add2 Add3 City State Zip Phone Fax Notes line 1
>> 6458 Arrow Add1 Add2 Add3 City State Zip Phone Fax Notes line 2
>> 6458 Arrow Add1 Add2 Add3 City State Zip Phone Fax Notes lines 3
>>
>> What I need:
>>
>> 6546 AAA Add1 Add2 Add3 City State Zip Phone Fax Notes line 1Notes line
>> 2Notes line 3
>> 6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes line 1Notes line
>> 2Notes lines 3Notes lines 4Notes lines 5
>> 6458 Arrow Add1 Add2 Add3 City State Zip Phone Fax Notes line 1Notes line
>> 2Notes lines 3
>>
>> I can email you this small file if that would help.
>>
>> "Bob Phillips" wrote:
>>
>> > I feel like I am in a bit of a vacuum. Can you post the data that you
> have
>> > that does this?
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > (replace somewhere in email address with gmail if mailing direct)
>> >
>> > "Helpless1" <Helpless1@discussions.microsoft.com> wrote in message
>> > news:7E4FC8DE-5A7F-4C0E-BA46-1F48841DD88A@microsoft.com...
>> > > Well folks,
>> > > That seemed as though it started to work (concatenated the notes as I
>> > > needed) then the screen started kind of blinking and then the program
>> > stopped
>> > > responding.
>> > > I feel bad taking up all of your time so stop helping whenever you
> need
>> > to.
>> > > Thank you so much.
>> > >
>> > > "Bob Phillips" wrote:
>> > >
>> > > > I think Tom meant
>> > > >
>> > > > Set rng = Range(cells(2,1),Cells(Rows.count,1).End(xlup))
>> > > >
>> > > >
>> > > > --
>> > > > HTH
>> > > >
>> > > > Bob Phillips
>> > > >
>> > > > (replace somewhere in email address with gmail if mailing direct)
>> > > >
>> > > > "Helpless1" <Helpless1@discussions.microsoft.com> wrote in message
>> > > > news:81E74D6E-068F-4E50-8492-9A333B6C5004@microsoft.com...
>> > > > > This revision gives syntax error message in line 3.
>> > > > >
>> > > > > "Tom Ogilvy" wrote:
>> > > > >
>> > > > > > Here is a revision. Assumes you have a header row in row 1 and
> data
>> > > > starts
>> > > > > > in row2. Again, test on a copy of your data.
>> > > > > >
>> > > > > > Sub ProcessData()
>> > > > > > Dim rng As Range, cell As Range, s As String
>> > > > > > Set rng = Range(cells(2,1),Row.count,1).End(xlup))
>> > > > > > For Each cell In rng
>> > > > > > If cell.Value <> cell.Offset(1, 0).Value Then
>> > > > > > s = s & " " & cell.Offset(0,10).Value
>> > > > > > rng.Offset(0, 10).Value = Application.Trim(s)
>> > > > > > If rng.Row <> cell.Row Then
>> > > > > > Range(rng.Offset(1, 0), cell).Entirerow.ClearContents
>> > > > > > End If
>> > > > > > Set rng = cell.Offset(1, 0)
>> > > > > > s = ""
>> > > > > > Else
>> > > > > > s = s & " " & cell.Offset(0,10).Value
>> > > > > > End If
>> > > > > >
>> > > > > > Next
>> > > > > > Columns(10).SpecialCells(xlBlanks).EntireRow.Delete
>> > > > > > End Sub
>> > > > > >
>> > > > > > --
>> > > > > > Regards,
>> > > > > > Tom Ogilvy
>> > > > > >
>> > > > > > "Helpless1" wrote:
>> > > > > >
>> > > > > > > Tom,
>> > > > > > > Thanks so much for your help! I should have mentioned before
>> > however
>> > > > that I
>> > > > > > > have other information between the customer number and note.
> The
>> > > > customer
>> > > > > > > number is in column A and the notes are in column K. This
> other
>> > > > information
>> > > > > > > is the contact information which appears with every row of
> notes.
>> > I
>> > > > need to
>> > > > > > > end up with only one row for each customer with the notes in
>> > column K
>> > > > in one
>> > > > > > > cell. I cannot tell you how much I appreciate you looking at
> this
>> > for
>> > > > me!
>> > > > > > >
>> > > > > > > -Helpless1
>> > > > > > >
>> > > > > > > "Tom Ogilvy" wrote:
>> > > > > > >
>> > > > > > > > Assume the customer number is in column A and the text in
> column
>> > B
>> > > > > > > >
>> > > > > > > > select the cells in column A that you want to process, then
> run
>> > this
>> > > > macro
>> > > > > > > >
>> > > > > > > > Sub ProcessData()
>> > > > > > > > Dim rng As Range, cell As Range, s As String
>> > > > > > > > Set rng = Selection(1)
>> > > > > > > > For Each cell In Selection
>> > > > > > > > If cell.Value <> cell.Offset(1, 0).Value Then
>> > > > > > > > s = s & " " & cell.Value
>> > > > > > > > rng.Offset(0, 1).Value = Application.Trim(s)
>> > > > > > > > If rng.Row <> cell.Row Then
>> > > > > > > > Range(rng.Offset(1, 0), cell).Resize(, 2).ClearContents
>> > > > > > > > End If
>> > > > > > > > Set rng = cell.Offset(1, 0)
>> > > > > > > > s = ""
>> > > > > > > > Else
>> > > > > > > > s = s & " " & cell.Value
>> > > > > > > > End If
>> > > > > > > >
>> > > > > > > > Next
>> > > > > > > > Columns(2).SpecialCells(xlBlanks).EntireRow.Delete
>> > > > > > > > End Sub
>> > > > > > > >
>> > > > > > > >
>> > > > > > > > Test this on a copy of your data
>> > > > > > > >
>> > > > > > > > --
>> > > > > > > > Regards,
>> > > > > > > > Tom Ogilvy
>> > > > > > > >
>> > > > > > > > "Helpless1" wrote:
>> > > > > > > >
>> > > > > > > > > HELP! I am a very minimal user and have a huge annoying
>> > project
>> > > > that I keep
>> > > > > > > > > wondering if there is an easier way to accomplish. I am
> doing
>> > > > conversion work
>> > > > > > > > > and have about 29000 lines left of customer information.
> When
>> > the
>> > > > info from
>> > > > > > > > > our old system was imported the customer notes were
> inserted
>> > in a
>> > > > new cell
>> > > > > > > > > with every line of text. My current method is to filter
>> > > > > > > > > by
>> > > > customer number
>> > > > > > > > > then concatenate every line of customer notes, then copy
> and
>> > paste
>> > > > special as
>> > > > > > > > > values. I then delete the other lines of text and move to
> the
>> > next
>> > > > customer
>> > > > > > > > > number. PLEASE tell me there is any easier way! Restore
>> > > > > > > > > my
>> > sanity!
>> > > >
>> > > >
>> > > >
>> >
>> >
>> >
>
>

[Helpless1]

I give up. I know you all think I'm a complete idiot because I'm sure what
you are tell me is right, but it still isn't working for me. I'm sure its
something I'm doing wrong. Thank you all again.

"Bob Phillips" wrote:

> That's perfectly understandable mate, the data was fine (apart from a couple
> of typos in the ids, which I spotted).
>
> I think (hope!) this does it for you
>
> Sub Test()
> Dim iLastRow As Long
> Dim i As Long
> Dim rng As Range
>
> iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
> For i = iLastRow To 2 Step -1
> If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
> Cells(i, "K").Resize(1, 20).Copy Cells(i - 1, "L")
> Cells(i, "A").Value = ""
> End If
> Next i
>
>
> Range("A1").Resize(iLastRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
> End Sub
>
>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Helpless1" <Helpless1@discussions.microsoft.com> wrote in message
> news:C9178FD3-2AAF-4474-851F-A4B635AFAB63@microsoft.com...
> > I'm so sorry Bob. Thank you. I felt a little uneasy about publishing all
> of
> > our customer's information to the web so I have recreated the exact way my
> > data is set up. When the notes came from the old system Excel inserted the
> > contact info in every row next to the notes. I have included the cells the
> > way that I need them just for clearification. This isn't showing up quite
> > right but it through columns A-K.
> >
> > What I have:
> >
> > Cust # Name Add1 Add2 Add3 City State Zip Phone Fax Notes
> >
> > 6546 AAA Add1 Add2 Add3 City State Zip Phone Fax Notes line 1
> > 6456 AAA Add1 Add2 Add3 City State Zip Phone Fax Notes line 2
> > 6456 AAA Add1 Add2 Add3 City State Zip Phone Fax Notes line 3
> > 6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes line 1
> > 6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes line 2
> > 6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes lines 3
> > 6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes lines 4
> > 6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes lines 5
> > 6458 Arrow Add1 Add2 Add3 City State Zip Phone Fax Notes line 1
> > 6458 Arrow Add1 Add2 Add3 City State Zip Phone Fax Notes line 2
> > 6458 Arrow Add1 Add2 Add3 City State Zip Phone Fax Notes lines 3
> >
> > What I need:
> >
> > 6546 AAA Add1 Add2 Add3 City State Zip Phone Fax Notes line 1Notes line
> > 2Notes line 3
> > 6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes line 1Notes line
> > 2Notes lines 3Notes lines 4Notes lines 5
> > 6458 Arrow Add1 Add2 Add3 City State Zip Phone Fax Notes line 1Notes line
> > 2Notes lines 3
> >
> > I can email you this small file if that would help.
> >
> > "Bob Phillips" wrote:
> >
> > > I feel like I am in a bit of a vacuum. Can you post the data that you
> have
> > > that does this?
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace somewhere in email address with gmail if mailing direct)
> > >
> > > "Helpless1" <Helpless1@discussions.microsoft.com> wrote in message
> > > news:7E4FC8DE-5A7F-4C0E-BA46-1F48841DD88A@microsoft.com...
> > > > Well folks,
> > > > That seemed as though it started to work (concatenated the notes as I
> > > > needed) then the screen started kind of blinking and then the program
> > > stopped
> > > > responding.
> > > > I feel bad taking up all of your time so stop helping whenever you
> need
> > > to.
> > > > Thank you so much.
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > I think Tom meant
> > > > >
> > > > > Set rng = Range(cells(2,1),Cells(Rows.count,1).End(xlup))
> > > > >
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > (replace somewhere in email address with gmail if mailing direct)
> > > > >
> > > > > "Helpless1" <Helpless1@discussions.microsoft.com> wrote in message
> > > > > news:81E74D6E-068F-4E50-8492-9A333B6C5004@microsoft.com...
> > > > > > This revision gives syntax error message in line 3.
> > > > > >
> > > > > > "Tom Ogilvy" wrote:
> > > > > >
> > > > > > > Here is a revision. Assumes you have a header row in row 1 and
> data
> > > > > starts
> > > > > > > in row2. Again, test on a copy of your data.
> > > > > > >
> > > > > > > Sub ProcessData()
> > > > > > > Dim rng As Range, cell As Range, s As String
> > > > > > > Set rng = Range(cells(2,1),Row.count,1).End(xlup))
> > > > > > > For Each cell In rng
> > > > > > > If cell.Value <> cell.Offset(1, 0).Value Then
> > > > > > > s = s & " " & cell.Offset(0,10).Value
> > > > > > > rng.Offset(0, 10).Value = Application.Trim(s)
> > > > > > > If rng.Row <> cell.Row Then
> > > > > > > Range(rng.Offset(1, 0), cell).Entirerow.ClearContents
> > > > > > > End If
> > > > > > > Set rng = cell.Offset(1, 0)
> > > > > > > s = ""
> > > > > > > Else
> > > > > > > s = s & " " & cell.Offset(0,10).Value
> > > > > > > End If
> > > > > > >
> > > > > > > Next
> > > > > > > Columns(10).SpecialCells(xlBlanks).EntireRow.Delete
> > > > > > > End Sub
> > > > > > >
> > > > > > > --
> > > > > > > Regards,
> > > > > > > Tom Ogilvy
> > > > > > >
> > > > > > > "Helpless1" wrote:
> > > > > > >
> > > > > > > > Tom,
> > > > > > > > Thanks so much for your help! I should have mentioned before
> > > however
> > > > > that I
> > > > > > > > have other information between the customer number and note.
> The
> > > > > customer
> > > > > > > > number is in column A and the notes are in column K. This
> other
> > > > > information
> > > > > > > > is the contact information which appears with every row of
> notes.
> > > I
> > > > > need to
> > > > > > > > end up with only one row for each customer with the notes in
> > > column K
> > > > > in one
> > > > > > > > cell. I cannot tell you how much I appreciate you looking at
> this
> > > for
> > > > > me!
> > > > > > > >
> > > > > > > > -Helpless1
> > > > > > > >
> > > > > > > > "Tom Ogilvy" wrote:
> > > > > > > >
> > > > > > > > > Assume the customer number is in column A and the text in
> column
> > > B
> > > > > > > > >
> > > > > > > > > select the cells in column A that you want to process, then
> run
> > > this
> > > > > macro
> > > > > > > > >
> > > > > > > > > Sub ProcessData()
> > > > > > > > > Dim rng As Range, cell As Range, s As String
> > > > > > > > > Set rng = Selection(1)
> > > > > > > > > For Each cell In Selection
> > > > > > > > > If cell.Value <> cell.Offset(1, 0).Value Then
> > > > > > > > > s = s & " " & cell.Value
> > > > > > > > > rng.Offset(0, 1).Value = Application.Trim(s)
> > > > > > > > > If rng.Row <> cell.Row Then
> > > > > > > > > Range(rng.Offset(1, 0), cell).Resize(, 2).ClearContents
> > > > > > > > > End If
> > > > > > > > > Set rng = cell.Offset(1, 0)
> > > > > > > > > s = ""
> > > > > > > > > Else
> > > > > > > > > s = s & " " & cell.Value
> > > > > > > > > End If
> > > > > > > > >
> > > > > > > > > Next
> > > > > > > > > Columns(2).SpecialCells(xlBlanks).EntireRow.Delete
> > > > > > > > > End Sub
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > Test this on a copy of your data
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > > Regards,
> > > > > > > > > Tom Ogilvy
> > > > > > > > >
> > > > > > > > > "Helpless1" wrote:
> > > > > > > > >
> > > > > > > > > > HELP! I am a very minimal user and have a huge annoying
> > > project
> > > > > that I keep
> > > > > > > > > > wondering if there is an easier way to accomplish. I am
> doing
> > > > > conversion work
> > > > > > > > > > and have about 29000 lines left of customer information.
> When
> > > the
> > > > > info from
> > > > > > > > > > our old system was imported the customer notes were
> inserted
> > > in a
> > > > > new cell
> > > > > > > > > > with every line of text. My current method is to filter by
> > > > > customer number
> > > > > > > > > > then concatenate every line of customer notes, then copy
> and
> > > paste
> > > > > special as
> > > > > > > > > > values. I then delete the other lines of text and move to
> the
> > > next
> > > > > customer
> > > > > > > > > > number. PLEASE tell me there is any easier way! Restore my
> > > sanity!
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

[Bob Phillips]

I have posted an example to http://cjoint.com/?hnpewUDvLT. Perhaps this will
help you work it through.

If you want to send me your workbook, I will implement it for you (I
understand the confidentiality issue). Mail me at bob xxdotxx ngs xxatxx
gmail xxdotxx com (do the obvious with that, as explained in my sig).

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Helpless1" <Helpless1@discussions.microsoft.com> wrote in message
news:6D30D64F-1F02-4386-AC40-3ECFD109C877@microsoft.com...
> I give up. I know you all think I'm a complete idiot because I'm sure what
> you are tell me is right, but it still isn't working for me. I'm sure its
> something I'm doing wrong. Thank you all again.
>
> "Bob Phillips" wrote:
>
> > That's perfectly understandable mate, the data was fine (apart from a
couple
> > of typos in the ids, which I spotted).
> >
> > I think (hope!) this does it for you
> >
> > Sub Test()
> > Dim iLastRow As Long
> > Dim i As Long
> > Dim rng As Range
> >
> > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
> > For i = iLastRow To 2 Step -1
> > If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
> > Cells(i, "K").Resize(1, 20).Copy Cells(i - 1, "L")
> > Cells(i, "A").Value = ""
> > End If
> > Next i
> >
> >
> >
Range("A1").Resize(iLastRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
> > End Sub
> >
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Helpless1" <Helpless1@discussions.microsoft.com> wrote in message
> > news:C9178FD3-2AAF-4474-851F-A4B635AFAB63@microsoft.com...
> > > I'm so sorry Bob. Thank you. I felt a little uneasy about publishing
all
> > of
> > > our customer's information to the web so I have recreated the exact
way my
> > > data is set up. When the notes came from the old system Excel inserted
the
> > > contact info in every row next to the notes. I have included the cells
the
> > > way that I need them just for clearification. This isn't showing up
quite
> > > right but it through columns A-K.
> > >
> > > What I have:
> > >
> > > Cust # Name Add1 Add2 Add3 City State Zip Phone Fax Notes
> > >
> > > 6546 AAA Add1 Add2 Add3 City State Zip Phone Fax Notes line 1
> > > 6456 AAA Add1 Add2 Add3 City State Zip Phone Fax Notes line 2
> > > 6456 AAA Add1 Add2 Add3 City State Zip Phone Fax Notes line 3
> > > 6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes line 1
> > > 6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes line 2
> > > 6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes lines 3
> > > 6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes lines 4
> > > 6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes lines 5
> > > 6458 Arrow Add1 Add2 Add3 City State Zip Phone Fax Notes line 1
> > > 6458 Arrow Add1 Add2 Add3 City State Zip Phone Fax Notes line 2
> > > 6458 Arrow Add1 Add2 Add3 City State Zip Phone Fax Notes lines 3
> > >
> > > What I need:
> > >
> > > 6546 AAA Add1 Add2 Add3 City State Zip Phone Fax Notes line 1Notes
line
> > > 2Notes line 3
> > > 6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes line 1Notes
line
> > > 2Notes lines 3Notes lines 4Notes lines 5
> > > 6458 Arrow Add1 Add2 Add3 City State Zip Phone Fax Notes line 1Notes
line
> > > 2Notes lines 3
> > >
> > > I can email you this small file if that would help.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > I feel like I am in a bit of a vacuum. Can you post the data that
you
> > have
> > > > that does this?
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (replace somewhere in email address with gmail if mailing direct)
> > > >
> > > > "Helpless1" <Helpless1@discussions.microsoft.com> wrote in message
> > > > news:7E4FC8DE-5A7F-4C0E-BA46-1F48841DD88A@microsoft.com...
> > > > > Well folks,
> > > > > That seemed as though it started to work (concatenated the notes
as I
> > > > > needed) then the screen started kind of blinking and then the
program
> > > > stopped
> > > > > responding.
> > > > > I feel bad taking up all of your time so stop helping whenever you
> > need
> > > > to.
> > > > > Thank you so much.
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > I think Tom meant
> > > > > >
> > > > > > Set rng = Range(cells(2,1),Cells(Rows.count,1).End(xlup))
> > > > > >
> > > > > >
> > > > > > --
> > > > > > HTH
> > > > > >
> > > > > > Bob Phillips
> > > > > >
> > > > > > (replace somewhere in email address with gmail if mailing
direct)
> > > > > >
> > > > > > "Helpless1" <Helpless1@discussions.microsoft.com> wrote in
message
> > > > > > news:81E74D6E-068F-4E50-8492-9A333B6C5004@microsoft.com...
> > > > > > > This revision gives syntax error message in line 3.
> > > > > > >
> > > > > > > "Tom Ogilvy" wrote:
> > > > > > >
> > > > > > > > Here is a revision. Assumes you have a header row in row 1
and
> > data
> > > > > > starts
> > > > > > > > in row2. Again, test on a copy of your data.
> > > > > > > >
> > > > > > > > Sub ProcessData()
> > > > > > > > Dim rng As Range, cell As Range, s As String
> > > > > > > > Set rng = Range(cells(2,1),Row.count,1).End(xlup))
> > > > > > > > For Each cell In rng
> > > > > > > > If cell.Value <> cell.Offset(1, 0).Value Then
> > > > > > > > s = s & " " & cell.Offset(0,10).Value
> > > > > > > > rng.Offset(0, 10).Value = Application.Trim(s)
> > > > > > > > If rng.Row <> cell.Row Then
> > > > > > > > Range(rng.Offset(1, 0), cell).Entirerow.ClearContents
> > > > > > > > End If
> > > > > > > > Set rng = cell.Offset(1, 0)
> > > > > > > > s = ""
> > > > > > > > Else
> > > > > > > > s = s & " " & cell.Offset(0,10).Value
> > > > > > > > End If
> > > > > > > >
> > > > > > > > Next
> > > > > > > > Columns(10).SpecialCells(xlBlanks).EntireRow.Delete
> > > > > > > > End Sub
> > > > > > > >
> > > > > > > > --
> > > > > > > > Regards,
> > > > > > > > Tom Ogilvy
> > > > > > > >
> > > > > > > > "Helpless1" wrote:
> > > > > > > >
> > > > > > > > > Tom,
> > > > > > > > > Thanks so much for your help! I should have mentioned
before
> > > > however
> > > > > > that I
> > > > > > > > > have other information between the customer number and
note.
> > The
> > > > > > customer
> > > > > > > > > number is in column A and the notes are in column K. This
> > other
> > > > > > information
> > > > > > > > > is the contact information which appears with every row of
> > notes.
> > > > I
> > > > > > need to
> > > > > > > > > end up with only one row for each customer with the notes
in
> > > > column K
> > > > > > in one
> > > > > > > > > cell. I cannot tell you how much I appreciate you looking
at
> > this
> > > > for
> > > > > > me!
> > > > > > > > >
> > > > > > > > > -Helpless1
> > > > > > > > >
> > > > > > > > > "Tom Ogilvy" wrote:
> > > > > > > > >
> > > > > > > > > > Assume the customer number is in column A and the text
in
> > column
> > > > B
> > > > > > > > > >
> > > > > > > > > > select the cells in column A that you want to process,
then
> > run
> > > > this
> > > > > > macro
> > > > > > > > > >
> > > > > > > > > > Sub ProcessData()
> > > > > > > > > > Dim rng As Range, cell As Range, s As String
> > > > > > > > > > Set rng = Selection(1)
> > > > > > > > > > For Each cell In Selection
> > > > > > > > > > If cell.Value <> cell.Offset(1, 0).Value Then
> > > > > > > > > > s = s & " " & cell.Value
> > > > > > > > > > rng.Offset(0, 1).Value = Application.Trim(s)
> > > > > > > > > > If rng.Row <> cell.Row Then
> > > > > > > > > > Range(rng.Offset(1, 0), cell).Resize(,
2).ClearContents
> > > > > > > > > > End If
> > > > > > > > > > Set rng = cell.Offset(1, 0)
> > > > > > > > > > s = ""
> > > > > > > > > > Else
> > > > > > > > > > s = s & " " & cell.Value
> > > > > > > > > > End If
> > > > > > > > > >
> > > > > > > > > > Next
> > > > > > > > > > Columns(2).SpecialCells(xlBlanks).EntireRow.Delete
> > > > > > > > > > End Sub
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > Test this on a copy of your data
> > > > > > > > > >
> > > > > > > > > > --
> > > > > > > > > > Regards,
> > > > > > > > > > Tom Ogilvy
> > > > > > > > > >
> > > > > > > > > > "Helpless1" wrote:
> > > > > > > > > >
> > > > > > > > > > > HELP! I am a very minimal user and have a huge
annoying
> > > > project
> > > > > > that I keep
> > > > > > > > > > > wondering if there is an easier way to accomplish. I
am
> > doing
> > > > > > conversion work
> > > > > > > > > > > and have about 29000 lines left of customer
information.
> > When
> > > > the
> > > > > > info from
> > > > > > > > > > > our old system was imported the customer notes were
> > inserted
> > > > in a
> > > > > > new cell
> > > > > > > > > > > with every line of text. My current method is to
filter by
> > > > > > customer number
> > > > > > > > > > > then concatenate every line of customer notes, then
copy
> > and
> > > > paste
> > > > > > special as
> > > > > > > > > > > values. I then delete the other lines of text and move
to
> > the
> > > > next
> > > > > > customer
> > > > > > > > > > > number. PLEASE tell me there is any easier way!
Restore my
> > > > sanity!
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

[crazybass2]

Helpless1,

Don't be so hard on yourself. I have found, given the nature of this forum,
even the simplest of explanations are misunderstood, or assumptions are made
that are not correct. Looking at your post with the data from your
spreadsheet I believe I have the code you are looking for.

Place the code in the sheet module. Select the Customer# range (for your
example 6456 to 6458). Then run the macro. The code will concatenate each
"Note" line with the same customer numer onto the row where that customer
number first appears. It will then remove the additional lines with that
customer number. NOTE: The data must be sorted by customer number for this
to work (as you have in your example).

Sub ProcessData()
Dim rng As Range, cell As Range, s As String
Dim i As Integer
Set rng = Selection(1)
i = 1
For Each cell In Selection
If cell.Value <> "" Then
Do
cell.Offset(0, 10).Value = cell.Offset(0, 10).Value & cell.Offset(i,
10).Value
cell.Offset(i, 0).ClearContents
i = i + 1
Loop While cell.Value = cell.Offset(i, 0).Value
Else
i = 1
End If
Next
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Mike

"Helpless1" wrote:

> I give up. I know you all think I'm a complete idiot because I'm sure what
> you are tell me is right, but it still isn't working for me. I'm sure its
> something I'm doing wrong. Thank you all again.
>
> "Bob Phillips" wrote:
>
> > That's perfectly understandable mate, the data was fine (apart from a couple
> > of typos in the ids, which I spotted).
> >
> > I think (hope!) this does it for you
> >
> > Sub Test()
> > Dim iLastRow As Long
> > Dim i As Long
> > Dim rng As Range
> >
> > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
> > For i = iLastRow To 2 Step -1
> > If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
> > Cells(i, "K").Resize(1, 20).Copy Cells(i - 1, "L")
> > Cells(i, "A").Value = ""
> > End If
> > Next i
> >
> >
> > Range("A1").Resize(iLastRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
> > End Sub
> >
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Helpless1" <Helpless1@discussions.microsoft.com> wrote in message
> > news:C9178FD3-2AAF-4474-851F-A4B635AFAB63@microsoft.com...
> > > I'm so sorry Bob. Thank you. I felt a little uneasy about publishing all
> > of
> > > our customer's information to the web so I have recreated the exact way my
> > > data is set up. When the notes came from the old system Excel inserted the
> > > contact info in every row next to the notes. I have included the cells the
> > > way that I need them just for clearification. This isn't showing up quite
> > > right but it through columns A-K.
> > >
> > > What I have:
> > >
> > > Cust # Name Add1 Add2 Add3 City State Zip Phone Fax Notes
> > >
> > > 6546 AAA Add1 Add2 Add3 City State Zip Phone Fax Notes line 1
> > > 6456 AAA Add1 Add2 Add3 City State Zip Phone Fax Notes line 2
> > > 6456 AAA Add1 Add2 Add3 City State Zip Phone Fax Notes line 3
> > > 6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes line 1
> > > 6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes line 2
> > > 6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes lines 3
> > > 6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes lines 4
> > > 6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes lines 5
> > > 6458 Arrow Add1 Add2 Add3 City State Zip Phone Fax Notes line 1
> > > 6458 Arrow Add1 Add2 Add3 City State Zip Phone Fax Notes line 2
> > > 6458 Arrow Add1 Add2 Add3 City State Zip Phone Fax Notes lines 3
> > >
> > > What I need:
> > >
> > > 6546 AAA Add1 Add2 Add3 City State Zip Phone Fax Notes line 1Notes line
> > > 2Notes line 3
> > > 6457 Acme Add1 Add2 Add3 City State Zip Phone Fax Notes line 1Notes line
> > > 2Notes lines 3Notes lines 4Notes lines 5
> > > 6458 Arrow Add1 Add2 Add3 City State Zip Phone Fax Notes line 1Notes line
> > > 2Notes lines 3
> > >
> > > I can email you this small file if that would help.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > I feel like I am in a bit of a vacuum. Can you post the data that you
> > have
> > > > that does this?
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (replace somewhere in email address with gmail if mailing direct)
> > > >
> > > > "Helpless1" <Helpless1@discussions.microsoft.com> wrote in message
> > > > news:7E4FC8DE-5A7F-4C0E-BA46-1F48841DD88A@microsoft.com...
> > > > > Well folks,
> > > > > That seemed as though it started to work (concatenated the notes as I
> > > > > needed) then the screen started kind of blinking and then the program
> > > > stopped
> > > > > responding.
> > > > > I feel bad taking up all of your time so stop helping whenever you
> > need
> > > > to.
> > > > > Thank you so much.
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > I think Tom meant
> > > > > >
> > > > > > Set rng = Range(cells(2,1),Cells(Rows.count,1).End(xlup))
> > > > > >
> > > > > >
> > > > > > --
> > > > > > HTH
> > > > > >
> > > > > > Bob Phillips
> > > > > >
> > > > > > (replace somewhere in email address with gmail if mailing direct)
> > > > > >
> > > > > > "Helpless1" <Helpless1@discussions.microsoft.com> wrote in message
> > > > > > news:81E74D6E-068F-4E50-8492-9A333B6C5004@microsoft.com...
> > > > > > > This revision gives syntax error message in line 3.
> > > > > > >
> > > > > > > "Tom Ogilvy" wrote:
> > > > > > >
> > > > > > > > Here is a revision. Assumes you have a header row in row 1 and
> > data
> > > > > > starts
> > > > > > > > in row2. Again, test on a copy of your data.
> > > > > > > >
> > > > > > > > Sub ProcessData()
> > > > > > > > Dim rng As Range, cell As Range, s As String
> > > > > > > > Set rng = Range(cells(2,1),Row.count,1).End(xlup))
> > > > > > > > For Each cell In rng
> > > > > > > > If cell.Value <> cell.Offset(1, 0).Value Then
> > > > > > > > s = s & " " & cell.Offset(0,10).Value
> > > > > > > > rng.Offset(0, 10).Value = Application.Trim(s)
> > > > > > > > If rng.Row <> cell.Row Then
> > > > > > > > Range(rng.Offset(1, 0), cell).Entirerow.ClearContents
> > > > > > > > End If
> > > > > > > > Set rng = cell.Offset(1, 0)
> > > > > > > > s = ""
> > > > > > > > Else
> > > > > > > > s = s & " " & cell.Offset(0,10).Value
> > > > > > > > End If
> > > > > > > >
> > > > > > > > Next
> > > > > > > > Columns(10).SpecialCells(xlBlanks).EntireRow.Delete
> > > > > > > > End Sub
> > > > > > > >
> > > > > > > > --
> > > > > > > > Regards,
> > > > > > > > Tom Ogilvy
> > > > > > > >
> > > > > > > > "Helpless1" wrote:
> > > > > > > >
> > > > > > > > > Tom,
> > > > > > > > > Thanks so much for your help! I should have mentioned before
> > > > however
> > > > > > that I
> > > > > > > > > have other information between the customer number and note.
> > The
> > > > > > customer
> > > > > > > > > number is in column A and the notes are in column K. This
> > other
> > > > > > information
> > > > > > > > > is the contact information which appears with every row of
> > notes.
> > > > I
> > > > > > need to
> > > > > > > > > end up with only one row for each customer with the notes in
> > > > column K
> > > > > > in one
> > > > > > > > > cell. I cannot tell you how much I appreciate you looking at
> > this
> > > > for
> > > > > > me!
> > > > > > > > >
> > > > > > > > > -Helpless1
> > > > > > > > >
> > > > > > > > > "Tom Ogilvy" wrote:
> > > > > > > > >
> > > > > > > > > > Assume the customer number is in column A and the text in
> > column
> > > > B
> > > > > > > > > >
> > > > > > > > > > select the cells in column A that you want to process, then
> > run
> > > > this
> > > > > > macro
> > > > > > > > > >
> > > > > > > > > > Sub ProcessData()
> > > > > > > > > > Dim rng As Range, cell As Range, s As String
> > > > > > > > > > Set rng = Selection(1)
> > > > > > > > > > For Each cell In Selection
> > > > > > > > > > If cell.Value <> cell.Offset(1, 0).Value Then
> > > > > > > > > > s = s & " " & cell.Value
> > > > > > > > > > rng.Offset(0, 1).Value = Application.Trim(s)
> > > > > > > > > > If rng.Row <> cell.Row Then
> > > > > > > > > > Range(rng.Offset(1, 0), cell).Resize(, 2).ClearContents
> > > > > > > > > > End If
> > > > > > > > > > Set rng = cell.Offset(1, 0)
> > > > > > > > > > s = ""
> > > > > > > > > > Else
> > > > > > > > > > s = s & " " & cell.Value
> > > > > > > > > > End If
> > > > > > > > > >
> > > > > > > > > > Next
> > > > > > > > > > Columns(2).SpecialCells(xlBlanks).EntireRow.Delete
> > > > > > > > > > End Sub
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > Test this on a copy of your data
> > > > > > > > > >
> > > > > > > > > > --
> > > > > > > > > > Regards,
> > > > > > > > > > Tom Ogilvy
> > > > > > > > > >
> > > > > > > > > > "Helpless1" wrote:
> > > > > > > > > >
> > > > > > > > > > > HELP! I am a very minimal user and have a huge annoying
> > > > project
> > > > > > that I keep
> > > > > > > > > > > wondering if there is an easier way to accomplish. I am
> > doing
> > > > > > conversion work
> > > > > > > > > > > and have about 29000 lines left of customer information.
> > When
> > > > the
> > > > > > info from
> > > > > > > > > > > our old system was imported the customer notes were
> > inserted
> > > > in a
> > > > > > new cell
> > > > > > > > > > > with every line of text. My current method is to filter by
> > > > > > customer number
> > > > > > > > > > > then concatenate every line of customer notes, then copy
> > and
> > > > paste
> > > > > > special as
> > > > > > > > > > > values. I then delete the other lines of text and move to
> > the
> > > > next
> > > > > > customer
> > > > > > > > > > > number. PLEASE tell me there is any easier way! Restore my
> > > > sanity!
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >