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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
> >
> >
> >
>
>
>