• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

concatenate text in multiple rows

J

joharmenezes

Member
I have a file http://www.filedropper.com/book3 which has 2 worksheets, master & data. In the master WS I have 2 columns A & B. I would like to concatenate the rows in column B and need the output as shown in the data WS. I know this can be done using Fill-->Justify & then Replace but it becomes tedious if u have a large number of rows. I seem to have resolved this issue on my own using tranpose, COUNTIF and using the Replace option in MSWORD but this again requires some amount of manual work. So I was wondering if this process could be automated using only excel formulas and without using VBA?
 
Hi ,


I think the concatenated result may not be possible.


However , a listing of all entries , column-wise , is possible using formulae. Is this acceptable ?


Narayan
 
Hi Iron Man,


While Narayan suggested, i was working over the formula so here is the file if you like his suggested solution:


http://dl.dropbox.com/u/60644346/Iron%20Man_Solution.xlsx


Regards,

Faseeh
 
@Faseeh

Hi!

Nice formula for columns F in advance :)

Regards!

PS: it seems as getting paid by char again :p
 
Hi SirJB7,


It took more time to close the braces for the last part that handles NUM# error then to write rest of the formula, whenever paid by character your commission is fixed! :D


Faseeh
 
wow Faseeh u r a genius. but there is one small problem. I noticed that if u double click inside the cells it changes to "..". I wanted to change the ".." to just ""
 
Iron Man

Select Cell F4

Edit the cell with F2 and delete the .. at the end leaving just ""

Press Ctrl+Shift+Enter

Copy F4 Paste to G4:O4

Copy F4:O4 Paste down
 
I was going thru the function SMALL and it says that the fuction returns the kth smallest value from an array. but when I pasted this formula, SMALL(IF(ISBLANK($A$2:$A$27)=FALSE,ROW($A$2:$A$27)),ROW(A1)) in one of the cells I get numerial values, 2 5 15 19 25

which correspond to the row numbers of the nonblank cells in column A. But in column A I have the following values:

S0508

S0545 S1816

S2295

S1815


So what am I missing?
 
In your function, the bold part is what's is being outputted from the IF function

SMALL(IF(ISBLANK($A$2:$A$27)=FALSE,ROW($A$2:$A$27)),ROW(A1))


Thus, the SMALL function si returning ROW numbers. If you want the value in the cells, put this inside an INDEX function like:

=INDEX(A:A,SMALL(IF(ISBLANK($A$2:$A$27)=FALSE,ROW($A$2:$A$27)),ROW(A1)))

Now the row numbers will tell the INDEX function which cell value to return.
 
Hi Luke, I am sorry but that didn't help me. I have another question. So the SMALL function just returns the smallest value of an array, in this case it returns the smallest row #. So why can't I simply replace SMALL with just the ROW function? I did try it out, for example I entered the formula in E4 as =INDEX($A$2:$A$27,IF(ISBLANK($A$2:$A$27)=FALSE,ROW(A1)-1,0). I got the first value as S0508 which is correct but the rows below from E5 to E8 gave the values as S0508, 0, 0 and S0545. Why so?
 
Hi T100,


Is the formula not giving desired result or you want and explanation how does it work??


The formula can be simplified to a simple Index() function that takes a range as an argument, takes a row and column no. and returns the corresponding value in the range. The tricky part of the formula is how we adjust the range or how do we make it variable within the whole range in column A.


When we try to find the non Blank cells in Column A the the first smallest and the second smallest give us a row no. that corresponded to the first variable range when we convert it into a cell reference using and "&" Operator, this will be present in cell F4, similarly you will drag formula down to F5, the corresponding smallest values will be changes to 2nd and 3rd one, thus giving us the row nos. of them and we will convert them to cell references again.


The only problem arises when you reach the last smallest value since there is no further "Smallest" it will give you an error msg. To handle that you can see an If statement incorporated with CountA() function. That will revert the formula to counntA() if it faces an Error, the countA() will give you the nos. of Non-Blank cells, for this particular case it is 26 i think so again when feed and joined with "&" will give you a cell reference for the new range!!


Hope that helps!!


Faseeh
 
Hi Faseeh, the formula wors perfectly but I can't understand how this fuction, SMALL(IF(ISBLANK($A$2:$A$27)=FALSE,ROW($A$2:$A$27)),ROW(A1)) works. How does SMALL return the row nos. of the nonblank cells when it is supplied with rows from A2 to A27?
 
Hi T100,


Here is the formula:


Code: 
SMALL(IF(ISBLANK($A$2:$A$27)=FALSE,ROW($A$2:$A$27)),ROW(A1))


Lets start with the inner most braces for ISBLANK($A$2:$A$27), the result will be an array of True and False like below:


[code]SMALL(IF((False,True,True,.....,False,True,True)=FALSE,ROW($A$2:$A$27)),ROW(A1))


When compared to the equal sign it will give True for False & False for True:


[code]SMALL(IF((True,False,False,.....,True,False,False),ROW($A$2:$A$27)),ROW(A1))


...for "True" it will return the corresponding row no.


SMALL(IF((True,False,False,.....,True,False,False),(2,3,4,.....,25,26,27),ROW(A1))


OR


SMALL((2,False,False,.....,25,False,False),ROW(A1))[/code]


OR


SMALL((2,False,False,.....,25,False,False),1)[/code]


Evaluating SMALL() will result in 2.


Regards,

Faseeh
 
Wow thanks a lot Faseeh. Fantastic explanation. So the secret was the IF function, which checks every element to see if it's blank and then returns an array of the row numbers. So that's why the formula was entered as an array formula.
 
You must log in or register to reply here.
Back
Top