Hello

I have been trying to achieve something for a number of days.. my
original post follows, with a solution from Bob which was a great piece
of code that almost worked...

================================================== ===============
Original Post: -

I have been puzzling over this for some time now and am sure a
seasoned excel person will have a solution.

-----------------------------------------------------------------
First let me explain the spreadsheet.
-----------------------------------------------------------------
It is a list of customers, and purchases that each customer has made.


every purchase has a unique row.


for example: -


CUSTOMER ITEM
customer a someitem
customer a someitem2
customer a someitem3
customer b someitem4


-------------------------------------------
What I want to end up with
-------------------------------------------
customer a someitem someitem2 someitem3
customer b someitem4
etc ...


the problem is that the number of purchases varies between customers,
some have fifteen or so whilst some have three so i'm having trouble
figuring out a way of automating it.
I've simplified the example but in reality there are about 10 columns
which belong to each purchase, and these are what I need to append to
the end of the customer row.


I hope someone can help because there are over 4 thousand rows - so to
do this manually is going to take me a week of sundays


Thanks,
Gary.


Reply »


From: Bob Phillips - view profile
Date: Tues, Jul 25 2006 12:14 pm
Email: "Bob Phillips"
Groups: microsoft.public.excel.programming
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author


Excellent presentation of the problem, made me want to solve it.

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


Application.ScreenUpdating = false
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(, 200).Copy Cells(i - 1, "C")
Rows(i).Delete
End If
Next i
Application.ScreenUpdating = True


End Sub

================================================== ==
Now. Back to this post.

The reason this didn't work for me is that some customers had 90 rows,
and so they couldn't all be copied to columns because excel ran out of
columns. I have since obtained from Microsoft a beta of office 12 - and
now have more than enough columns. I have saved the worksheet in excel
2007 format and now have access to the extra columns, but - when I run
the macro it only fills in columns upto IJ so it's still not working
right.

Can someone help please?

Thanks,

Gary.