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!

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!

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!

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 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!

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!

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