Excel 2007 : combine text from other cell into one cell.

[justexcel-lent]

the following may or may not be a simple formula but i cannot seem to solve this problem without writing a drawn out formula. Here is obstacle I am encountering...i have the following information...

a b
1 hello h1
2 how h1
3 are h2

i would like a formula that finds all "h1" in column b and puts all text in column a (in this case "hello" and "how") into one cell, so the cell would look like "hellohow" (you would also need to add a space or comma or both to the cell.

thanks in advance.

[jeffreybrown]

If it is just these three cells then why not...

=IF(B2="h1",A2,"")&IF(B3="h1",A3,"")&IF(B4="h1",A4,"")

[dmang1]

Hello,

Do you have an example workbook to upload?

Diana

[dip11]

It's going to be a longish INDEX function using ROW to count through all instances of B:B to grab the h1 (or other varibale) in turn to produce the sentence.

I don't have Excel on this machine so I can't write you the exact code until Monday. Maybe someone else will by then.

[justexcel-lent]

If it is just these three cells then why not...

=IF(B2="h1",A2,"")&IF(B3="h1",A3,"")&IF(B4="h1",A4,"")

i would but that was the drawn out formula i was talking about. i have a lot of instances of "h1" up to 25 of them. i need a formula that can handle that. many thanks

[jeffreybrown]

Well see that is where the problem lies. If you just give us a little bit, that is all we have to work with.

Would it be possible to upload and sample workbook which details your exact requirements?

[Haseeb Avarakkan]

Insert this VBA Code,

Please Login or Register  to view this content.

Then use with CTRL+SHIFT+ENTER

=aconcat(IF(B1:B10="h1",A1:A10,""))

[justexcel-lent]

Insert this VBA Code,

Please Login or Register  to view this content.

Then use with CTRL+SHIFT+ENTER

=aconcat(IF(B1:B10="h1",A1:A10,""))

the above works, many thanks! but i was looking for a universal formula that was available that could be used across many platforms; such as google spreadsheet (i hope that word is not taboo here).

thanks again valiant effort!

[justexcel-lent]

Well see that is where the problem lies. If you just give us a little bit, that is all we have to work with.

Would it be possible to upload and sample workbook which details your exact requirements?

i have attached the file as per request. i hope this helps.

thanks, in advance.

[jeffreybrown]

To me a UDF may be the only answer, don't know, but I understand you are looking for a worksheet formula.

Well anyway, I don't have an answer, but I have posed this question to the formula guru's and maybe they can work some magic.

Also, when responding to posts, please see one of the forum rules.

12. Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

[Andy Pope]

code is the only way to concatenate range information, without referencing each cell in the range.

For formula only you will need to use a helper column to build the concatenation.

C1: =IF(B1="H1",A1,"")

C2: =TRIM(C1&IF(B2="H1"," " &A2,""))

copy down C2 to end of list.

The final display cell can then link to the last cell in the helper column. You can always hide the helper column.

[daddylonglegs]

Yes there's no inbuilt function in Excel which allows you to concatenate a range so I think your options are as follows:

1. Use a helper column to get the qualifying text and then concatenate that column (edit: as per Andy's suggestion)
2. Use a series of IF functions concatenated together like this

=TRIM(IF(B1="h1",A1&" ","")&IF(B2="h1",A2&" ","")&IF(B3="h1",A3&" ","")&IF(B4="h1",A4&" ","")&IF(B5="h1",A5&" ","")&IF(B6="h1",A6&" ","")&IF(B7="h1",A7&" ",""))

you can extend to 25 cells or further

3. Use a UDF as suggested by Haseeb
4. Download Morefunc add-in and use MCONCAT function which can concatenate a range.......but really that's not much different from a UDF

[JBeaucaire]

I would use a UDF for this, too. For a purely formulaic result you would need to "index" the values and collect the results one row at a time. Using a "helper" column, you can thus get the answer for any one code you want.

Using your same sheet, do this:

1) put the search code: "h1" into cell E2

2) put this results formula into cell F2:
=TRIM(INDEX(C:C, COUNTA(C:C)))

3) put this starter helper formula in C1:
=IF(B1=$E$2, A1, "")

4) Put this second helper formula in C2:
=IF(B2=$E$2, C1 & " " & A2, C1)

5) Copy C2 down the whole dataset


The formula in F2 will always display value of the last cell in the helper column, which will always show the full results no matter how long the dataset is.

6) change the code in E2 at anytime to get a new result in F2.

7) Copy the result to another cell and paste>values if you wish to save them.

[jeffreybrown]

Thanks gents.

Great support and much appreciated

[justexcel-lent]

thanks to all that have contributed to my question, although i didn't get exactly what i was looking for i got more than i expected. i guess udf is the only way to go and i have the code for that now so thanks again. have a great weekend!!!!
consider it solved

[jeffreybrown]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[justexcel-lent]

This is a follow-up to my previous question. Now that the b10 (for arguments sake) holds all the concatenated information, what formula can i use to show the information being removed from b10 once it has been redistributed somewhere else. so in a nutshell...there are a range of cells in worksheet 1, i show all the cells that meet my criteria concatenated into cell b10 on worksheet 2, then as i redistribute the concatenated cells into their individual cells there disappear from b10.
if you need further clarification i will upload a worksheet, but for those that have been following along should get the jest.
thanks in advance!!

[jeffreybrown]

Yes, please upload a worksheet showing a clear before and after.

[justexcel-lent]

i hope the attachment helps to clarify what i am looking for.

[Haseeb Avarakkan]

Are you looking to exclude DUPLICATE entries? If so try Mike Rickson's VBA.

Please Login or Register  to view this content.

Then try,

=CONCATIF(Sheet1!B1:B1000,"H1",Sheet1!A1:A1000,"",1)

Same like as SUMIF with 2 additional fields. delimiter & Duplicate

1 = exclude duplicate, 0 = include duplicate.