+ Reply to Thread
Results 1 to 16 of 16

Convert single colum/multiple rows to multiple colums.

  1. #1
    Registered User
    Join Date
    06-08-2008
    Posts
    7

    Convert single colum/multiple rows to multiple colums.

    Hi,

    I have a .dat file when opened with Excel it has only 1 column with 7 rows per
    entry. I would like to delete some rows and convert the rest to something
    like I have below. Any chance this can be done? It's quite large. 51,793
    rows.

    TIA

    Jeff


    Col 1 Col 2 Col 3
    User Name Date Artist

    users name 06/04/2008 Lenny Kravitz - 2000 - Greatest Hits

    users name 02/03/2007 Cold War Kids - 2007 - Robbers & Cowards


    -- Table: {1}
    {
    "music"
    "users name",
    "06/04/2008",
    "Lenny Kravitz - 2000 - Greatest Hits",
    },

    -- Table: {2}
    {
    "music",
    "users name",
    "02/03/2007",
    "Cold War Kids - 2007 - Robbers & Cowards",
    },

    etc
    etc

  2. #2
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    Try this macro

    It works on the small sample data you supplied

    If it needs tweeking can you poast a larger sample - preferable a copy of the dat file

    Please Login or Register  to view this content.
    Please Read Forum Rules Before Posting
    Wrap VBA code by selecting the code and clicking the # icon or Read This
    How To Cross Post politely

    Top Excel links for beginners to Experts

    If you are pleased with a member's answer then use the Scales icon to rate it
    If my reply has assisted or failed to assist you I welcome your Feedback.

  3. #3
    Registered User
    Join Date
    06-08-2008
    Posts
    7
    Quote Originally Posted by mudraker
    Try this macro It works on the small sample data you supplied
    It did not work at first but I think the problem is there are no spaces between entries. It appears when I pasted in my post it created a space.

    I tried the macro with a space on line 8 etc and it worked.

    -- Table: {2} <----Line 8
    -- Table: {3} <----Line 15
    -- Table: {4} <----Line 22 etc....


    Sorry,


    TIA

    M

  4. #4
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    If your data reapeats every 7 rows try changimg

    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    06-08-2008
    Posts
    7
    Quote Originally Posted by mudraker
    If your data reapeats every 7 rows try changing
    That worked thanks. Of course one of the entries half way through the dat file was missing a bracket so it took me and hour to figure that out...

    Also, if you don't mind. I'm quite new with this and once saved I can't format the date cells so I can sort them? Year doesn't seem to matter. I replaced the "quotes" with nothing and I'm not sure if that matters.

    M



    02/03/2007
    02/03/2008
    02/03/2008
    02/03/2008
    02/03/2008
    02/03/2008
    02/03/2008
    02/04/2007

  6. #6
    Forum Contributor
    Join Date
    01-18-2005
    Location
    Auckland New Zealand
    MS-Off Ver
    Office Professional 2007
    Posts
    295

    Non-macro solution

    Been working slowly on a non-macro solution while others have posted, but I'll offer this anyway.
    Regards
    Mike
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    06-08-2008
    Posts
    7
    Quote Originally Posted by Mikeopolo
    Been working slowly on a non-macro solution while others have posted, but I'll offer this anyway.
    Regards
    Mike
    That also works for me. Being new at this, is there an easy way to apply it to a large spread sheet? I copy, pasted, dragged etc. and it worked but must be an easy way to save and apply to future (larger) sheets.

    TIA,

    M

  8. #8
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    For your date problem have a look at

    http://www.excelforum.com/showthread.php?t=646365

    If that solves the problem it can be built into the macro

  9. #9
    Registered User
    Join Date
    06-08-2008
    Posts
    7
    Quote Originally Posted by mudraker
    For your date problem have a look at,
    If that solves the problem it can be built into the macro
    That worked fine, Thanks.

    The macro leaves "quotes" around each entry. Is it possible to remove them?

    Original Table...
    -- Table: {1}
    {
    "music",
    "username",
    "06/08/2008",
    "Created Spread sheet",
    },
    -- Table: {2}
    {
    "music",
    "username",
    "01/29/2007",
    "Robert Randolph and the Family Band - Colorblind",
    },
    -- Table: {3}
    {
    "music",
    "username",
    "02/03/2007",
    "Cold War Kids - 2007 - Robbers & Cowards",
    },

    Result after Macro...
    "username" "06/08/2008" "Created Spread sheet"
    "username" "01/29/2007" "Robert Randolph and the Family Band - Colorblind"
    "username" "02/03/2007" "Cold War Kids - 2007 - Robbers & Cowards"

    Thanks again,

    M

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464
    Quote Originally Posted by merlinxl
    It did not work at first but I think the problem is there are no spaces between entries. It appears when I pasted in my post it created a space.

    I tried the macro with a space on line 8 etc and it worked.

    -- Table: {2} <----Line 8
    -- Table: {3} <----Line 15
    -- Table: {4} <----Line 22 etc....


    Sorry,


    TIA

    M

    The macro leaves "quotes" around each entry. Is it possible to remove them?
    try
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    06-08-2008
    Posts
    7
    Quote Originally Posted by jindon
    try
    Please Login or Register  to view this content.
    That didn't work.
    Run-time error 438
    Object doesn't support this property or method.
    Debug
    Cells(n, "c").Reszie(, 7).Value = _
    Evaluate("transpose(transpose(" & _
    Cells(i + 1, "a").Resize(7).Address & "))")

    Thanks for your help,

    M

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464
    Should be
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    06-08-2008
    Posts
    7
    Quote Originally Posted by mudraker
    Try this macro

    It works on the small sample data you supplied

    If it needs tweeking can you poast a larger sample - preferable a copy of the dat file

    Please Login or Register  to view this content.
    After changing row step to 7, this seems to work the best for me. If there is a way to remove the quotes that remain around the remaining Fields that would be appreciated.

    Thanks for all the help,

    M

  14. #14
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    replace
    Please Login or Register  to view this content.
    with
    Please Login or Register  to view this content.

  15. #15
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243
    hi all,

    Quote Originally Posted by jindon
    Should be
    Please Login or Register  to view this content.

    You may already have a working solution but I think the problem with this section of code is the typo ".reszie" which should read ".resize".

    hth
    Rob
    Rob Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464
    Quote Originally Posted by broro183
    hi all,




    You may already have a working solution but I think the problem with this section of code is the typo ".reszie" which should read ".resize".

    hth
    Rob
    OOps, Thanks Rob

    I didn't realise that...

+ 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