How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask John D Your Own Question

John D
John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9658
Experience:  Bachelor of Science - Engineering Consultant
53785
Type Your Microsoft Office Question Here...
John D is online now
A new question is answered every 9 seconds

I have data that runs horizontally but I need all of it to

Resolved Question:

I have data that runs horizontally but I need all of it to be formatted vertically

Therefore one line of horizontal information contains multiple data points that need to be listed individually vertically. 1 horizontal line could equal 30 or 40 vertical lines
Submitted: 2 years ago.
Category: Microsoft Office
Expert:  John D replied 2 years ago.

Hi,

 

Would you be able to send me the file so I can take a look at the data. To do so go to http://www.wikisend.com/ and upload the file using the Browse button (no need to signup) then copy the "File ID" number (or the download link) that you will be given and paste it here in your reply.

 

If the file has sensitive information let me know before you upload it

 

Customer: replied 2 years ago.
ok
Customer: replied 2 years ago.
ok I uploaded the file You will see a "As is" current state of file and the future state is what I want it to look like
Expert:  John D replied 2 years ago.

Where is the File ID or download link

 

Customer: replied 2 years ago.
File 931314
Expert:  John D replied 2 years ago.

Got it, thanks

 

One moment while I take a look...

 

 

Expert:  John D replied 2 years ago.

Ok this cannot be done with formulas, functions or pivot table. A macro must be written to generate the list in that format. It will however take some time to set up the macro. Let me know

 

 

 

Customer: replied 2 years ago.

Ok Can you set up a macro. This is only a piece of one file for example only. The file has dates that from 1/1 - 6/30


 


 

Expert:  John D replied 2 years ago.
In that case I need the actual file so we don't have to setup the macro twice
Customer: replied 2 years ago.
Ok where do I submit it It is large 4 tabs and a total of about 10,000 lines combined in the 4 tabs each tab is a location
Expert:  John D replied 2 years ago.

Upload it at this same site that I gave you

 

 

 

 

Customer: replied 2 years ago.
File # XXXXX Please note that when you look at these files and page down you will come across another row of Column headings Each of these breaks in the file start a new 2 week period. You will see the Week ending at the top of the column headings. thanks
Expert:  John D replied 2 years ago.

That is a lot more complex than the sample sheet you sent me. Also do you want to generate vertical list for one sheet or all sheets

 

Accordingly I have sent a price adjustment report. Please confirm if the price looks ok to you and let me know so I can start working on this

 

 

 

Customer: replied 2 years ago.
vertical for all the sheets/tabs
Expert:  John D replied 2 years ago.

If you agree to the suggested price adjustment please confirm so I can start working on it

 

 

Customer: replied 2 years ago.
where is this report?
Expert:  John D replied 2 years ago.

JA should have sent it to you by email

 

 

Customer: replied 2 years ago.

I have not received anything via email


 

Expert:  John D replied 2 years ago.

hmm..There must be an issue with the site

 

Ok experts are not allowed to suggest a prices in this forum (only through a special price report via JustAnswer). But customer can make an offer and the expert can either take it or not. So you can make an offer and I will let you know

 

 

Customer: replied 2 years ago.
I am unsure of price. I will wait for the price report.
Expert:  John D replied 2 years ago.

No problem, but there are some reported issues where the system fails to send some emails

 

 

Expert:  John D replied 2 years ago.

In the meantime I finished the major part of the macro. Here is a screenshot of part of the output sheet for the first block of data on sheet 'Greenville'

 

 

Please check the above output and let me know is this is what you're trying to accomplish

 

 

Customer: replied 2 years ago.
That looks good!
Customer: replied 2 years ago.
Can I get a copy of the Greenville tab when completed so I can compare to my file and reconcile to ensure all the Units are captured then we can proceed with the other tabs.......Thanks
Expert:  John D replied 2 years ago.

Ok will send you the result the Greenville tab

 

 

Customer: replied 2 years ago.
Great Thank you
Expert:  John D replied 2 years ago.

Here is the Greensville output for the for the first block of data on Greensville (rows 4 thru 65)

 

 

Attachment: 2012-08-08_141955_greensville_output.xlsx

 

Please check it and let me know

 

 

Customer: replied 2 years ago.

IT LOOKS GOOD AFTER A QUICK REVIEW


 


 

Expert:  John D replied 2 years ago.

Great

 

I will send the file with the macro for that sheet which should work on any sheet with identical data structure (same columns and starting in the same row). Meanwhile you can rate the answer. Then when you get the file you can add the difference between the suggested price and the current question price as using the Add Bonus button

 

Thank you

 

John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9658
Experience: Bachelor of Science - Engineering Consultant
John D and 5 other Microsoft Office Specialists are ready to help you
Customer: replied 2 years ago.
Sounds Great
Expert:  John D replied 2 years ago.

Thanks. (but the bonus is not the correct balance amount)

 

Here is the file with the code.

 

http://www.filesharesite.com/files/201208/1344438132South_Carolina_-_Copy_2.xlsm

 

To run the macro click the Go! button on the Greensville main sheet. It will process all data from row 4 until it hits a title row with the phrase "Sort Options:" in column A. To make it work for all data on that sheet you need to remove all intermediate headers and type "Sort Options" in the last row so the macro can know where to stop

 

Please note that since there is a macro in the file you need to make sure that you have your Excel Options configured so that macros are allowed to run, otherwise nothing happens when you click the button. If you need assistance in this regard please let me know,.and provide the version of Excel you are running

 

 

 

Expert:  John D replied 2 years ago.

Please use this file instead

 

http://www.filesharesite.com/files/201208/1344440432South_Carolina_-_Copy_2a.xlsm

 

It takes care of Ref numbers so they are not increased by 1 within the same client name

 

 

 

 

Customer: replied 2 years ago.
Thank You. The Macro works well. The only question I have is when I ran the Macro for the next period in June it seemed like it is driven off of a range of cell A4-A65. Some of the periods and locations could have less then 62 rows while others could have in the 100's.
Expert:  John D replied 2 years ago.

Did you notice my comment about the phrase "Sort Options:" which was posted with the message where I sent you the first file.

 

Customer: replied 2 years ago.
Also, 1 more thing...how do I copy the Macro over to the other tabs???
Expert:  John D replied 2 years ago.

If you are not familiar with macros I can install a button for you on each sheet, but as I said before the data structure of all sheets should be identical to the first sheet for which the macro was written

 

Customer: replied 2 years ago.

Yes please install on all sheets for me. The structure should be identical


 


Thanks

Expert:  John D replied 2 years ago.

Ok I will do that. But as I mentioned before the bonus you paid falls quite short of what was suggested for the question. So how do you intend to correct that

 

Thanks

 

Expert:  John D replied 2 years ago.

Ok I have set up buttons on all the sheets so will be able to run the macro for each sheet. However as I mentioned before the sheets have to 100% identical with the first sheet for which the macro was created in order that the macro can produce the correct results

 

http://www.filesharesite.com/files/201208/1344462211South_Carolina_-_Copy_2a1.xlsm

 

Hope this helps

 

 

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional