Concatenating text cells in varying length lists - Prettification

[geofspa]

Hi

I need to compile a number of text cells together - the entries might be 3 or 4 items long - or may be up to 50+ .

I have enetered formula as below and this does the job, but looks nasty - the result is an ever increasing text line - with only the last line being relevant.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]32240[/TD]
[TD]=IF(A1="","",A1)[/TD]
[TD] or[/TD]
[/TR]
[TR]
[TD]31759[/TD]
[TD]=IF(A2="","",CONCATENATE(B1,$C$1,A2))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31614[/TD]
[TD]=IF(A3="","",CONCATENATE(B2,$C$1,A3))[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

results in

[TABLE="width: 441"]
<tbody>[TR]
[TD][TABLE="width: 210"]
<tbody>[TR]
[TD]32240[/TD]
[/TR]
[TR]
[TD]32240 or 31759[/TD]
[/TR]
[TR]
[TD]32240 or 31759 or 31615[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 441"]
<tbody>[TR]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][TABLE="width: 441"]
<tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD]How can I alter my formula or change the way I look at joining the text so only the last line shows.

I am using Microsoft Windoze something and XL2013[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[NeonRedSharpie]

You're probably going to need to add a column because any other way is going to cause a circular reference and excel doesn't like that. So let's say you add in a column between B and C (or just use C for my example below).

In B, you have the formula that you currently have. Then you HIDE column B. In C, you add the formula:

Formula for C2:
=IF(A4="",B3,"")

This checks to see if it is the last populated cell and will return the HIDDEN column B value.

 

[Comfy]

You could use conditional formatting to change the text to white???

Or you could use a User Defined Function to concatenate a range with " or " as your separator.
http://www.contextures.com/rickrothsteinexcelvbatext.html

 

[geofspa]

Thanks that is just the nudge I needed .... Indeed If I move all the formula onto a 2nd work book leaving only the Text entry fields and a results cell
.... start naming ranges ,,,, it comes flooding back !!

 

[Rick Rothstein]

If I understand you correctly, you want to do that concatenation without the repeated formulas. If so, then you will need a UDF (user defined function) in order to do that given the variability in the number of cells you described. As long as the range of cells to be concatenated in your column are contiguous, you can use this one...

Function ConcatWithOr(Rng As Range) As String
  ConcatWithOr = Application.Trim(Join(Application.Transpose(Rng.Value), " or "))
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, 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. You can now use ConcatWithOr just like it was a built-in Excel function. For example, to concatenate the text in cells A2 through A50...

=ConcatWithOr(A2:A50)

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.

 

[geofspa]

Thanks all

My solution after you kicked my thought train (It was the end of a long and frustrating week)

have an entry/output worksheet, create a 2nd sheet for the calculations (Hidden)

for the output to one cell I used =IF(B6="",VLOOKUP('Input;Result'!B5,Result,2,FALSE),IF(B7="",VLOOKUP('Input;Result'!B6,Result,2,FALSE),IF... This may not be the most elegant solution but it works for me

Thanks again for your kind assistance.