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!

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!

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" wrote in message
...
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!

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!

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!

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!

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.Delet e
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!

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!

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.Delet e
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!

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" wrote in message
...
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.Delet e
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!

This sort of task is best made with macros - using the record macro is a
good start. You should follow a course that covers macros; it is
essential for this kind of work!


--
raypayette


------------------------------------------------------------------------
raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
View this thread: http://www.excelforum.com/showthread...hreadid=560693

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" wrote in message
...
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.Delet e
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!

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" wrote in message
...
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" wrote in message
...
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.Delet e
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!

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" wrote in message
...
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" wrote in message
...
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.Delet e
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!

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(xlCellTy peBlanks).EntireRow.Delete
End Sub



--
HTH

Bob Phillips

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

"Helpless1" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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.Delet e
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!

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

Range("A1").Resize(iLastRow).SpecialCells(xlCellTy peBlanks).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" wrote in message
...
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(xlCellTy peBlanks).EntireRow.Delete
End Sub



--
HTH

Bob Phillips

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

"Helpless1" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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.Delet e
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!

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(xlCellTy peBlanks).EntireRow.Delete
End Sub



--
HTH

Bob Phillips

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

"Helpless1" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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.Delet e
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!

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" wrote in message
...
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(xlCellTy peBlanks).EntireRow.Delete
End Sub



--
HTH

Bob Phillips

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

"Helpless1" wrote in message
...
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" wrote in message
...
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" wrote in
message
...
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.Delet e
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,

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(xlCellTy peBlanks).EntireRow.Delete
End Sub



--
HTH

Bob Phillips

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

"Helpless1" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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.Delet e
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!