+ Reply to Thread
Results 1 to 14 of 14

Advanced transpose macro

  1. #1
    Registered User
    Join Date
    12-19-2008
    Location
    canada
    MS-Off Ver
    2007
    Posts
    10

    Advanced transpose macro

    Hi,

    I am desperately seeking a better way of transposing a large amount of data. By the looks of other posts, my objective is possible with a bit of VBA. Sadly, I am VBA illiterate. I sincerely appreciate your help.

    My data looks like this (simplified) [ data is separated by columns "|".

    A | May 1 | Jun 25 | Aug 9 | Dec 12
    B | Apr 1 | Oct 25
    C | Jan 6 | July 7 | Nov 11

    I want to make it look like this:
    A | May 1
    A | June 25
    A | Aug 9
    A | Dec 12
    B | Apr 1
    B | Oct 25
    C | Jan 6
    C | Jul 7
    C | Nov 11

    I have accomplished this in the past, but it involved importing the table into access, then creating a query for each of the "date" columns, then copying and pasting the results into one table. It was a painful experience considering there are over 2,000 lines on the original table (resulting in 25,00 lines in the combined query).

    Any insight you have would be very very greatly appreciated.

    -J

  2. #2
    Registered User
    Join Date
    12-19-2008
    Location
    canada
    MS-Off Ver
    2007
    Posts
    10

    From another post, but needs modification

    I saw this on another post, but it needs some mod to work in my situation. Thanks for your help!

    Please Login or Register  to view this content.

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    How about posting a workbook with a meaningful sample of the data (a hundred or so rows)?
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Registered User
    Join Date
    12-19-2008
    Location
    canada
    MS-Off Ver
    2007
    Posts
    10

    The data to transpose

    Here is an excerpt of the data I am trying to transpose.

    Thank you again for your help.

    J
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606
    Does this do what you want? It puts the results on sheet2.
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    12-19-2008
    Location
    canada
    MS-Off Ver
    2007
    Posts
    10

    Very close!

    This is pretty close, but I need to have the original data in column A repeated alongside the transposed dates (now in column B).

    Like this:
    PN | Date
    A | Jun 1
    A | July 1
    A | Aug 4

    Currently, its like this:
    A
    Jun 1
    July 1
    Aug 4

    Thanks for your amazingly quick response.

    J.

  7. #7
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606
    Try this:
    Please Login or Register  to view this content.

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Maybe ...
    Please Login or Register  to view this content.
    Edit: Stephen, I thought you were off drinking eggnog

  9. #9
    Registered User
    Join Date
    12-19-2008
    Location
    canada
    MS-Off Ver
    2007
    Posts
    10

    Excited to check it out on Monday

    Thanks very much for the amazing insight on this. I didnt have a chance to check it before I left for the weekend. It will be the first thing I do on Monday.

    Thanks again and have a great weekend.

  10. #10
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606
    shg: taking it easy ... too much eggnog the day before.

  11. #11
    Registered User
    Join Date
    12-19-2008
    Location
    canada
    MS-Off Ver
    2007
    Posts
    10

    Mmm. One amendment if possible please

    Hi again,

    The macro you've provided works great. I wondered if I could ask your wisdom on how to modify it slightly. In some cases, I there are two (or more) columns of data between the Part Number and the series of Dates to transpose. What aspect of the code would I modify to expand the range?
    I have attached an example for clarification.

    I made several attempt to figure it out myself, but it didnt work out at all

    Thank you again very kindly.

    J.
    Attached Files Attached Files

  12. #12
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606
    Have amended the code and added a few comments which I hope elucidate matters. Come back if not. If it's going to vary a lot, you could do something fancier, such as prompting the user to specify which columns to transpose etc.
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    12-19-2008
    Location
    canada
    MS-Off Ver
    2007
    Posts
    10
    Amazing - thank you so very much. I sincerely appreciate your help. If this parameters change, I think I can use your comments to modify the code. Thank you again.

    -J.

  14. #14
    Registered User
    Join Date
    07-28-2011
    Location
    Melbourne
    MS-Off Ver
    Excel 365
    Posts
    14

    Re: Advanced transpose macro

    Hi Guys This thread is very useful!I was wondering if the code can be modified to also include the heading the date relates to.

    I have tried to put something together but had no luck. I have been using the code StephenR posted on the 01-06-2009, 08:55 PM in the example file Jleopard posted on the 01-06-2009, 08:36 AM

    Originally the result was

    Transpose to:

    A blue large 1-May
    A blue large 10-May
    A blue large 11-May
    A blue large 12-May
    A blue large 13-May
    A blue large 14-May
    B green small 1-Jun
    B green small 2-Jun
    B green small 3-Jun
    B green small 4-Jun
    B green small 5-Jun
    B green small 6-Jun



    But i now need a result as follows:

    Transpose to:

    A blue large Date1 1-May
    A blue large Date 2 10-May
    A blue large Date 3 11-May
    A blue large Date 4 12-May
    A blue large Date 5 13-May
    A blue large Date 6 14-May
    B green small Date1 1-Jun
    B green small Date 2 2-Jun
    B green small Date 3 3-Jun
    B green small Date 4 4-Jun
    B green small Date 5 5-Jun
    B green small Date 6 6-Jun


    Does anyone know if StephenR's code can modified to include this? This save me heaps of time

    Thanks


    V
    Last edited by vbarone; 10-25-2011 at 12:41 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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