How to merge rows

Last week Hugo (California) asked me to create a new tool in ASAP Utilities to create what he calls a "tickerstring". This way multiple rows in a column will be merged into the first row. But you know what? It's already available:
Select the range, then in the menu select
ASAP Utilities > Columns/rows > Merge row data (join cells)
(I'm working hard on a User Guide but it is not finished yet)

Hi Bastien,

Hey, I'm loving your utilities!

But of course, there is one additional thing I would like to do ;-)

Regularly I analyse stocks. I use a screener on Fincelance Yahoo or so to identify all the stocks with certain characteristics. I output the data from the stock screener in Excel.

Once in Excel, I want to make a "ticker string" which I can paste in another program.

E.g. I have a spreadsheet with the following data in a certain column:

IBM
MSFT
GOOG
YHOO
etc.

Now I want to be able to do the following:
1. Select the cells IBM ... etc.
2. Choose the function "TickerString"

The function should return the string IBM,MSFT,GOOG,YHOO,etc.

I.e. this a kind of transpose function but the result goes in one cell and the values can be separated by a user-definable "string", e.g. "'" or a simple blank.

Hope you like this idea. It would be great if it somehow could be added to ASAP Utilities

Thanks!

Example
When you have the following range, and use the merge rows utility with a comma(,) the result will be that alle the cell value will be merge into cell [A2] (AAPL,AMAT,AMGN,ATML,BCRX, etc.) and cells [A3:A26] will become empty)

Before
After

If you want the cells to be seperated by a return (Alt+Enter) you can use:
ASAP Utilities » Columns / Rows » Merge row data separated by a return (join cells)

3 comments

Headtoadie

I use this one often – very useful. Would it be possible to create an option to not remove the source data, but instead to just copy it and put the consolidated data in a cell of the users choosing?

RandyH

Headtodie — how about a simple UDF for that:

Function sfJoin(myRange As Range, myDelimiter As String)
sfJoin = ""
For Each oCell In myRange
If sfJoin "" And oCell.Value "" Then sfJoin = sfJoin & myDelimiter
sfJoin = sfJoin & oCell.Value
Next oCell
End Function

MYINT SOE OO

I want rular in excel. If you can, please.
From Yangon, Myanmar.

Best Regards,
Myint Soe Oo