Removing spaces and adding comma's using a Macro
 

Hello,

I am looking for the next solution :

I have an excel sheet which gives me :
A B
Name 1 Problem 1
Name 1 Problem 2
Name 1 Problem 3
Name 2 Problem 1

I have a macro which does the the next :

A B C D
Name 1 Problem 1 Problem 2 Problem 3
Name 2 Problem 1

The Macro is as following :
-------------------------------
Sub Test()
Dim iLastRow As Long
Dim i As Long

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, "B").Resize(, 253).Copy Cells(i - 1, "C")
Rows(i).Delete
End If
Next i

End Sub
-------------------------------
1 Thing I have a problem with now :
Some names are provided with an add. space at the end of the name.
When this is, the cells are not the same and macro is not working correct.
1)How can I remove spaces after names in the same macro
2) How can I add comma's to the "problems" until the last "problem so it
looks like :

A B C D
Name 1 Problem 1, Problem 2, Problem 3
Name 2 Problem 1


Thanks in advance for every help.
Jeroen

Removing spaces and adding comma's using a Macro
 

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

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
Cells(i, "A").Value = Application.Trim(Cells(i, "A").Value)
Cells(i, "A").Value = Replace(Cells(i, "A").Value, Chr(160), "")
Cells(i - 1, "A").Value = Application.Trim(Cells(i - 1, "A").Value)
Cells(i - 1, "A").Value = Replace(Cells(i - 1, "A").Value, Chr(160),
"")
If i < iLastRow Then
If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
Cells(i - 1, "B").Value = Cells(i - 1, "B").Value & ","
End If
End If
If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
Cells(i, "B").Resize(, 253).Copy Cells(i - 1, "C")
Rows(i).Delete
End If
Next i

End Sub




--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Jeroen" wrote in message
...
Hello,

I am looking for the next solution :

I have an excel sheet which gives me :
A B
Name 1 Problem 1
Name 1 Problem 2
Name 1 Problem 3
Name 2 Problem 1

I have a macro which does the the next :

A B C D
Name 1 Problem 1 Problem 2 Problem 3
Name 2 Problem 1

The Macro is as following :
-------------------------------
Sub Test()
Dim iLastRow As Long
Dim i As Long

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, "B").Resize(, 253).Copy Cells(i - 1, "C")
Rows(i).Delete
End If
Next i

End Sub
-------------------------------
1 Thing I have a problem with now :
Some names are provided with an add. space at the end of the name.
When this is, the cells are not the same and macro is not working correct.
1)How can I remove spaces after names in the same macro
2) How can I add comma's to the "problems" until the last "problem so it
looks like :

A B C D
Name 1 Problem 1, Problem 2, Problem 3
Name 2 Problem 1


Thanks in advance for every help.
Jeroen

Removing spaces and adding comma's using a Macro
 

Thanks Bob,

very helpfull. It works for me ...


"Bob Phillips" schreef:

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

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
Cells(i, "A").Value = Application.Trim(Cells(i, "A").Value)
Cells(i, "A").Value = Replace(Cells(i, "A").Value, Chr(160), "")
Cells(i - 1, "A").Value = Application.Trim(Cells(i - 1, "A").Value)
Cells(i - 1, "A").Value = Replace(Cells(i - 1, "A").Value, Chr(160),
"")
If i < iLastRow Then
If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
Cells(i - 1, "B").Value = Cells(i - 1, "B").Value & ","
End If
End If
If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
Cells(i, "B").Resize(, 253).Copy Cells(i - 1, "C")
Rows(i).Delete
End If
Next i

End Sub




--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Jeroen" wrote in message
...
Hello,

I am looking for the next solution :

I have an excel sheet which gives me :
A B
Name 1 Problem 1
Name 1 Problem 2
Name 1 Problem 3
Name 2 Problem 1

I have a macro which does the the next :

A B C D
Name 1 Problem 1 Problem 2 Problem 3
Name 2 Problem 1

The Macro is as following :
-------------------------------
Sub Test()
Dim iLastRow As Long
Dim i As Long

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, "B").Resize(, 253).Copy Cells(i - 1, "C")
Rows(i).Delete
End If
Next i

End Sub
-------------------------------
1 Thing I have a problem with now :
Some names are provided with an add. space at the end of the name.
When this is, the cells are not the same and macro is not working correct.
1)How can I remove spaces after names in the same macro
2) How can I add comma's to the "problems" until the last "problem so it
looks like :

A B C D
Name 1 Problem 1, Problem 2, Problem 3
Name 2 Problem 1


Thanks in advance for every help.
Jeroen