Please help with a column/row transposing problem

[garyusenet]

Hello, 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.

 

[Kaak]

I think I can help you but I need a better example

 

[Bob Phillips]

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

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[garyusenet]

Thankyou very much for your excellent code,

I have run it and it has got me excited because it nearly seems to be
doing just what I need!

This is what's happening...

Once i've run it column-a is sorted into perfect ascending order and
all duplicate entries have been moved. However the data that was moved
appears to have been moved to the wrong place.

As I read across the row I can see that some data has been overwritten
in some columns and replaced with the data that we have just moved.
It's as if the data we moved is being pasted over the top of the
existing columns and not into it's own new columns?

I hope this makes sense and you can get a feel for what's happening. I
know very little code, and so simply copy and pasted your macro and
can't beleive that it's nearly working! I really appreciate your time.

Thanks,

Gary.

 

[Bob Phillips]

Gary,

It should be moving into new columns. I have assumed that columns C on are
empty to start with, is this a correct assumption?

Can you give an example of what it does and what it should do?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[garyusenet]

Bob,

Thanks again for your reply - I realised now the part of the macro that
referred to column C. Column C was not empty the first empty column was
AS - so I modified the macro and replaced C with AS. It is working but
I have hit another problem! It seems to have run out of columns at the
end of the sheet, as those customers that have a lot of products have
had the data which relates to the last product they purchased truncated
from the end of their row.

Suspecting it was because there wasn't enough columns, I went to the
end of a row and went to insert some columns - but the insert column
option is greyed out! It does appear that excel has run out of columns.


I have attempted to find an answer on the internet but haven't been
able to, hope you have a suggestion! And thanks again for your
brilliance in getting me the code to do what I needed in such quick
time.

Many Thanks

Gary.

 

[Bob Phillips]

Do you have the key in column A, and then data in B-AR? And if so, does that
mean that B-AR should be copied to the previous row on duplicates? And if
yet so, what if there is too much to go in the 256 columns Excel currently
has.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[garyusenet]

Bob,

I was trying to spare some of the finer details to simplify the problem
- but I can see that by doing this i've just made it more confusing, so
i'll explain exactly what i'm trying to do and hopefully it will put
this in context.

Thank you for getting me this far i'm miles ahead of where I would have
been had you not been kind enough to offer your expert assistence in
the first place.

....
I am using a CRM package called ACT. I am trying to get existing data
(written in a custom database) from the custom database into ACT. ACT
accepts comma delimitted text files by way of import, and so this is
the method I am using to import data from the bespoke database into
ACT.

The bespoke database has two main tables. Customers and Orders. I have
contructed a query using the wizard and told it that I want information
from the customers table and the orders table. It returns the data -
however it produces a new row for every order that the customer has
placed. The result is one large file with each row representing an
individual order. For this reason each row has many bits of duplicated
customer data. For example, name, address, telephone number etc...
appear on every row - even if ninety consecutive rows are the same
customer.

Now what i'm trying to do is get this data imported into Act. But Act
treats every row as a unique contact and so I need to make sure that
every company only has one row associated with it in this file. This is
what i'm trying to achieve by using Excel.

I had thought that if i removed all the generic columns - like customer
name, address etc... and just kept the key column (ID - which is column
A), and those columns which contain unique order information, then
perhaps it would fit. But that's not the case. Taking the frequent
customer that has placed 90 orders as an example - even after having
removed the generic columns from the spreadsheet i'm still left with 14
columns which contain unique order data. A simple calculation 14*90
proves that this data isn't all going to fit into one row, as i've now
learnt - excel has a limit of 250 columns.

So I have a dilemma the code you kindly gave me works fine. So I am
tantalisingly close. But I don't have enough columns!

If you need any further information please ask - I look forward to
hearing your thoughts on this problem.

Many Thanks,

Gary.

 

[garyusenet]

Bob just in case you read this or anyone else does with a simmilar
problem...

I've discovered that i've been coming at this from the wrong angle - on
reflection I think there must be a way to instruct access to produce a
table in the format i'm trying to achieve - one customer per row, many
orders per row.

So i'll try it from the DB end!

Many Thanks,

Gary.