Concatonate - formula too long
 

I have a cell that populates by determining if the columns next to it have an
"X" entered. If they do, the name of the column is concatenated to the
current text, followed by a comma. If there is no "X", no text is added. I
use the formula below:

=CONCATENATE(IF(E3="X",$E$2&", ","")&IF(F3="X",$F$2&",
","")&IF(G3="X",$G$2&", ","")&IF(H3="X",$H$2&", ","")&IF(I3="X",$I$2&",
","")&IF(J3="X",$J$2&", ","")& etc...

This has woked fine for awhile, but now we have too many columns and the
formula is too long.

Any suggestions on how to make this work with a shorter formula?

Thanks in advance.

Concatonate - formula too long
 

Try this idea instead. Modify to suit
=INDEX(E13:J13,1,MATCH("x",E14:J14,0))
--
Don Guillett
SalesAid Software

"Foo2U" wrote in message
...
I have a cell that populates by determining if the columns next to it have
an
"X" entered. If they do, the name of the column is concatenated to the
current text, followed by a comma. If there is no "X", no text is added.
I
use the formula below:

=CONCATENATE(IF(E3="X",$E$2&", ","")&IF(F3="X",$F$2&",
","")&IF(G3="X",$G$2&", ","")&IF(H3="X",$H$2&", ","")&IF(I3="X",$I$2&",
","")&IF(J3="X",$J$2&", ","")& etc...

This has woked fine for awhile, but now we have too many columns and the
formula is too long.

Any suggestions on how to make this work with a shorter formula?

Thanks in advance.

Concatonate - formula too long
 

Foo2U2,

It sounds as though you've set up your table sideways, compared to the normal table layout.
That is, you have related stuff in columns instead of rows. There are many Excel tools that
won't help you with such a layout. If it's possible to have your table rearranged so it's
row for column, then you could use autofilter for this requirement. To rearrange the table,
copy it, then paste special somewhere else with transpose, then move it back over the
original. If you have more than 256 rows now, you'll end up with too many columns, and
won't be able to do this unless you have Excel 2007.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Foo2U" wrote in message
...
I have a cell that populates by determining if the columns next to it have an
"X" entered. If they do, the name of the column is concatenated to the
current text, followed by a comma. If there is no "X", no text is added. I
use the formula below:

=CONCATENATE(IF(E3="X",$E$2&", ","")&IF(F3="X",$F$2&",
","")&IF(G3="X",$G$2&", ","")&IF(H3="X",$H$2&", ","")&IF(I3="X",$I$2&",
","")&IF(J3="X",$J$2&", ","")& etc...

This has woked fine for awhile, but now we have too many columns and the
formula is too long.

Any suggestions on how to make this work with a shorter formula?

Thanks in advance.

Concatonate - formula too long
 

A simple Function is much easier to work with the a very long formula. They
are also easier to debug and easier to write. You can call the function with
your parameters. try it, your will like it.

Excel spreadsheet call (looks like the standarrd functtions)
=MyFunction (E3, F3, G3, H3, I3)


In VBA where the return value is Myfunction (the function name)

Function Myfunction( Parm1, Parm2, Parm3, Parm4, Parm5)

Myfunction = False

if( Parm1 = Parm2) then
Myfunction = true
end if

End Function


Here is one a wrote tthis morning
Function FindLast(ShortString, LongString) As Integer

Shortlength = Len(ShortString)
LongLength = Len(LongString)

FindLast = 0
If (Shortlength <= LongLength) Then

StringPosition = LongLength - Shortlength + 1

For i = StringPosition To 1 Step -1

If (StrComp(ShortString, Left(Mid(LongString, i, Shortlength),
Shortlength)) = 0) Then
FindLast = i
Exit For
End If

Next i

End If

End Function



"Foo2U" wrote:

I have a cell that populates by determining if the columns next to it have an
"X" entered. If they do, the name of the column is concatenated to the
current text, followed by a comma. If there is no "X", no text is added. I
use the formula below:

=CONCATENATE(IF(E3="X",$E$2&", ","")&IF(F3="X",$F$2&",
","")&IF(G3="X",$G$2&", ","")&IF(H3="X",$H$2&", ","")&IF(I3="X",$I$2&",
","")&IF(J3="X",$J$2&", ","")& etc...

This has woked fine for awhile, but now we have too many columns and the
formula is too long.

Any suggestions on how to make this work with a shorter formula?

Thanks in advance.

Concatonate - formula too long
 

Will that deal with "X"s in more than one column, which seemed to be what
the OP was after?
--
David Biddulph

"Don Guillett" wrote in message
...
Try this idea instead. Modify to suit
=INDEX(E13:J13,1,MATCH("x",E14:J14,0))

"Foo2U" wrote in message
...
I have a cell that populates by determining if the columns next to it have
an
"X" entered. If they do, the name of the column is concatenated to the
current text, followed by a comma. If there is no "X", no text is added.
I
use the formula below:

=CONCATENATE(IF(E3="X",$E$2&", ","")&IF(F3="X",$F$2&",
","")&IF(G3="X",$G$2&", ","")&IF(H3="X",$H$2&", ","")&IF(I3="X",$I$2&",
","")&IF(J3="X",$J$2&", ","")& etc...

This has woked fine for awhile, but now we have too many columns and the
formula is too long.

Any suggestions on how to make this work with a shorter formula?

Thanks in advance.

Concatonate - formula too long
 

That's true; I tried it and could only work with one value.

"David Biddulph" wrote:

Will that deal with "X"s in more than one column, which seemed to be what
the OP was after?
--
David Biddulph

"Don Guillett" wrote in message
...
Try this idea instead. Modify to suit
=INDEX(E13:J13,1,MATCH("x",E14:J14,0))

"Foo2U" wrote in message
...
I have a cell that populates by determining if the columns next to it have
an
"X" entered. If they do, the name of the column is concatenated to the
current text, followed by a comma. If there is no "X", no text is added.
I
use the formula below:

=CONCATENATE(IF(E3="X",$E$2&", ","")&IF(F3="X",$F$2&",
","")&IF(G3="X",$G$2&", ","")&IF(H3="X",$H$2&", ","")&IF(I3="X",$I$2&",
","")&IF(J3="X",$J$2&", ","")& etc...

This has woked fine for awhile, but now we have too many columns and the
formula is too long.

Any suggestions on how to make this work with a shorter formula?

Thanks in advance.

Concatonate - formula too long
 

How about adding two more cells--three total.

In the first cell, put a long formula for lots of cells. In the next cell, do
the rest of the concatenation.

In the third cell, just concatenate the first two cells.

If you have to, hide the columns with those two "helper cells".

Foo2U wrote:

I have a cell that populates by determining if the columns next to it have an
"X" entered. If they do, the name of the column is concatenated to the
current text, followed by a comma. If there is no "X", no text is added. I
use the formula below:

=CONCATENATE(IF(E3="X",$E$2&", ","")&IF(F3="X",$F$2&",
","")&IF(G3="X",$G$2&", ","")&IF(H3="X",$H$2&", ","")&IF(I3="X",$I$2&",
","")&IF(J3="X",$J$2&", ","")& etc...

This has woked fine for awhile, but now we have too many columns and the
formula is too long.

Any suggestions on how to make this work with a shorter formula?

Thanks in advance.

--

Dave Peterson

Concatonate - formula too long
 

Try this macro

Sub addifxinrow()
ar = ActiveCell.Row
For Each c In Range(Cells(ar, "e"), Cells(ar, "z"))
If UCase(c) = "X" Then ms = ms & "," & c.Offset(-1)
Next
MsgBox Right(ms, Len(ms) - 1)
End Sub

or a function that must go into a regular module.
Function ax() 'put in the active row at col D maybe
ar = ActiveCell.Row
For Each c In Range(Cells(ar, "e"), Cells(ar, "z"))
If UCase(c) = "X" Then ms = ms & "," & c.Offset(-1)
Next
ax = Right(ms, Len(ms) - 1)
End Function


--
Don Guillett
SalesAid Software

"Foo2U" wrote in message
...
That's true; I tried it and could only work with one value.

"David Biddulph" wrote:

Will that deal with "X"s in more than one column, which seemed to be what
the OP was after?
--
David Biddulph

"Don Guillett" wrote in message
...
Try this idea instead. Modify to suit
=INDEX(E13:J13,1,MATCH("x",E14:J14,0))

"Foo2U" wrote in message
...
I have a cell that populates by determining if the columns next to it
have
an
"X" entered. If they do, the name of the column is concatenated to
the
current text, followed by a comma. If there is no "X", no text is
added.
I
use the formula below:

=CONCATENATE(IF(E3="X",$E$2&", ","")&IF(F3="X",$F$2&",
","")&IF(G3="X",$G$2&", ","")&IF(H3="X",$H$2&",
","")&IF(I3="X",$I$2&",
","")&IF(J3="X",$J$2&", ","")& etc...

This has woked fine for awhile, but now we have too many columns and
the
formula is too long.

Any suggestions on how to make this work with a shorter formula?

Thanks in advance.