1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Moving data from columns to rows

Discussion in 'Business Applications' started by pflynn, Feb 15, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. pflynn

    pflynn Thread Starter

    Joined:
    Feb 15, 2012
    Messages:
    3
    Hey,

    I have imported sales data in excel. The information lists in along two rows with mutliple columns. I need the information to be in one row under various headings. I have attached a link to a sample to explain this properly. I think I need to use a macro but I don't understand enough about them to make it work.

    Any help would be much appreciated.


    Cheers


    https://docs.google.com/spreadsheet/ccc?key=0Av0-qDbMrH0rdHJWaWlReXd4RXptWlpwME50UTBsclE
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Looks like a nice challenge.
    See what I can do.
     
  3. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Select the yellow cells (Names in column C) in the attached, then run the test macro.

    You have to tidy up the column headers afterwards, but is that close enough?

    Sub test()
    For Each Cell In Selection
    If Cell <> "" Then
    new_data = ";" & Cell.Offset(, 3) & ";" & Cell.Offset(1, 1) & ";" & _
    Cell.Offset(1, 2) & ";" & Cell.Offset(1, 3) & ";" & Cell.Offset(1, 4)
    Cell.Offset(, 3).Resize(, 6) = Split(new_data, ";")
    Cell.Offset(1, 1).Resize(, 4).ClearContents
    End If
    Next Cell
    End Sub
     

    Attached Files:

  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Here's my solution

    The macro is named Testing() and will apply the changes to Sheet2

    Sheet1 was your sample, Sheet3 is backup to reset the Sheet2

    The VBA code contains all the remarks and explanation
     

    Attached Files:

  5. pflynn

    pflynn Thread Starter

    Joined:
    Feb 15, 2012
    Messages:
    3
    Bomb # 21 - this is pretty close but the postcodes fail to line up, Keebellah, in the sample you sent the postcode line up but i can't get the macro to work in my real document. When i put the code in and go to run it nothing happens???
     
  6. pflynn

    pflynn Thread Starter

    Joined:
    Feb 15, 2012
    Messages:
    3
    I just realised i forgot to say thank you - thanks!
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    "the postcodes fail to line up"

    When I run it they all parse to column K, except for the row with "The Shop" because of fewer values in its "sub-row". If there ought to be a "padding" cell (in the output) for:

    GYMPIE ... QLD ... 4570

    , how could code know where to place it?
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    There is a check line

    If eCol >= vCol Then

    I added thsi in case the 'next. row was too short,

    But if you post a sample with the data where it goes wrong I'll check it.
    The macro works, it dependes on the data if it retruns the correct results, macro's can't 'guess':)
     
  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    you can replace

    Code:
           If eCol >= vCol Then
    
    the line of code with:

    Code:
           If eCol >= Application.Match("Customer Name", ws.Rows(1), 0) Then
    
    if the last filled column below the row before the row containing 'Customer Name ' the line will be skipped, no update.

    Remeber I mentioned that teh assumption is that the last filled colum MUST be the PostCode if not the data off-synch
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    And, is it working now?
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1041255

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice