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.

Macro Creation Request for Moving Data from Rows to Columns in a Non-Conventional Way

Discussion in 'Business Applications' started by dsalazar189, Feb 18, 2014.

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

    dsalazar189 Thread Starter

    Joined:
    Feb 18, 2014
    Messages:
    7
    "Macro Creation Request for Moving Data from Rows to Columns in a Non-Conventional Way"

    Hello, all! Hopefully somebody can help me with the following problem (please see the attached document).

    Presently, I have an Excel 2010 document containing variables 1-6 per country in rows for label 1 through label x. However, I need the table to contain the country level information as columns for each label so that there is only one row per label. For example, instead of row 1 containing label 1 argentina variables 1-6 then row 2 containing label 1 arab emirates variables 1-6, I need the table to contain row 1 label 1 columns argentina variable 1, argentina variable 2, etc...then arab emirates variables 1, arab emirates variable 2, etc.

    Also, the countries in the table that do not have information for a given label fall at the end of the list for each label instead of in alphabetical order, but they need to be placed in alphabetical order when "columnized." I have provided the arab emirates as an example. Alphabetically, it comes before Argentina, but in the list it comes after because it did not contain information for those given labels. Since information per country will vary from label to label, the countries must remain in alphabetical order in the columns regardless of whether information is provided or not per label.

    Is there a macro that may be written for this?

    Thank you so much, in advance, for taking the time to read my post! Happy day!
     

    Attached Files:

  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Hi, welcome to the forum.
    As far as I know there is no macro for this unless you decide to write it yourself or have someone try it for you.
    Thanks to Excel 2007 and newer you will be able to list 2370 countries with 6 variables
    I you have some rpogramming knowledge I suggest you must try and write down how you would approach this and then transalet it into vba code for the macro.
    I'll try and see if I understand some of this and also what you mean with Label 1 and Label 2 and the fact that these do or do not contain information.
    If I can come up with something I'll post it
     
  3. dsalazar189

    dsalazar189 Thread Starter

    Joined:
    Feb 18, 2014
    Messages:
    7
    Hi, Hans! Thank you for taking the time to look at my post. I have absolutely no programming knowledge, so I do not know how to even begin to approach code for this issue. Label 1 and label 2 continues on to label x. They are major titles of interest. Suggestions?

    Have a great day!
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Okay, I'll try and find some time to figure something out.
    I've got an idea but need to give it structure. Hope you have the patience :)*



    * if not . . . well . . .
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Hi,
    I did not add much comments to the vba code.
    The sorting of the results is not done yet.
    Press the Smiley to run the macro.

    The result will be placed in the output sheet
    Column A is intentionally left blank in the output

    Will explain moer some time later just give this a try
    If you want to test just fill the testsheet with your data and run the macro
     

    Attached Files:

  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Have you taken a look?
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    I've noticed that you haven't even visited the site but let's say it's because of the weekend.
    I took a look and made some changes to my previous macro

    The list seems to work fine now, I've added some rows of data to see what happens and all country names are sorted alphabetically and the ones that contain n/a and 0 are moved (also alphabetically) to the end.

    Once you've run the macro the output is placed in the output sheet.

    If you run the macro again the data is appended so maybe you will have to add a line of code deleting the data once it is copied.

    If you add some data to cuba and run the macro again, cuba will be placed after colombia and before the arab_emirates

    Give it a run and let me know if this is more or less the idea.
     

    Attached Files:

  8. dsalazar189

    dsalazar189 Thread Starter

    Joined:
    Feb 18, 2014
    Messages:
    7
    Hi, Hans! I am so excited to hear back from you! Let me take a look, then I will reply with the outcome.

    Have a great day!
     
  9. dsalazar189

    dsalazar189 Thread Starter

    Joined:
    Feb 18, 2014
    Messages:
    7
    Hi, Hans! I hope you are having a great day.

    I tried the Macro and it is almost exactly what I need. However, when I run the Macro, there are no labels on the output other than the variables for each country so I do not know which variables correspond to which titles because it places the data out of order. I am not sure why this is happening. Any suggestions?
     
  10. dsalazar189

    dsalazar189 Thread Starter

    Joined:
    Feb 18, 2014
    Messages:
    7
    BTW, I appreciate your help with this. Your time is valued. :)
     
  11. dsalazar189

    dsalazar189 Thread Starter

    Joined:
    Feb 18, 2014
    Messages:
    7
    The titles (label1, 2, 3, etc.) are out of order (ranking) and when I run the macros on a large scale, it places data out of order for each country...that is, label1 austria is found in label 3 austria columns instead of label 1 austria columns. It is seemingly random, but I am sure nothing in programming is random. :/
     
  12. dsalazar189

    dsalazar189 Thread Starter

    Joined:
    Feb 18, 2014
    Messages:
    7
    The following are my specific questions associated with the issues encountered with the Macros:

    1) Is there a way of including the label name in the output, because as it stands, I just see data and do not know which label it corresponds with. Having analyzed each row carefully, the data seems to have been alphabetized by label name, which leads me to question 2.

    2) Is there a way of the output placing labels in the order in which they were run, because the order is important.

    3) When running a large amount of data, the data is placed out of order in some of the rows. That is, label 1 austria is found in label 3 austria columns instead of in label 1 austria columns. Like I mentioned before, I now it seems random, but I am sure it is not. Is there a way to fix this? This is the most critical problem because then the data in the row for each label is incorrect for some rows and correct for others. It seems the output misplaces data for countries that are not really a part of a particular label (e.g. label 5 does not have data for estonia but data is included there from some other label anyway). Is there a way of keeping all 81 countries in alphabetical order at all times and just including zeros if there is no data present for a given label, instead of modifying the list every time a new country is added?

    You're a guru at this stuff, Hans! Can you help please? Let me know if I was not clear on the explanation and I will try to clarify.

    ALSO:

    There is also a problem with russia variables1-6 and russia_cis variables 1-6. Apparently, the output is collapsing these variables into one set of six columns instead of twelve, and it is using more than one row to show the information.
     
  13. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Hi, for some reason your replies dit not arrive.
    I just read your last 4 posts.
    Could you attach a sample file with some dummy data (un processed)

    In another sheet place the data as you would want it to show up.

    I do not understand what is out of order, but it could be because the columns are sorted too and if the variable names change the sorting ourde will change too.

    I'll need a sample file to try and figure this out.
     
  14. 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/1120209

  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