Column Data Transpose With Repeated Row Data

  • Hi guys,


    I need to 'kind of' transpose a range of data and I have thousands of data cells so I guess a VBA macro would do the trick, however, I'm fairly n00b for that stuff, and I hoped one of you guys could help me out. You can see the attached example in regard to what I'd like to do with data; the input sheet obviously shows the data format I have right now, while the output sheet shows how I'd like data to be. It pretty much resembles a transpose of data, but with a twist though...


    (Btw, numbers 1-15 represent my data points, thus they range arbitrarily between empty cells (de facto zero), zero's and several hundred thousands.. hence, don't mind the linearity of the range 1 to 15... that's simply because that was easier to write :-p)


    In short, what I want to do is a transpose with a twist. I have something like:


    a, 1, 2
    b, 5, 6
    c, 10,15


    and I'd like:
    a, 1
    a, 2
    b, 5
    b, 6
    c, 10
    c, 15


    So kind of like a transpose, but keeping the records next to each value - see transpose.xls (input -> output) for more :-)



    Hope anyone can help - fire away if you need more info! :-)


    Best regards,
    Niko

  • Re: Help needed for advanced transposing (probably VBA code)


    Niko....



  • Re: Help needed for advanced transposing (probably VBA code)


    ncikusa,

    Welcome to the Ozgrid forum.

    With your raw data in worksheet Sheet1, a new worksheet Results will be created with your data transposed per your request.


    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


    1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
    2. Open your workbook
    3. Press the keys ALT + F11 to open the Visual Basic Editor
    4. Press the keys ALT + I to activate the Insert menu
    5. Press M to insert a Standard Module
    6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
    7. Press the keys ALT + Q to exit the Editor, and return to Excel
    8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).





    Then run the ReorgData macro.


    Have a great day,
    Stan

  • Re: Column Data Transpose With Repeated Row Data


    Hi ncikusa,


    Welcome to Ozgrid.



    Please take note of the following regarding thread titles:


    Thread titles are used in searching the forum, therefore, it is vital the be written to accurately describe your _thread content or overall objective_ using ONLY *search friendly key words*.


    * The title must not use non-essential words such as:"Help needed", "Formula problem", "Please help", "urgent", "Code issue", "Need Advice", etc. Such words dilute the title/search results.
    * The title should not contain VBA code or formula syntax or use abbreviations, jargon, delimiters
    * The title should not assume or anticipate a solution as in referencing Excel functions or VBA methods - the actual solution is often quite different

  • Re: Help needed for advanced transposing (probably VBA code)


    Hi guys,

    Huge thanks go to both of you.

    On another note, I actually forgot one column... the names (name1, name2, name3 etc.). Could either of you help adding this column to the output as well? I've added it in red in the attached output sheet (transpose_new.xls), so you know what I mean.

    Also, Stan, when running your code version I get a 'runtime error '9' - subscript out of range' error?

    iwrk, I have a whole bunch of lines and columns of this sort... hundreds.... (effectively giving thousands of rows) and when running your code, it seems to stop after some 10.400 records or so?

    I really appreciate all your help, guys! :D

    Regards,
    Niko

  • Re: Column Data Transpose With Repeated Row Data


    Niko...


    You'll need to tell me WHY it's stopping... Is it filling 65536 rows of data?



    Here's the code change

  • Re: Column Data Transpose With Repeated Row Data


    ncikusa,


    With your raw data in worksheet input, and worksheet output will contain the re-organized data.


    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


    1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
    2. Open your workbook
    3. Press the keys ALT + F11 to open the Visual Basic Editor
    4. Press the keys ALT + I to activate the Insert menu
    5. Press M to insert a Standard Module
    6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
    7. Press the keys ALT + Q to exit the Editor, and return to Excel
    8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.





    Then run the ReorgDataV2 macro.

  • Re: Column Data Transpose With Repeated Row Data


    Hi guys,

    Again, thanks for all your help... it's really appreciated!

    Barry, I think your code is probably OK, however the months are made into values and I thought I only needed to format the values to months, but that simply shows the months looping within the year or so - and I have several months - for a couple of years.

    Stan, I think your code is spot on... i'll check it out more thoroughly tomorrow :D

    Anyway, both you guys, sincerely... thank you. I wish I culd do that geekish coding; I do know a little VBA, but we're talking pure intro level stuff :P


    Best regards,

    Niko

  • Re: Column Data Transpose With Repeated Row Data


    Awwww Niko,


    Ya breaking my heart here... My code wasn't good enough... Sniff Sniff


    Stan cheated... he copy and pasted values.... All you had to do was format the column the same as you had formated those headers...



    It's ok tho... I've got soft forgiving ego (lol)


    I like my solution better tho.. it's cleaner and simpler (IMHO)



    Although STAN I'm not sure we should take the "GEEK" thing laying down.

  • Re: Column Data Transpose With Repeated Row Data


    ncikusa,

    Quote

    I wish I culd do that geekish coding; I do know a little VBA, but we're talking pure intro level stuff




    Training / Books / Sites:

    How to Learn to Write Macros
    http://articles.excelyogi.com/playin...ba/2008/10/27/

    How to use the macro recorder
    http://articles.excelyogi.com/

    Click here and scroll down to Getting Started with VBA.
    http://www.datapigtechnologies.com/ExcelMain.htm

    If you are serious about learning VBA try
    http://www.add-ins.com/vbhelp.htm

    Excel Tutorials and Tips - VBA - macros - training
    http://www.mrexcel.com/articles.shtml

    See David McRitchie's site if you just started with VBA
    http://www.mvps.org/dmcritchie/excel/getstarted.htm

    Debra Dalgleish has some notes how to implement macros here:
    Excel VBA -- Adding Code to a Workbook
    http://www.contextures.com/xlvba01.html

    David McRitchie has an intro to macros:
    http://www.mvps.org/dmcritchie/excel/getstarted.htm

    Ron de Bruin's intro to macros:
    http://www.rondebruin.nl/code.htm

    Creating custom functions
    http://office.microsoft.com/en-us/excel/HA011117011033.aspx

    Writing Your First VBA Function in Excel
    http://www.exceltip.com/st/Wri…unction_in_Excel/631.html
    http://www.excel-vba.com/
    http://www.mvps.org/dmcritchie/excel/getstarted.htm
    http://www.exceltip.com/excel_links.html

    (livelessons video)
    Excel VBA and Macros with MrExcel
    ISBN: 0-7897-3938-0
    http://www.amazon.com/Excel-Macros-M...7936479&sr=1-1
    http://www.youtube.com/user/ExcelIsFun#g/search
    http://www.xl-central.com/index.html
    http://www.datapigtechnologies.com/ExcelMain.htm

    Dependent validation lists. Debra has a neat little tutorial here.
    http://www.contextures.com/xlDataVal02.html

    Data Validation > Drop-Down Lists - Dependent
    http://www.bettersolutions.com/excel/EGH188/QE229212022.htm

    Cascading queries
    http://www.tushar-mehta.com/excel/ne...ing_dropdowns/
    http://www.contextures.com/xlDataVal05.html

    Excel Data Validation - Add New Items
    http://www.contextures.com/excel-data-validation-add.html

    Programming The VBA Editor - Created by Chip Pearson at Pearson Software Consulting LLC
    This page describes how to write code that modifies or reads other VBA code.
    http://www.cpearson.com/Excel/vbe.aspx

    Locating files containing VBA
    Searching Files in Subfolders for VBA code string:
    http://www.dailydoseofexcel.com/arch...a-code-string/
    http://www.pcreview.co.uk/forums/thread-978054.php

    Excel 2003 Power Programming with VBA, by John Walkenbach

    VBA and Macros for Microsoft Excel, by Bill Jelen "Mr.Excel" and Tracy Syrstad

    Excel Hacks 100 Industrial-Strength Tips & Tools, by David & Traina Hawley

    VBA and Macros for Microsoft Excel 2007, by Bill Jelen "Mr.Excel" and Tracy Syrstad
    Excel 2007 Book: you can try this...there is a try before you buy ebook available at this link…
    http://www.mrexcel.com/learnexcel2.shtml

    DonkeyOte: My Recommended Reading:

    Volatility
    http://www.decisionmodels.com/calcsecretsi.htm

    Sumproduct
    http://www.xldynamic.com/source/xld.SUMPRODUCT.html

    Introduction to Array Formulas
    http://www.xtremevbtalk.com/showthread.php?t=296012

    Using Pivot Tables and Pivot Charts in Microsoft Excel
    http://peltiertech.com/Excel/Pivots/pivotstart.htm

    A List of Pivot Table links at Jon Peltier's site (contributed by Debra Dalgleish) is here:
    http://peltiertech.com/Excel/Pivots/pivotlinks.htm

    Email from XL - VBA & Outlook VBA
    http://www.rondebruin.nl/sendmail.htm
    http://www.outlookcode.com/article.aspx?ID=40

    Excel Function Dictionary
    http://www.xlfdic.com/

    Excel 2007 function name translations
    http://www.piuha.fi/excel-function-name-translation/

    Dynamic Named Ranges
    http://www.contextures.com/xlNames01.html

    Where to paste code in VBE VBA
    Introducing the Excel VBA Editor
    http://www.ask.com/web?qsrc=24…e+in+the+Excel+VBA+Editor

    VBA for Excel (Macros)
    http://www.excel-vba.com/excel-vba-contents.htm

    VBA Lesson 11: VBA Code General Tips and General Vocabulary
    http://www.excel-vba.com/vba-code-2-1-tips.htm

    Excel VBA -- Adding Code to a Workbook
    http://www.contextures.com/xlvba01.html

    Basics of array formulas
    http://www.youtube.com/view_play_lis...7E7E9CA63304D3

    Array formula data extract formulas
    http://www.youtube.com/watch?v=Tp7I5u1MqiM
    http://www.youtube.com/watch?v=R5ZWAiNJLNo
    http://www.youtube.com/watch?v=132ZdpxBm1U

    How to create Excel Dashboards
    http://www.contextures.com/excel-dashboards.html
    http://chandoo.org/wp/excel-dashboards/

    Unique Record Counting and Data Extract formulas
    http://www.youtube.com/watch?v=uUrI8hoj8BA

  • Re: Column Data Transpose With Repeated Row Data


    LOL - if that's not a lot of reading material... i don't know what is - thanks for the info! :-)

    Barry; nothing personal intended - I really am happy for all your help :-)


    Stan, in your code, would it be possible to make the 'transposing' of datapoints include formulas and comments - perhaps 'All', as Excel would say, instead of values?

    See attachment (transpose_new2.xls) in which the top data is an example; both 'hardcoded' values and formulas and comments would be nice to have as in the output sheet, such that everything from the cell is transposed. If it is a hardcoded value then transpose the hardcoded value, if it is a formula, make the formula point to the right cells (which is always in the same row on the input sheet) etc.

    Best regards,

    Niko

  • Re: Column Data Transpose With Repeated Row Data


    ncikusa,


    Based on your latest request, and with your latest workbooks raw data, try this:

    Detach/open workbook ReorgData - w1 w2 - ncikusa - OZ151467 - V3 - SDG15.xls and run macro ReorgDataV3.


    If your data has more than three months (and, I am sure it does/will), the macro will not work correctly.

    I am not sure how to adjust the latest macro for more than three months of data.


    Have a great day,
    Stan

  • Re: Column Data Transpose With Repeated Row Data


    ncikusa,


    The following macro will copy formatting, comments, and adjust formulae to values, and has been tested with more monthy columns to the right (see worksheet Instructions for other sample raw data).

    Detach/open workbook ReorgData - w1 w2 - ncikusa - OZ151467 - V4 - SDG15.xls and run macro ReorgDataV4.


    Have a great day,
    Stan

  • Re: Column Data Transpose With Repeated Row Data


    Hi again Stan,


    Actually, in your code, you assume that the formulas I put in the example was the formulas to be used again and again, however, those were merely examples. What I'd like was that formulas in the input sheet is simply 'transposed' as well with the data in the output sheet. For instance, when you transpose a row of, say, hardcoded values and formulas, what you get is a column of the same data in which the formulas have also been transposed, referencing to the correct cells in the new column (that was cells in rows before).

    Is this possible to do in your code?


    Best regards,
    Niko

  • Hello -


    I just joined today...but long long time user of this form with excel questions.


    I realize that this thread is 10 years old, but it is very close to what I'm looking for. So, instead of posting a new thread, I'm hoping someone will see this.

    I'll reference the 'Input' tab in the attached 'ReorgData - w1 w2 - ncikusa - OZ151467 - V5 - SDG15.xls' excel sheet. Disregard Columns A, B and C.


    I'm trying to move info similar fashion, to create two different ordering sheets based on one of the columns...for this, let's say that Column D...so, we would have two new separate tabs, 'business1' and 'business2' to move data to instead of 'output'. So, in these two new sheets, the first column would be the dates that are currently in F1:H1, instead of the last column that is currently being moved to. The next column would be the Column E, placed to the correct sheet AND only if there are numbers present in F2:H6. So, for example, if G4 is 0 or blank, the Feb-11 date will not be moved, only the G6 line, to the 'business2' sheet.


    Thanks for the help...I can create a sheet to what I'd like the final product to be IF that would be more helpful.

    Matt