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: 9666
Experience:  Bachelor of Science - Engineering Consultant
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

This answer was rated:

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



Would you be able to send me the file so I can take a look at the data. To do so go to 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 4 years ago.
Customer: replied 4 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

Where is the File ID or download link


Customer: replied 4 years ago.
File 931314

Got it, thanks


One moment while I take a look...



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 4 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



In that case I need the actual file so we don't have to setup the macro twice
Customer: replied 4 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

Upload it at this same site that I gave you





Customer: replied 4 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

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 4 years ago.
vertical for all the sheets/tabs

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



Customer: replied 4 years ago.
where is this report?

JA should have sent it to you by email



Customer: replied 4 years ago.

I have not received anything via email


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 4 years ago.
I am unsure of price. I will wait for the price report.

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



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 4 years ago.
That looks good!
Customer: replied 4 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

Ok will send you the result the Greenville tab



Customer: replied 4 years ago.
Great Thank you

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 4 years ago.






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 and 2 other Microsoft Office Specialists are ready to help you
Customer: replied 4 years ago.
Sounds Great

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


Here is the file with the code.


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




Please use this file instead


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





Customer: replied 4 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.

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 4 years ago.
Also, 1 more do I copy the Macro over to the other tabs???

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 4 years ago.

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



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




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


Hope this helps



Related Microsoft Office Questions