Splitting multiple column entries into separate rows

[hebruiser]

Hi all,

I'm not very well-versed in the syntax of Excel, so please bear with me. I am trying to take each of multiple entries in one column and in one row and split that into several rows (one for each entry in that column) and copy/paste the other column's information into the new rows.

So, basically, I would like to have a new line with all of the information EXCEPT the stock tickers repeating for each new line. Does that make sense?
Thanks in advance for any help or guidance!

[Glenn Kennedy]

Non-editable pictures are useless. Read the yellow banner (top) and post a small REPRESENTATIVE sample EXCEL sheet.

[hebruiser]

Hi Glenn,
Thanks for forcing me to read the instructions. I hope I attached this snippet correctly...I am not as tech savvy as I once was.

[Rick Rothstein]

Will there be other rows of data under the single row you show in your example file?

[hebruiser]

Yes. So for each row, I want to have all of the column data that is NOT a stock ticker repeat on a new row. So in effect, this report was run based off of that initial value in the first row. The client would prefer a method to display "verbose" information breaking out that data into different rows for each ticker. So, if you imagine each ticker as it's own row, all of the other data from that example would need to be repeated for each new row. I hope that makes sense.

[Rick Rothstein]

See if the following macro works for you. Note - you can change any of the requirements via the four Const statements.

Please Login or Register  to view this content.

[hebruiser]

Thank you for taking the time to do this. However, I am not having much luck getting this macro into Excel...because I am old and not so good at numbers. I can write a helluva poem, though!
Thanks!

[alansidman]

Can also be achieved using Power Query

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

[Rick Rothstein]

However, I am not having much luck getting this macro into Excel...

See if this helps...

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (RedistributeData) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

[hebruiser]

Thank you Rick! That worked perfectly. Now I have to decide if it's worth it to release this into the client's hands or offer to do this for them once a month.
Thanks again!

[alansidman]

Did you try out my solution in post #8?

[hebruiser]

Alansidman,
I have not yet. I am currently gauging the client's "receptiveness" to the VB macro. Thank you!