+ Reply to Thread
Results 1 to 9 of 9

Please help with a column/row transposing problem

  1. #1

    Please help with a column/row transposing problem

    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.


  2. #2
    Forum Contributor
    Join Date
    03-24-2004
    Location
    Edam Netherlands
    Posts
    181
    I think I can help you but I need a better example

  3. #3
    Bob Phillips
    Guest

    Re: Please help with a column/row transposing problem

    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)

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




  4. #4

    Re: Please help with a column/row transposing problem

    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.


  5. #5
    Bob Phillips
    Guest

    Re: Please help with a column/row transposing problem

    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)

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




  6. #6

    not enough columns

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



  7. #7
    Bob Phillips
    Guest

    Re: not enough columns

    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)

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

    >




  8. #8

    Re: not enough columns

    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.


  9. #9

    Re: not enough columns

    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.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1