combining rows and deleting easy for u experts

Customer Name 2000 sales 2001 Sales 2002 sales

Joe Smith $200.00
Joe Smith $300.00
Joe Smith $250.00
Kip Tucker $100.00
Kip Tucker $175.00
Kip Tucker $225.00

I need to combine these on to one row but leave them under the
appropriate year and then delete the duplicate names

Should look like this

Customer Name 2000 sales 2001 Sales 2002 sales

Joe Smith $200.00 $300.00 $250.00
Kip Tucker $100.00 $175.00 $225.00

I copied a formula from this board that is close but isn't quite what I
need. Can someone modify this formula or write a new one

Sub combine()
r = 1
Do Until Cells(r, 1) = ""
If Cells(r + 1, 1) = Cells(r, 1) Then
Cells(r, 2) = Cells(r, 2) & "," & Cells(r + 1, 2)
Rows(r + 1).Delete
Else
r = r + 1
End If
Loop
End Sub

An easier way to do this may be to use a pivot table. Put the customer
name in the row fields and the sum of the sales columns in data. You
could then drag the heading "Data" over the heading "Total" to get you
sales data into columns as shown in your results.

Hope this helps
Rowan

bamamike wrote:
> Customer Name 2000 sales 2001 Sales 2002 sales
>
> Joe Smith $200.00
> Joe Smith $300.00
> Joe Smith $250.00
> Kip Tucker $100.00
> Kip Tucker $175.00
> Kip Tucker $225.00
>
> I need to combine these on to one row but leave them under the
> appropriate year and then delete the duplicate names
>
> Should look like this
>
> Customer Name 2000 sales 2001 Sales 2002 sales
>
> Joe Smith $200.00 $300.00 $250.00
> Kip Tucker $100.00 $175.00 $225.00
>
> I copied a formula from this board that is close but isn't quite what I
> need. Can someone modify this formula or write a new one
>
> Sub combine()
> r = 1
> Do Until Cells(r, 1) = ""
> If Cells(r + 1, 1) = Cells(r, 1) Then
> Cells(r, 2) = Cells(r, 2) & "," & Cells(r + 1, 2)
> Rows(r + 1).Delete
> Else
> r = r + 1
> End If
> Loop
> End Sub
>

You have another response at your other post in .misc.

bamamike wrote:
>
> Customer Name 2000 sales 2001 Sales 2002 sales
>
> Joe Smith $200.00
> Joe Smith $300.00
> Joe Smith $250.00
> Kip Tucker $100.00
> Kip Tucker $175.00
> Kip Tucker $225.00
>
> I need to combine these on to one row but leave them under the
> appropriate year and then delete the duplicate names
>
> Should look like this
>
> Customer Name 2000 sales 2001 Sales 2002 sales
>
> Joe Smith $200.00 $300.00 $250.00
> Kip Tucker $100.00 $175.00 $225.00
>
> I copied a formula from this board that is close but isn't quite what I
> need. Can someone modify this formula or write a new one
>
> Sub combine()
> r = 1
> Do Until Cells(r, 1) = ""
> If Cells(r + 1, 1) = Cells(r, 1) Then
> Cells(r, 2) = Cells(r, 2) & "," & Cells(r + 1, 2)
> Rows(r + 1).Delete
> Else
> r = r + 1
> End If
> Loop
> End Sub

--

Dave Peterson