+ Reply to Thread
Results 1 to 10 of 10

Automatically transposing data and deleting zero values

  1. #1
    Registered User
    Join Date
    02-15-2012
    Location
    brisbane, australia
    MS-Off Ver
    Excel 2003
    Posts
    5

    Automatically transposing data and deleting zero values

    I have a huge amount of data which I wish to convert into a different orientation (transpose) and delete zero values. Is there a way I can program excel to do it automatically. I have hopefully attched a simplified version showing what I am trying to do as i am finding it hard to explain.
    Any help would be greatly appreciated.
    Attached Files Attached Files
    Last edited by oad2g; 02-15-2012 at 02:46 AM.

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Simple problem but is there a possible solution

    I wrote a transpose template which transpose all the data into a single column. After you run the macro, assign each value with a unique ID from 1 to xyz, then sort the data from large to small, then delete all the 0, then sort again by the unique ID to get the list to the original order. Make sure you have a tab called "data" and another tab called "output"

    Please Login or Register  to view this content.
    Last edited by JieJenn; 02-15-2012 at 02:35 AM.

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Simple problem but is there a possible solution

    Pl see the code . Run this macro on the relevant sheet.


    Please Login or Register  to view this content.
    Last edited by Mordred; 02-15-2012 at 03:18 AM.

  4. #4
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Help required with transposing data and deleting zero values

    @ JieJenn and kvsrinivasamurthy , please do not respond until there is a proper thread title (or at least close to one).

    Edit: @kvsrinivasamurthy, please use code tags to display code instead of quote tags. I have changed for you but please be mindful of that in the future.
    Last edited by Mordred; 02-15-2012 at 03:20 AM.
    If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.

    ---Keep on Coding in the Free World---

  5. #5
    Valued Forum Contributor mohan.r1980's Avatar
    Join Date
    09-18-2010
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2010 (windows7)
    Posts
    729

    Re: Automatically transposing data and deleting zero values

    Try This Code : or Find attached file

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

  6. #6
    Registered User
    Join Date
    02-15-2012
    Location
    brisbane, australia
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Automatically transposing data and deleting zero values

    wow thanks for your help. I often use if, and, or statements in excel but have never successfully used a macro. Thank you for the attached spreadsheet with the macro setup. How can I edit the macro you have sent so I can get it to suit the spreadsheet I am doing? Also does any one know of a good book on macros for someone starting out.

  7. #7
    Registered User
    Join Date
    02-15-2012
    Location
    brisbane, australia
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Automatically transposing data and deleting zero values

    Thanks again for your help I have been playing around with the the macro you sent but don't fully understand how it works and have only managed to change the output cell locations rather than the source cell locations.
    I have attached a spreadsheet showing the actual cell locations of the information that I am working with. If you could adjust the macro to suit the attached excel sheet I could then have a look at it to better understand what has been adjusted and how it affects the output.

  8. #8
    Registered User
    Join Date
    02-15-2012
    Location
    brisbane, australia
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Automatically transposing data and deleting zero values

    sorry forgot to attach
    Attached Files Attached Files

  9. #9
    Valued Forum Contributor mohan.r1980's Avatar
    Join Date
    09-18-2010
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2010 (windows7)
    Posts
    729

    Re: Automatically transposing data and deleting zero values

    Hi Friend,

    i have attached Same file with solution (See Result in "Final Process" sheets)
    but i fix the column No. as per your requirement
    if you change the column then you will get wrong data
    if you want to change the column No. then go to script(Press Alt+11>> Module1>>

    col and icolLoc2 variable
    where now i fix 5 and 26 value
    fix it as your requirement

    i am trying to give you user friendly solution but now a day i am busy in another projects
    so please wait.......
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    02-15-2012
    Location
    brisbane, australia
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Automatically transposing data and deleting zero values

    Thank you mohan. I am only just beginning to realise the possibilities in excel.

+ 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